GENERATING AN ENVIROMENT FILE FROM PL/SQL
REQUEST
I want to generate the current Oracle database environment file by querying a database.
RELATED
SOLUTION
-------------------------------------------------------
-- Victor Khalturin
-- $Header 14:22 30.01.2018
--
-- Script generates the current environment file.
-- You must have execute privilege on SYS.DBMS_SYSTEM.
-------------------------------------------------------
SET LINES 300
SET PAGES 999
SET SERVEROUTPUT ON FORMAT WRAPPED
DECLARE
PLATFORM VARCHAR2(100);
DELIMITER VARCHAR2(1);
ORACLE_SID VARCHAR2(10);
ORACLE_HOME VARCHAR2(200);
DBS_HOME VARCHAR2(200);
TNS_HOME VARCHAR2(200);
PATCH_HOME VARCHAR2(200);
NLS_LANG VARCHAR2(100);
PATH VARCHAR2(100);
PS1 VARCHAR2(200);
LC_ALL VARCHAR2(200);
DISPLAY VARCHAR2(10);
BEGIN
BEGIN
SELECT PLATFORM_NAME INTO PLATFORM FROM V$DATABASE WHERE LOWER(PLATFORM_NAME) LIKE '%windows%';
DELIMITER := '\';
PATH := '%ORACLE_HOME%\bin;%ORACLE_HOME%\OPatch;%PATH%';
EXCEPTION
WHEN NO_DATA_FOUND THEN
DELIMITER := '/';
PATH := '$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:$PATH';
END;
--------------------------------
-- GET ENVIRONMENT VALUES
--------------------------------
SELECT INSTANCE_NAME INTO ORACLE_SID FROM V$INSTANCE;
DBMS_SYSTEM.GET_ENV('ORACLE_HOME',ORACLE_HOME);
DBMS_SYSTEM.GET_ENV('PATCH_HOME',PATCH_HOME);
DBMS_SYSTEM.GET_ENV('NLS_LANG',NLS_LANG);
--DBMS_SYSTEM.GET_ENV('PS1',PS1);
DBMS_SYSTEM.GET_ENV('LC_ALL',LC_ALL);
DBMS_SYSTEM.GET_ENV('DISPLAY',DISPLAY);
--------------------------------
-- SET DEFAULT
--------------------------------
IF NLS_LANG IS NULL THEN NLS_LANG := 'AMERICAN_AMERICA.AL32UTF8'; END IF;
IF LC_ALL IS NULL THEN LC_ALL := 'en_US.UTF-8'; END IF;
IF PS1 IS NULL THEN
PS1 := '"\[\e[35;1m\]\u@\h\[\e[m\]|\A|\[\e[0;33m\]\w:\[\e[m\]\[\e[32;1m\]<${ORACLE_SID}> \[\e[m\]"';
END IF;
--------------------------------
-- PRINT ENVIRONMENT FILE
--------------------------------
DBMS_OUTPUT.NEW_LINE;
DBMS_OUTPUT.PUT_LINE('#################################');
DBMS_OUTPUT.PUT_LINE('## ' || LOWER(ORACLE_SID) || '.env');
DBMS_OUTPUT.PUT_LINE('#################################');
DBMS_OUTPUT.NEW_LINE;
DBMS_OUTPUT.PUT_LINE('unset ORACLE_SID');
DBMS_OUTPUT.PUT_LINE('unset ORACLE_HOME');
DBMS_OUTPUT.PUT_LINE('unset PATH');
DBMS_OUTPUT.NEW_LINE;
DBMS_OUTPUT.PUT_LINE('export ORACLE_SID=' || ORACLE_SID);
DBMS_OUTPUT.PUT_LINE('export ORACLE_HOME=' || ORACLE_HOME);
DBMS_OUTPUT.PUT_LINE('export DBS_HOME=$ORACLE_HOME' || DELIMITER || 'dbs');
DBMS_OUTPUT.PUT_LINE('export TNS_HOME=$ORACLE_HOME' || DELIMITER || 'network' || DELIMITER || 'admin');
DBMS_OUTPUT.PUT_LINE('export PATCH_HOME=' || PATCH_HOME);
DBMS_OUTPUT.PUT_LINE('export NLS_LANG=' || NLS_LANG);
DBMS_OUTPUT.PUT_LINE('export PATH=' || PATH);
DBMS_OUTPUT.PUT_LINE('export PS1=' || PS1);
DBMS_OUTPUT.PUT_LINE('export LC_ALL=' || LC_ALL);
DBMS_OUTPUT.PUT_LINE('export DISPLAY=' || DISPLAY);
DBMS_OUTPUT.NEW_LINE;
DBMS_OUTPUT.PUT_LINE('echo');
DBMS_OUTPUT.PUT_LINE('echo " ===================================="');
DBMS_OUTPUT.PUT_LINE('echo " ORACLE_HOME : ${ORACLE_HOME}"');
DBMS_OUTPUT.PUT_LINE('echo " ORACLE_SID : ${ORACLE_SID}"');
DBMS_OUTPUT.PUT_LINE('echo " DBS_HOME : ${DBS_HOME}"');
DBMS_OUTPUT.PUT_LINE('echo " TNS_HOME : ${TNS_HOME}"');
DBMS_OUTPUT.PUT_LINE('echo " PATCH_HOME : ${PATCH_HOME}"');
DBMS_OUTPUT.PUT_LINE('echo " NLS_LANG : ${NLS_LANG}"');
DBMS_OUTPUT.PUT_LINE('echo " ===================================="');
DBMS_OUTPUT.PUT_LINE('echo');
END;
/
#################################
## dbpilot.env
#################################
unset ORACLE_SID
unset ORACLE_HOME
unset PATH
export ORACLE_SID=DBPILOT
export ORACLE_HOME=/oracle/product/12.2.0.1
export DBS_HOME=$ORACLE_HOME/dbs
export TNS_HOME=$ORACLE_HOME/network/admin
export PATCH_HOME=/oracle/patches
export NLS_LANG=AMERICAN_AMERICA.AL32UTF8
export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:$PATH
export PS1="\[\e[35;1m\]\u@\h\[\e[m\]|\A|\[\e[0;33m\]\w:\[\e[m\]\[\e[32;1m\]<${ORACLE_SID}> \[\e[m\]"
export LC_ALL=en_US.UTF-8
export DISPLAY=:1
echo
echo " ===================================="
echo " ORACLE_HOME : ${ORACLE_HOME}"
echo " ORACLE_SID : ${ORACLE_SID}"
echo " DBS_HOME : ${DBS_HOME}"
echo " TNS_HOME : ${TNS_HOME}"
echo " PATCH_HOME : ${PATCH_HOME}"
echo " NLS_LANG : ${NLS_LANG}"
echo " ===================================="
echo
NOTE: The DBMS_SYSTEM.GET_ENV does not return PATH variable.
[oracle@dbpilot.net ~]$ cd .env/
[oracle@dbpilot.net .env]$ source dbpilot.env
====================================
ORACLE_HOME : /oracle/product/12.2.0.1
ORACLE_SID : DBPILOT
DBS_HOME : /oracle/product/12.2.0.1/dbs
TNS_HOME : /oracle/product/12.2.0.1/network/admin
PATCH_HOME : /oracle/patches
NLS_LANG : AMERICAN_AMERICA.AL32UTF8
====================================
oracle@dbpilot.net|14:16|~/.env:<DBPILOT>
Version : 14:16 30.01.2018
Database : Oracle database 10g – 12c
Database : Oracle database 10g – 12c
Tags In
Related Posts
1 Comment
Leave a Reply Cancel reply
This site uses Akismet to reduce spam. Learn how your comment data is processed.
Categories
- Linux (8)
- Iptables (2)
- Memory Usage (1)
- Postfix (1)
- Yum (4)
- Networker (2)
- Oracle (63)
- Account (2)
- AWR (2)
- Database Errors (6)
- Database Performance (9)
- Datapump (3)
- Enterprise Manager (24)
- Enterprise Manager Metrics (4)
- Environments (4)
- High CPU Usage (2)
- High IO Load (4)
- Initialization Parameters (5)
- job_queue_processes (1)
- Processes (3)
- NLS (1)
- Objects (1)
- Objects DDL (2)
- OJVM/PSU/DRU Patches (1)
- PL/SQL (5)
- UTL_FILE (4)
- Prerequisites (1)
- Privileges (1)
- Profile (1)
- Queries (2)
- Redologs (1)
- Resource Limits (1)
- Segment Shrink (1)
- Session (6)
- Spfile (3)
- SQL PLUS (1)
- Spooling (1)
- Standard Queries (7)
- Statistics (2)
- Temporary Tablespace (2)
Tags
/etc/sudoers
Account
Agent
Agent 12c Deinstall
Agent 13c Deployment
Automated Maintenance Tasks
AWR
BLOCKING SESSION
Database Auditing
Database Directory
Database Performance
Datapump
DBMS_SYSTEM
Enterprise Manager
Enterprise Manager Metrics
Failed Logins (Historical) Metric
Failed Logins Metric
Gather Statistics Job
GET_ENV
High IO Load
Idle Sessions
Initialization Parameters
LOCKED(TIMED)
OEM
OEM 12.1.0.4
OEM 13.1.0.0.0
OMS
Processes
Repository
Retrieve Object DDL
ROW LOCK
Scheduler
Schema Statistics
Scripts
Sessions
spfile
SQL_ID
Standard Query
TRANSACTION
User Grants
UTL_FILE
WAITING SESSION
Window Group
Window Name
Yum
This only gives the current CLIENT variables. Not the variables the database was started with. If the database is started with lets say TNS_ADMIN=/tmp
And a new user session(shell) comes later and TNS_ADMIN is not set to /tmp. They will get whatever the CURRENT shell variables are. Not what the database was started it.