PLSQL常用指令
set time on/off
在SQL>提示符號前顯示時間
SQL> set time on
19:52:28 SQL>
set timing on/off
顯示DDL/DML 執行所使用的時間
SQL> select name from v$database;
NAME
———
TESTDB
Elapsed: 00:00:00.17
List：列出上一次執行的sql
SQL> list
1* select object_id , object_name from dba_objects where rownum < 3
/：執行上次的sql
SQL> /
OBJECT_ID OBJECT_NAME
———- ————————————————–
20 ICOL$
44 I_USER1
Col column_name format a<num>
SQL> select object_id , object_name from dba_objects where rownum < 3;
OBJECT_ID
———-
OBJECT_NAME
——————————————————————————–
20
ICOL$
44
I_USER1
SQL> col object_name format a50
SQL> select object_id , object_name from dba_objects where rownum < 3;
OBJECT_ID OBJECT_NAME
———- ————————————————–
20 ICOL$
44 I_USER1
set linesize n
設置螢幕顯示行寬
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> set pagesize 5
SQL> select rownum,object_id from dba_objects where rownum <15;
ROWNUM OBJECT_ID
———- ———-
1 20
2 44
ROWNUM OBJECT_ID
———- ———-
3 28
4 15
Set autotrace on explain
SQL> set autotrace
Usage: SET AUTOT[RACE] {OFF | ON | TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]]
SQL> set autotrace on explain
–autotrace on表示顯示execution result,execution plan and exection statistics
–aututrace on explain =>只顯示execution result and execution plan
–traceonly 表示不要顯示execution result,只要顯示execution plan and exection statistics.但該sql還是有被執行
SQL> select count(*) from hr.big1;
COUNT(*)
———-
72469 –真實筆數(Execute)
Execution Plan
———————————————————-
Plan hash value: 544717873
——————————————————————-
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
——————————————————————-
| 0 | SELECT STATEMENT | | 1 | 289 (1)| 00:00:04 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| BIG1 | 67259 | 289 (1)| 00:00:04 | –optimizer所使用的statistics(67259),代表在計算執行計畫成本時,optimizer認為hr.big1有67259筆rows
——————————————————————-
SQL> execute dbms_stats.gather_table_stats('HR','BIG1'); –人為收集hr.big1的optimizer statistics(預設抽樣單位為100%)
Set feedback off
禁止顯示最後一行的計數反饋信息
SQL> select name from v$database;
NAME
———
TESTDB
1 row selected.
SQL> set feedback on;
SQL> select name from v$database;
NAME
———
TESTDB
1 row selected.
show all
查看所有的系統變數
show user
顯示目前連線使用者ID
show error
顯示錯誤
set autocommit ON
設定是否自動commit，預設為OFF
set heading off
禁止輸出列標題，預設為ON
SQL> select sysdate from dual;
SYSDATE
———
22-JAN-14
1 row selected.
SQL> set heading off;
SQL> select sysdate from dual;
22-JAN-14
1 row selected.
參考資料：
http://rickyju.pixnet.net/blog/post/28380252-oracle-%3a-sqlplus-%E5%B8%B8%E7%94%A8%E5%8F%83%E6%95%B8