GENERATING AN ENVIROMENT FILE FROM PL/SQL
Environments
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
Categories
- Linux (8)
- Iptables (2)
- Memory Usage (1)
- Postfix (1)
- Yum (4)
- Networker (2)
- Oracle (69)
- Account (2)
- AWR (4)
- Database Errors (6)
- Database Performance (10)
- Datapump (4)
- Enterprise Manager (25)
- Enterprise Manager Metrics (4)
- Environments (4)
- High CPU Usage (3)
- High IO Load (4)
- Historical Session Information (3)
- 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)
- Tablespaces (1)
- Temporary Tablespace (2)
Tags
/etc/sudoers
Account
ACTIVE SESSION HISTORY
Agent
Agent 12c Deinstall
Agent 13c Deployment
ASH
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
historical session information
Idle Sessions
Initialization Parameters
LOCKED(TIMED)
OEM
OEM 12.1.0.4
OEM 13.1.0.0.0
OMS
Processes
Repository
ROW LOCK
Scheduler
Schema Statistics
Scripts
Sessions
spfile
SQL_ID
STANDARD EDITION
Standard Query
TRANSACTION
UTL_FILE
WAITING SESSION
Yum