Edi Yanto (何 萬 新)

How to Find Environment Variables and ORACLE_HOME Path on Database

Posted on: September 16, 2014


Do you ever think to find the environment variables path and ORACLE_HOME path from database table? Below are the scripts to achieve them:

  • Environment Variables

    select variable_name, value
    from   fnd_env_context
    where  variable_name like ‘%\_TOP’ escape ‘\’
    and    concurrent_process_id = (select max(concurrent_process_id) from fnd_env_context)
    order by 1;

    Result:
    VARIABLE_NAME   VALUE
    ————— ————————————————–
    AD_TOP          /oracle/VIS/apps/apps_st/appl/ad/12.0.0
    AF_JRE_TOP      /oracle/VIS/apps/tech_st/10.1.3/appsutil/jdk/jre
    AHL_TOP         /oracle/VIS/apps/apps_st/appl/ahl/12.0.0
    AK_TOP          /oracle/VIS/apps/apps_st/appl/ak/12.0.0
    ALR_TOP         /oracle/VIS/apps/apps_st/appl/alr/12.0.0
    AME_TOP         /oracle/VIS/apps/apps_st/appl/ame/12.0.0
    AMS_TOP         /oracle/VIS/apps/apps_st/appl/ams/12.0.0
    AMV_TOP         /oracle/VIS/apps/apps_st/appl/amv/12.0.0
    AMW_TOP         /oracle/VIS/apps/apps_st/appl/amw/12.0.0
    APPL_TOP        /oracle/VIS/apps/apps_st/appl
    AP_TOP          /oracle/VIS/apps/apps_st/appl/ap/12.0.0
    ……………….
    ……………….

  • ORACLE_HOME

    select substr(file_spec,1,instr(file_spec,’lib’)-2) oracle_home
    from dba_libraries
    where library_name=’DBMS_SUMADV_LIB’;

    OR

    declare
    oracle_home varchar2(255);
    begin
    dbms_system.get_env(‘ORACLE_HOME’,oracle_home);
    dbms_output.put_line(oracle_home);
    end;

  • Result:
    ORACLE_HOME
    ———————————–
    /oracle/VIS/db/tech_st/11.1.0

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 1,368 other followers


Edi Yanto
Oracle Applications Technical Consultant
View Edi Yanto's profile on LinkedIn

My Certifications






Follow me on Twitter

Archives

September 2014
M T W T F S S
« May   Dec »
1234567
891011121314
15161718192021
22232425262728
2930  

Visitors

Categories

Blog Stats

  • 140,585 hits

Users Online

web counter

Pages

%d bloggers like this: