ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling
complimentary technical Workshops on Database Security for the first 30
Oracle Database customers located anywhere in North America, EMEA, LATAM, or
APAC that send an email to
asra_us@oracle.com. Request a Workshop for
your organization today.
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
Current container identifier
SHOW CON_ID
SQL> show con_id
CON_ID
-------
1
CON_NAME
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
