Oracle SQL*Plus SHOW
Version 12.1.0.1

General Information
Library Note Morgan's Library Page Footer
The Library is currently in the process of being upgraded from Oracle Database Version 11.2.0.3 to 12.1.0.1. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.
Purpose The SHOW command in SQL*Plus is used to display a single value based on the SQL*Plus environment or to list all parameters of the current SQL*Plus session
 
ALL
Display SQL*Plus configuration parameters SHOW ALL
conn uwclass/uwclass@pdbdev

show all
 
AUTORECOVERY
Display whether AUTORECOVERY is enabled SHOW AUTORECOVERY
SQL> show autorecovery
autorecovery OFF
 
CON_ID (new in 12c)
Current container identifier SHOW CON_ID
SQL> show con_id

CON_ID
------------------------------
1
 
CON_NAME (new in 12c)
Current container name SHOW CON_NAME
SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT
 
EDITION
Display the name of the current session edition SHOW EDITION
SQL> show edition

EDITION
------------------------------
ORA$BASE
 
EDITION_ID
Display the name of the current session edition SHOW EDITION_ID
SQL> show edition_id

EDITION
------------------------------
ORA$BASE
 
ERRORS
Display information about the most recent PL/SQL compilation error or warning stack SHOW ERRORS
conn uwclass/uwclass@pdbdev

CREATE PROCEDURE p IS
BEGIN
  bad_code;
END;
/

show errors
 
INSTANCE
Display the connect identifier for the default instance SHOW INSTANCE
conn / as sysdba

SQL> show instance
instance "local"
 
LOGSOURCE
Display the location for archive logs. Displays "" if not in archivelog mode SHOW LOGSOURCE
conn / as sysdba

show logsource
 
PARAMETERS
Display the initialization parameters SHOW PARAMETERS <parameter_name>
conn / as sysdba

set linesize 121

SQL> show parameter undo

NAME TYPE VALUE
------------------------------------ ----------- ---------
temp_undo_enabled boolean FALSE
undo_management string AUTO
undo_retention integer 3600
undo_tablespace string UNDOTBS1
 
RECYCLEBIN
Display the objects in the recyclebin that can be reverted with FLASHBACK SHOW RECYCLEBIN
conn uwclass/uwclass@pdbdev

show recyclebin

CREATE TABLE t (
testcol VARCHAR2(20));

DROP TABLE t;

show recyclebin
-- flashback database must be enabled otherwise no result is observed
 
RELEASE
Display the Oracle release number SHOW RELEASE
conn uwclass/uwclass@pdbdev

SQL> show release
release 1201000100
 
SGA
Display information about the SGA SHOW SGA
conn / as sysdba

SQL> show sga

Total System Global Area 2505338880 bytes
Fixed Size                  2405760 bytes
Variable Size             587205248 bytes
Database Buffers         1895825408 bytes
Redo Buffers               19902464 bytes
 
SQLCODE
Display the value of the SQLCODE environment variable SHOW SQLCODE
conn uwclass/uwclass@pdbdev

SQL> drop tabel zzyzx;
drop tabel zzyzx
*
ERROR at line 1:
ORA-00950: invalid DROP option

SQL> show sqlcode
sqlcode 950
 
USER
Display the current user name SHOW USER
conn uwclass/uwclass@pdbdev

SQL> show user
USER is "UWCLASS"
 
XQUERY
Displays the current values of the XQUERY settings, BASEURI, CONTEXT, NODE and ORDERING SHOW XQUERY
conn uwclass/uwclass@pdbdev

SQL> show xquery
xquery BASEURI "" CONTEXT "" NODE DEFAULT ORDERING DEFAULT

Related Topics
SQL*Plus

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2014 Daniel A. Morgan All Rights Reserved