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
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
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
