SQL Plus - Show (System Variable) Command

> Procedural Languages > (SQL Plus|SqlCl) (Oracle Database Console)

1 - About

The SHOW command shows the value of:

SHOW SGA requires a DBA privileged login.

Advertising

3 - Syntax

SHO[W] option

where option represents one of the following:

  • system variable: Represents any system variable set by the SET command.
  • ALL
  • BTI[TLE]: Shows the current BTITLE definition.
  • CON_ID: Displays the id of the Container to which you are connected when connected to a Consolidated Database. If issued when connected to a non-Consolidated Database, this command returns 0.
  • CON_NAME: Displays the name of the Container to which you are connected when connected to a Consolidated Database.
  • EDITION: Shows the edition attribute of the existing database.
  • ERR[ORS] [{FUNCTION | PROCEDURE | PACKAGE | PACKAGE BODY | TRIGGER | VIEW | TYPE | TYPE BODY | DIMENSION | JAVA CLASS} [schema.]name]
  • LNO: Shows the current line number (the position in the current page of the display and/or spooled output).
  • PARAMETERS [parameter_name]
  • PDBS: Display the names, ids, mode and restriction status of Pluggable Databases in the Consolidated Database to which you are connected. Returns NULL if you are connected to a non-Consolidated Database.
  • PNO: Shows the current page number.
  • RECYC[LEBIN] [original_name]: Shows objects in the recycle bin that can be reverted with the FLASHBACK BEFORE DROP command.
  • REL[EASE]: Shows the release number of Oracle Database that SQL*Plus is accessing.
  • REPF[OOTER]: Shows the current REPFOOTER definition.
  • REPH[EADER]: Shows the current REPHEADER definition.
  • SGA: Displays information about the current instance's System Global Area.
  • SPOO[L]: Shows whether output is being spooled.
  • SPPARAMETERS [parameter_name].
  • SQLCODE: Shows the value of SQL.SQLCODE (the SQL return code of the most recent operation).
  • TTI[TLE]: Shows the current TTITLE definition.
  • XQUERY: Shows the current values of the XQUERY settings, BASEURI, CONTEXT, NODE and ORDERING.
Advertising

4 - Options

4.1 - Errors

Syntax:

ERR[ORS] [{FUNCTION | PROCEDURE | PACKAGE | PACKAGE BODY | TRIGGER | VIEW | TYPE | TYPE BODY | DIMENSION | JAVA CLASS} [schema.]name]
CREATE PROCEDURE HR.BADPROC AS
BEGIN
   myVariable := 'Nico'
END;
/
Warning: Procedure created with compilation errors.
show errors
-- or 
show errors PROCEDURE HR.BADPROC
Errors for PROCEDURE HR.BADPROC:
4/1      PLS-00103: Encountered the symbol "END" when expecting one of the
         following:
         * & = - + ; < / > at in is mod remainder not rem
         <an exponent (**)> <> or != or ~= >= <= <> and or like like2
         like4 likec between || multiset member submultiset
         The symbol ";" was substituted for "END" to continue.

Oracle will fired the following SQL

SELECT
  TO_CHAR( LINE )
  ||'/'
  ||TO_CHAR( POSITION ) "LINE/COL",
  TEXT "ERROR"
FROM
  ALL_ERRORS A
WHERE
  A.NAME    = UPPER( :NMBIND_SHOW_OBJ )
AND A.TYPE  = 'PROCEDURE'
AND A.OWNER = UPPER( :NMBIND_SHOW_OWN )
ORDER BY
  LINE,
  POSITION,
  ATTRIBUTE,
  MESSAGE_NUMBER;

4.2 - SGA

Displays information about the current instance's System Global Area.

It will fire the following SQL:

SELECT
  DECODE( NULL, '', 'Total System Global Area', '' ) NAME_COL_PLUS_SHOW_SGA,
  SUM( VALUE ),
  DECODE( NULL, '', 'bytes', '' ) units_col_plus_show_sga
FROM
  V$SGA
UNION ALL
SELECT
  NAME NAME_COL_PLUS_SHOW_SGA,
  VALUE,
  DECODE( NULL, '', 'bytes', '' ) units_col_plus_show_sga
FROM
  V$SGA
Total System Global Area 1570009088 bytes
Fixed Size                  2403064 bytes
Variable Size            1140851976 bytes
Database Buffers          419430400 bytes
Redo Buffers                7323648 bytes
Advertising

4.3 - spparameter

The option SPPARAMETERS will fire the following SQL.

SELECT
  SID SID_COL_PLUS_SHOW_SPPARAM,
  NAME NAME_COL_PLUS_SHOW_SPPARAM,
  TYPE,
  DISPLAY_VALUE VALUE_COL_PLUS_SHOW_SPPARAM
FROM
  V$SPPARAMETER
WHERE
  UPPER(NAME) LIKE UPPER(:NMBIND_SHOW_OBJ) 
ORDER BY
  NAME_COL_PLUS_SHOW_SPPARAM,
  VALUE_COL_PLUS_SHOW_SPPARAM;

4.4 - XQUERY

Shows the current values of the XQUERY settings, BASEURI, CONTEXT, NODE and ORDERING.

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

5 - Example

SHOW ALL
appinfo is OFF and set to "SQL*Plus"
arraysize 15
autocommit OFF
autoprint OFF
autorecovery OFF
autotrace OFF
blockterminator "." (hex 2e)
btitle OFF and is the first few characters of the next SELECT statement
cmdsep OFF
colsep " "
compatibility version NATIVE
concat "." (hex 2e)
copycommit 0
COPYTYPECHECK is ON
define "&" (hex 26)
describe DEPTH 1 LINENUM OFF INDENT ON
echo OFF
editfile "afiedt.buf"
embedded OFF
escape OFF
escchar OFF
exitcommit ON
colinvisible OFF
FEEDBACK ON for 6 or more rows
flagger OFF
flush ON
heading ON
headsep "|" (hex 7c)
instance "local"
linesize 140
lno 5
loboffset 1
logsource ""
long 5000
longchunksize 80
markup HTML OFF HEAD "<style type='text/css'> body {font:10pt Arial,Helvetica,sans-serif; color:black; background:White;} p {font:10pt Arial
,Helvetica,sans-serif; color:black; background:White;} table,tr,td {font:10pt Arial,Helvetica,sans-serif; color:Black; background:#f7f7e7; p
adding:0px 0px 0px 0px; margin:0px 0px 0px 0px;} th {font:bold 10pt Arial,Helvetica,sans-serif; color:#336699; background:#cccc99; padding:0
px 0px 0px 0px;} h1 {font:16pt Arial,Helvetica,Geneva,sans-serif; color:#336699; background-color:White; border-bottom:1px solid #cccc99; ma
rgin-top:0pt; margin-bottom:0pt; padding:0px 0px 0px 0px;-
} h2 {font:bold 10pt Arial,Helvetica,Geneva,sans-serif; color:#336699; background-color:White; margin-top:4pt; margin-bottom:0pt;} a {font:9
pt Arial,Helvetica,sans-serif; color:#663300; background:#ffffff; margin-top:0pt; margin-bottom:0pt; vertical-align:top;}</style><title>SQL*
Plus Report</title>" BODY "" TABLE "border='1' width='90%' align='center' summary='Script output'" SPOOL OFF ENTMAP ON PREFORMAT OFF
newpage 1
null ""
numformat ""
numwidth 10
pagesize 9999
PAUSE is OFF
pno 1
recsep WRAP
recsepchar " " (hex 20)
release 1201000100
repfooter OFF and is NULL
repheader OFF and is NULL
securedcol is OFF
serveroutput ON SIZE 1000000 FORMAT WRAPPED
shiftinout INVISIBLE
showmode OFF
spool OFF
sqlblanklines OFF
sqlcase MIXED
sqlcode 0
sqlcontinue "> "
sqlnumber ON
sqlpluscompatibility 12.1.0
sqlprefix "#" (hex 23)
sqlprompt "[email protected]>"
sqlterminator ";" (hex 3b)
suffix "sql"
tab ON
termout OFF
timing OFF
trimout ON
trimspool ON
ttitle OFF and is the first few characters of the next SELECT statement
underline "-" (hex 2d)
USER is "SYS"
verify ON
wrap : lines will be wrapped
xmloptimizationcheck OFF
errorlogging is OFF