Oracle : sqlplus 常用參數－兩隻小虎,一隻豬

sqlplu 常用參數

sqlplus -s

-s 表示silent mode, 執行sqlplus 後面帶-s 參數,不會傳回任何訊息,正常情況下如果下sqlplus /nolog 會出現下面訊息

▼ Demo

$ sqlplus /nolog

SQL*Plus: Release 10.2.0.1.0 - Production on 星期一 3月 28 17:12:48 2011

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

SQL>

如果改用sqlplus -s /nolog,就不會出現上面的訊息,這樣就可以將Database中的資料寫入Log file,再透過scirpt 進行需要的處理

set time on/off

在SQL>提示符號前顯示時間

▼ Demo

SQL> set time on
15:22:49 SQL> select name from v$database;

NAME
------------------
testdb

15:23:03 SQL> set time off
SQL>

set timing on/off

顯示DDL/DML 執行所使用的時間

▼ Demo

SQL> set timing on
SQL> select name from v$database;

NAME
------------------
testdb

目前歷時: 00:00:00.17
SQL>
SQL> set timing off

show all

查看所有的系統變數

show user

顯示目前連線使用者ID

show error

顯示錯誤

set feedback off

禁止顯示最後一行的計數反饋信息

▼ Demo

SQL> set feedback on
SQL> select sysdate from dual;

SYSDATE
---------------
23-NOV-11

1 row selected.

SQL> set feedback off
SQL> select sysdate from dual;

SYSDATE
---------------
23-NOV-11
SQL>

set pagesize n

Sets the heigth of a page to n. If n=0, the page's headings, page breaks, titles, the initial blank line, and other formatting information is considered infinite.

SQL> show pagesize
pagesize 10
SQL> select rownum , object_id from dba_objects where rownum <15;

         1      17868
         2       7510
         3      23303
         4      14180
         5      22868
         6      10124
         7      23509
         8      23564
         9       9800
        10      11716

        11      17468
        12       7696
        13      19330
        14       9200 

19 rows selected.

SQL> set pagesize 20
SQL> select rownum , object_id from dba_objects where rownum <15;

         1      17868
         2       7510
         3      23303
         4      14180
         5      22868
         6      10124
         7      23509
         8      23564
         9       9800
        10      11716
        11      17468
        12       7696
        13      19330
        14       9200 

19 rows selected.

set autocommit ON

設定是否自動commit，預設為OFF

set linesize n

設置螢幕顯示行寬

▼ Demo

SQL> show linesize
linesize 80
SQL> select object_id , object_name from dba_objects where rownum < 5;

 OBJECT_ID
----------
OBJECT_NAME
--------------------------------------------------------------------------------
     17868
/1005bd30_LnkdConstant

      7510
/10076b23_OraCustomDatumClosur

     23303
/10297c91_SAXAttrList


 OBJECT_ID
----------
OBJECT_NAME
--------------------------------------------------------------------------------
     14180
/103a2e73_DefaultEditorKitEndP


SQL> set linesize 180
SQL> select object_id , object_name from dba_objects where rownum < 5;

 OBJECT_ID OBJECT_NAME
---------- ----------------------------------------------------------------------------------------
     17868 /1005bd30_LnkdConstant
      7510 /10076b23_OraCustomDatumClosur
     23303 /10297c91_SAXAttrList
     14180 /103a2e73_DefaultEditorKitEndP

set heading off

禁止輸出列標題，預設為ON

▼ Demo

SQL> show heading
heading ON
SQL> select sysdate from dual;

SYSDATE
---------------
23-NOV-11

SQL> set heading off
SQL> select sysdate from dual;

23-NOV-11

SQL>

colume COL_NAME format

▼ Demo

SQL> select object_id , object_name from dba_objects where rownum < 3;

 OBJECT_ID
----------
OBJECT_NAME
--------------------------------------------------------------------------------
     17868
/1005bd30_LnkdConstant

      7510
/10076b23_OraCustomDatumClosur


SQL> col OBJECT_NAME format a50
SQL> select object_id , object_name from dba_objects where rownum < 3;

 OBJECT_ID OBJECT_NAME
---------- --------------------------------------------------
     17868 /1005bd30_LnkdConstant
      7510 /10076b23_OraCustomDatumClosur

SQL>

colume COL_NAME noprint

▼ Demo

SQL> select object_id , object_name from dba_objects where rownum < 3;

 OBJECT_ID OBJECT_NAME
---------- --------------------------------------------------
     17868 /1005bd30_LnkdConstant
      7510 /10076b23_OraCustomDatumClosur

SQL> col object_id noprint;
SQL> select object_id , object_name from dba_objects where rownum < 3;

OBJECT_NAME
--------------------------------------------------
/1005bd30_LnkdConstant
/10076b23_OraCustomDatumClosur

Reference :

1. http://psoug.org/reference/sqlplus.html
2. http://www.adp-gmbh.ch/ora/sqlplus/