Little known SQL*Plus SHOW command options

The SQL*Plus command SHOW shows you the value of a SQL*Plus system variable, or the current SQL*Plus environment.

SHOW displays the value of any system variable set by the SET command. But, did you know that SHOW can also be used to display other types of information?

For example, SHOW PARAMETERS displays the values of initialization parameters in effect for the current session and SHOW SPPARAMETERS (new in 11g) displays the values of initialization parameters in the server parameter file (SPFILE):

SQL> show parameters cursor

NAME                                 TYPE        VALUE
------------------------------------ ----------- --------------------------
cursor_bind_capture_destination      string      memory+disk
cursor_sharing                       string      EXACT
cursor_space_for_time                boolean     FALSE
open_cursors                         integer     300
session_cached_cursors               integer     50

SQL> show spparameters cursor

SID      NAME                          TYPE        VALUE
-------- ----------------------------- ----------- ------------------------
*        cursor_bind_capture_destinati string
         on
*        cursor_sharing                string
*        cursor_space_for_time         boolean
*        open_cursors                  integer     300
*        session_cached_cursors        integer

The VALUE column in the SHOW SPPARAMETERS output displays the value of the initialization parameter as it is set in the spfile. Let’s change the value of open_cursors only in the spfile and then inspect the results of both show parameters and spparameters:

SQL> alter system set open_cursors=500 scope=spfile;

System altered.

SQL> show parameters cursor

NAME                                 TYPE        VALUE
------------------------------------ ----------- --------------------------
cursor_bind_capture_destination      string      memory+disk
cursor_sharing                       string      EXACT
cursor_space_for_time                boolean     FALSE
open_cursors                         integer     300
session_cached_cursors               integer     50

SQL> show spparameters cursor

SID      NAME                          TYPE        VALUE
-------- ----------------------------- ----------- ------------------------
*        cursor_bind_capture_destinati string
         on
*        cursor_sharing                string
*        cursor_space_for_time         boolean
*        open_cursors                  integer     500
*        session_cached_cursors        integer

Neat!

Another way to inspect the content of the spfile is to dump it into a pfile using the command create pfile … from spfile. for example: create pfile=’c:\init.ora’ from spfile. You can then open init.ora in a text editor and view its content. But, SHOW SPPARAMETERS is just simpler to use.

The following are a few other SHOW options that you may not know about.

Show objects in the recycle bin that can be reverted with the FLASHBACK BEFORE DROP command:

SQL> show recyclebin

ORIGINAL NAME   RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
--------------- ------------------------------ ------------ ----------------
T               BIN$kEMDIUImSOmJJgm4wAAPyQ==$0 TABLE        2011-02-27:20:51

Show the release number of the Oracle Database that SQL*Plus is accessing:

SQL> show release

release 1102000200

Display information about the current instance’s System Global Area (needs DBA privileges):

SQL> show sga

Total System Global Area 3323752448 bytes
Fixed Size                  2257744 bytes
Variable Size            2164264112 bytes
Database Buffers         1140850688 bytes
Redo Buffers               16379904 bytes

Show the username you are currently using to access SQL*Plus:

SQL> show user

USER is "EDDIE"

If you connect as “/ AS SYSDBA”, then the SHOW USER command displays USER is “SYS”.

Show whether AUTORECOVERY is enabled:

SQL> show autorecovery

autorecovery OFF

Display the connect identifier for the default instance:

SQL> show instance

instance "local"

Display the location for archive logs:

SQL> show logsource

logsource "C:\oracle\arch"

Thanks to @goryunov for bringing show spparameters to my attention.

Filed in Oracle on 01 Mar 11 | Tags: sqlplus

---