show命令用来显示SQL*Plus系统变量或当前SQL*Plus环境的值。SHOW SGA需要DBA特权登录。show命令支持的选项:
system_variable ALL BTI[TLE] CON_ID CON_NAME EDITION ERR[ORS] [ {ANALYTIC VIEW |
ATTRIBUTE DIMENSION | HIERARCHY | FUNCTION | PROCEDURE | PACKAGE | PACKAGE BODY |
TRIGGER | VIEW | TYPE | TYPE BODY | DIMENSION | JAVA CLASS } [schema.]name]HISTORY
LNO LOBPREF[ETCH] PARAMETER[S] [parameter_name] PDBS PNO RECYC[LEBIN]
[original_name] REL[EASE] REPF[OOTER] REPH[EADER] ROWPREF[ETCH] SGA SPOO[L]
SPPARAMETER[S] [parameter_name] SQLCODE STATEMENTC[ACHE] TTI[TLE] USER XQUERY
1,system_variable 显示由set命令设置的任何系统变量。
SQL> show echo
echo OFF
SQL>
SQL> show linesize
linesize 80
2,ALL 按字母顺序显示除了ERROR和SGA信息外的所有show命令支持选项的信息。
SQL> show all
appinfo is OFF and set to "SQL*Plus"
arraysize 15
autocommit OFF
autoprint OFF
autorecovery OFF
autotrace OFF
blockterminator "." (hex 2e)
。。。。。。
3,CON_ID 当连接的是CDB时,返回容器的ID;连接的是非CDB时,返回0。
SQL> show con_id
CON_ID
-------------------
0
4,CON_NAME 当连接到CDB时,显示容器的名称;当为非CDB时,12.2版本显示"Non Consolidated",12.1版本显示数据库名称。(本测试为12.1版本)
SQL> show con_name
CON_NAME
------------------------------
ora1
5,EDITION 显示现有数据库的版本属性。
SQL> show edition
EDITION
------------------
ORA$BASE
6,BTITLE 显示当前BTITLE定义。
SQL> show btitle
btitle OFF and is the first few characters of the next SELECT statement
7,ERRORS 显示编译存储过程 (includes stored functions,procedures, and packages)时产生的错误信息。
SQL> create procedure p_test //创建存储过程时,告警提示有编译错误
as
begin
update tab_aa set id = 1;
commit;
end;
/
Warning: Procedure created with compilation errors.
SQL> show errors //查看具体的告警错误信息
Errors for PROCEDURE P_TEST:
LINE/COL ERROR
-------- --------------------------------------------------
4/4 PL/SQL: SQL Statement ignored
4/11 PL/SQL: ORA-00942: table or view does not exist
show errors可带的参数如下:
ERR[ORS] [{ANALYTIC VIEW | ATTRIBUTE DIMENSION | HIERARCHY | FUNCTION | PROCEDURE |
PACKAGE | PACKAGE BODY | TRIGGER | VIEW | TYPE | TYPE BODY | DIMENSION | JAVA
CLASS} [schema.]name]
show errors默认显示最后一次的编译错误信息。当指定具体的对象类型和对象名称时,可以查看特定对象的编译错误信息。如上面的存储过程编译错误信息可以用如下命令查看:
SQL> show errors PROCEDURE sys.p_test
Errors for PROCEDURE SYS.P_TEST:
LINE/COL ERROR
-------- -----------------------------------------------------------------
4/4 PL/SQL: SQL Statement ignored
4/11 PL/SQL: ORA-00942: table or view does not exist
8,HISTORY 设置完set history后,可以show history查看history的设置状态。
SQL> show history
history is OFF
SQL> set history on
SQL> show history
history is ON and set to "100"
9,PARAMETERS [parameter_name] 显示一个或多个初始化参数的当前值。查看所有参数时就不写后面的参数名称。参数名称也不必写完整,会自动显示包含输入字符串的参数信息。
SQL> show parameter shared_pool
NAME TYPE VALUE
----------------------------------- ----------- ----------
shared_pool_reserved_size big integer 26843545
shared_pool_size big integer 512M
10,PDBS 显示当前连接的CDB下的PDB信息,如果连接的是PDB,则只显示当前PDB的信息。
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB1 READ WRITE NO
11,RECYC[LEBIN] [original_name] 查看回收站的对象信息,这些对象可以通过FLASHBACK BEFORE DROP命令恢复。show recyclebin查看的是user_recyclebin中的信息,而不是dba_recyclebin中的信息。
SQL> show recyclebin //查看当前账号下的回收站信息
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
WRI$_RCS_12_1 BIN$jMC+IM+xRdPgU24BAQEF4g==$0 TABLE 2019-07-03:13:00:40
WRI$_RCS_12_1 BIN$iDFVbglWLR3gU24BAQEflA==$0 TABLE 2019-05-06:12:14:16
SQL> purge recyclebin; //清空当前账号下的回收站信息
SQL> show recyclebin //此时查看回收站,已经没有信息保留
SQL>
SQL> select count( * ) from user_recyclebin; //查看user_recyclebin视图信息
COUNT( * )
----------
0
SQL> select count( * ) from dba_recyclebin; //查看dba_recyclebin视图信息
COUNT( * )
----------
29
12,SPOOL 查看spool状态信息。
SQL> show spool;
spool OFF
13,SGA 查看当前instance的SGA信息。
SQL> show sga;
Total System Global Area 1946157056 bytes
Fixed Size 2925840 bytes
Variable Size 855640816 bytes
Database Buffers 1073741824 bytes
Redo Buffers 13848576 bytes
14,USER 查看当前连接的用户名。
SQL> show user;
USER is "SYS"