3 Replies Latest reply on Aug 23, 2013 9:56 PM by gdieckmann

    SQLPlus 12c | Set SQLPROMPT

    gdieckmann

      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

          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

            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

              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>