About
The SHOW command shows the value of:
- a SQL*Plus system variable
- or the current SQL*Plus environment.
SHOW SGA requires a DBA privileged login.
Articles Related
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.
- USER: Shows the username you are currently using to access SQL*Plus
- xquery: Shows the current values of the XQUERY settings, BASEURI, CONTEXT, NODE and ORDERING.
Options
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;
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
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;
XQUERY
Shows the current values of the XQUERY settings, BASEURI, CONTEXT, NODE and ORDERING.
show xquery
xquery BASEURI "" CONTEXT "" NODE DEFAULT ORDERING DEFAULT
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 "sys@orcl>"
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