This discussion is archived
3 Replies Latest reply: Aug 23, 2013 2:56 PM by gdieckmann RSS

SQLPlus 12c | Set SQLPROMPT

gdieckmann Newbie
Currently Being Moderated

Is there a way to make the SQLPROMPT re-execute when we alter a session into a PDB?

 

For example, I've edited my glogin.sql script to show the con_name and db_name. However, if I access my CDB, and from the CDB execute "alter session set container=<NAME_OF_PDB>", then the glogin.sql script doesn't get re-executed, and the prompt still shows the name of my CDB.

 

SQL in glogin.sql: select upper(sys_context ('userenv', 'db_name') || '@' || sys_context('userenv', 'con_name')) global_name from dual;

 

If the glogin.sql can't do this, is there another way that the SQLPROMPT can be updated after an 'alter session'?

  • 1. Re: SQLPlus 12c | Set SQLPROMPT
    DK2010 Guru
    Currently Being Moderated

    Hi,

     

    You can get the value when you connect with PDB not with the alter session.

    connect with you pdb and then check that value.

    conn sys/pass@<pdbname> as sysdba

     

    HTH

  • 2. Re: SQLPlus 12c | Set SQLPROMPT
    gdieckmann Newbie
    Currently Being Moderated

    Hi DK,

     

    The SQL above works regardless of whether I sign directly into a CDB, directly into a PDB or even after an alter session. I just liked being able to use the SQLPROMPT to show which database/instance I was working in, without having query or use a SHOW command.

     

    Is the idea that if I wanted to work in the CDB, I'd connect to the CDB. And if I wanted to work in a PDB, I'd connect directly to the PDB? At my institution, I'd would be both the CDB and PDB admin, so could imagine myself switching between the instances fairly often.

     

    Thanks!

  • 3. Re: SQLPlus 12c | Set SQLPROMPT
    gdieckmann Newbie
    Currently Being Moderated

    I think I found somewhat of a solution (maybe it was too obvious).

     

    In the $ORACLE_HOME/sqlplus/admin directory, I created a new script (enter_plug.sql) that has the following:

    set termout off

    alter session set container=&1;

    @glogin

    set termout on

     

    I can enter into the CDB with SQLPlus, and see the name of the CDB and that I'm in the root:

    [oracle@localhost admin]$ sqlplus / as sysdba

    TEST12C@CDB$ROOT> show con_name

     

     

    CON_NAME

    ------------------------------

    CDB$ROOT

     

    And if I want to switch into a PDB, I just call the script from above:

    TEST12C@CDB$ROOT> @enter_plug PLUG3

    TEST12C@PLUG3> show con_name

     

     

    CON_NAME

    ------------------------------

    PLUG3

     

    The other alternative is to just call the SQL script after the alter session:

    TEST12C@CDB$ROOT> show con_name

     

     

    CON_NAME

    ------------------------------

    CDB$ROOT

    TEST12C@CDB$ROOT> alter session set container=PLUG3;

     

     

    Session altered.

     

     

    TEST12C@CDB$ROOT> @glogin.sql

    TEST12C@PLUG3>

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points