[oracle@nwgdb ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.1.0.6.0 - Production on 星期一 11月 12 15:58:13 2012
Copyright (c) 1982, 2007, Oracle. All rights reserved.
連線到:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options
SQL> conn pms/pms
已連線
SQL> set autotrace on;
SP2-0618: 找不到階段作業 ID. 請檢查是否啟用 PLUSTRACE 角色
SP2-0611: 啟動 STATISTICS 報表時發生錯誤
SQL> conn / as sysdba
SQL> show user;
USER 為 "SYS"
SQL> SELECT * FROM DBA_ROLE_PRIVS WHERE GRANTEE='PMS';
GRANTEE GRANTED_ROLE ADM DEF
------------------------------ ------------------------------ --- ---
PMS CONNECT NO YES
PMS RESOURCE NO YES
SQL> !find /u -name utlxplan.sql
/u/oracle/product/11.1/rdbms/admin/utlxplan.sql
SQL> @/u/oracle/product/11.1/rdbms/admin/utlxplan.sql
已建立表格
SQL> !find /u -name plustrce.sql
/u/oracle/product/11.1/sqlplus/admin/plustrce.sql
SQL> @/u/oracle/product/11.1/sqlplus/admin/plustrce.sql
SQL>
SQL> drop role plustrace;
已刪除角色.
SQL> create role plustrace;
已建立角色.
SQL>
SQL> grant select on v_$sesstat to plustrace;
順利授權.
SQL> grant select on v_$statname to plustrace;
順利授權.
SQL> grant select on v_$mystat to plustrace;
順利授權.
SQL> grant plustrace to dba with admin option;
順利授權.
SQL>
SQL> set echo off
SQL> grant plustrace to public;
順利授權.
SQL> grant plustrace to PMS;
順利授權.
SQL> conn pms/pms
SQL> set autotrace on;
SP2-0618: 找不到階段作業 ID. 請檢查是否啟用 PLUSTRACE 角色
SP2-0611: 啟動 STATISTICS 報表時發生錯誤
SQL> SELECT * FROM DBA_ROLE_PRIVS WHERE GRANTEE='PMS';
GRANTEE GRANTED_ROLE ADM DEF
------------------------------ ------------------------------ --- ---
PMS PLUSTRACE NO NO
PMS CONNECT NO YES
PMS RESOURCE NO YES
----Oracle10g的default_role為YES，但11g的default_role為NO所以在11g下還需要使用alter user -- default role all;命令修改默認值。
SQL> desc dba_role_privs;
名稱 空值? 類型
----------------------------------------- -------- ----------------------------
GRANTEE VARCHAR2(30)
GRANTED_ROLE NOT NULL VARCHAR2(30)
ADMIN_OPTION VARCHAR2(3)
DEFAULT_ROLE VARCHAR2(3)
SQL> alter user PMS default role all;
已更改使用者.
SQL> SELECT * FROM DBA_ROLE_PRIVS WHERE GRANTEE='PMS';
GRANTEE GRANTED_ROLE ADM DEF
------------------------------ ------------------------------ --- ---
PMS PLUSTRACE NO YES
PMS CONNECT NO YES
PMS RESOURCE NO YES
SQL> conn pms
輸入密碼: ***********
已連線.
SQL> set autotrace on;
SQL> set autotrace off;
SQL> set autotrace on;
select wsc_no,manhour,pre_start_time,pre_end_time,in_qty,b_mfc_qty,e_mfc_qty,out_qtyt_qty
from pt_work_flow_dt
where wflow_no='431211120274'
4 and wsc_seq='001';
沒有任何資料列被選取
執行計畫
----------------------------------------------------------
Plan hash value: 3964334498
--------------------------------------------------------------------------------
------------------
| Id | Operation | Name | Rows | Bytes | Cost
(%CPU)| Time |
--------------------------------------------------------------------------------
------------------
| 0 | SELECT STATEMENT | | 1 | 50 | 3
(0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| PT_WORK_FLOW_DT | 1 | 50 | 3
(0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | PK_PT_WORK_FLOW_DT | 1 | | 2
(0)| 00:00:01 |
--------------------------------------------------------------------------------
------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("WFLOW_NO"='431211120274' AND "WSC_SEQ"='001')
統計資料
----------------------------------------------------------
1 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
693 bytes sent via SQL*Net to client
405 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
SQL> set autotrace off;