oracle set命令小结

常用set小节

 

    在写脚本的时候，往往需要对输出的格式进行规范，我们常常用set语句在设置一些需要的格式规范。下面，列举了几个常用的set，和他们的应用。

SQL> set linesize 80;       //输出一行字符个数，缺省为80
SQL> set numwidth 12;     //输出number类型域长度，缺省为1

SQL> set verify off        //可以关闭和打开提示确认信息old 1和new 1的显示.

SQL> set echo on---------------------------------------------------设置运行命令是是否显示语句

SQL> set feedback on----------------------------------------------设置显示“已选择XX行”

SQL> set colsep | ---------------------------------------------------设置列与列之间的分割符号

SQL> set pagesize 10-----------------------------------------------设置每一页的行数

SQL> SET SERVEROUTPUT ON-------------------------------设置允许显示输出类似dbms_output

SQL> set heading on------------------------------------------------设置显示列名

SQL> set timing on--------------------------------------------------设置显示“已用时间：XXXX”

SQL> set time on-----------------------------------------------------设置显示当前时间

SQL> set autotrace on-----------------------------------------------设置允许对执行的sql进行分析

 

设置echo on，显示运行命令的语句

 

00:38:28 SQL> set echo on 00:38:37 SQL> @E:\echo.sql 00:38:41 SQL> select status from v$datafile where rownum<6;   STATUS ------- SYSTEM ONLINE ONLINE ONLINE ONLINE   已选择5行。   已用时间:  00: 00: 01.08 00:38:43 SQL> set echo off 设置echo off，不显示运行命令的语句 00:38:58 SQL> @E:\echo.sql   STATUS ------- SYSTEM ONLINE ONLINE ONLINE ONLINE   已选择5行。   已用时间:  00: 00: 01.09 00:39:08 SQL>

 

 

SQL> set feedback on SQL> set colsep | SQL> set pagesize 10 SQL> set serveroutput on SQL> set heading on SQL> set timing on SQL> set time on 00:54:11SQL> SELECT FILE#, STATUS, ENABLED FROM V$DATAFILE;      FILE#|STATUS |ENABLED ----------|-------|----------          1|SYSTEM |READ WRITE          2|ONLINE |READ WRITE          3|ONLINE |READ WRITE          4|ONLINE |READ WRITE          5|ONLINE |READ WRITE          6|ONLINE |READ WRITE          7|ONLINE |READ WRITE      FILE#|STATUS |ENABLED ----------|-------|----------          9|ONLINE |READ WRITE         10|ONLINE |READ WRITE         11|ONLINE |READ WRITE         12|ONLINE |READ WRITE 11 rows selected. Elapsed: 00:00:01.07 00:54:34SQL> DECLARE 00:55:00  2  BEGIN 00:55:00  3      DBMS_OUTPUT.PUT_LINE('===This is dbms_output.put_line!==='); 00:55:00  4  END; 00:55:02  5  / ===This is dbms_output.put_line!=== PL/SQL procedure successfully completed. Elapsed: 00:00:00.02 00:55:05SQL> Time on：显示时间 Feedback on：反映结果   Timing on：运行时间   Serveroutput on：设置服务器允许输出 Heading on：显示标题   Colsep | ：用“|”作为列分割符   Pagesize 10：每页10行，包含标题和空行

 

 

Autotrace on：显示sql的执行计划以及消耗。 SQL> set autotrace on SQL> SELECT E.LAST_NAME, D.DEPARTMENT_NAME, D.LOCATION_ID   2     FROM HR.EMPLOYEES E   3     JOIN HR.DEPARTMENTS D   4    USING (DEPARTMENT_ID); LAST_NAME                 DEPARTMENT_NAME                LOCATION_ID ------------------------- ------------------------------ ----------- King                      Executive                             1700 Kochhar                   Executive                             1700 De Haan                   Executive                             1700 Hunold                    IT                                    1400 Ernst                     IT                                    1400 …… 106 rows selected. Execution Plan ----------------------------------------------------------    0      SELECT STATEMENT Optimizer=CHOOSE (Cost=5 Card=106 Bytes=318           0)    1    0   HASH JOIN (Cost=5 Card=106 Bytes=3180)    2    1     TABLE ACCESS (FULL) OF 'DEPARTMENTS' (Cost=2 Card=27 Byt           es=513)    3    1     TABLE ACCESS (FULL) OF 'EMPLOYEES' (Cost=2 Card=107Byte           s=1177) Statistics ----------------------------------------------------------           7  recursive calls           0  db block gets          23  consistent gets          12  physical reads           0  redo size        2801  bytes sent via SQL*Net to client         580  bytes received via SQL*Net from client           9  SQL*Net roundtrips to/from client           0  sorts (memory)           0  sorts (disk)         106  rows processed SQL>

 

更加详细说明见官方文档：

 

SET System Variable Summary

http://docs.oracle.com/cd/E11882_01/server.112/e16604/ch_twelve040.htm#SQPUG075