?oracle 之數據字典屣履造門。（更新中）
?
?
? 今天是2013-06-20，哎，寫這篇筆記的時候，我發現我是一個非常懶惰的人，這篇文章本該昨天就完成的，想起了錢鶴灘的《明日歌》，真是“世人若被明日累，春去秋來老將至”。
???? oracle數據字典包括四部分，分別是RDBMS(X$),數據字典表、動態性能視圖（v$）和數據字典視圖。
1)RDBMS(X$)內部 表：
該部分內容是oracle最低層的表數據，這些表維持著oracle的整個視圖，就像我們人體的血液在我們人體流動維持生命一樣。
因此這些表只能查看、研究不建議修改。oracle對修改內部表產生的后果不負責技術支持工作。?
在查看參數文件的時候我曾經提到x$ksppi和X$ksppcv這兩個內部表。
研究內部表：
oracle@oracle:~> sqlplus "/as sysdba"
???? oracle數據字典包括四部分，分別是RDBMS(X$),數據字典表、動態性能視圖（v$）和數據字典視圖。
1)RDBMS(X$)內部 表：
該部分內容是oracle最低層的表數據，這些表維持著oracle的整個視圖，就像我們人體的血液在我們人體流動維持生命一樣。
因此這些表只能查看、研究不建議修改。oracle對修改內部表產生的后果不負責技術支持工作。?
在查看參數文件的時候我曾經提到x$ksppi和X$ksppcv這兩個內部表。
研究內部表：
oracle@oracle:~> sqlplus "/as sysdba"
SQL*Plus: Release 11.1.0.6.0 - Production on 星期四 6月 20 20:38:47 2013
Copyright (c) 1982, 2007, Oracle.? All rights reserved.
連接到:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SYS@orcl#select open_mode from v$database;
OPEN_MODE
--------------------
READ WRITE
--------------------
READ WRITE
SYS@orcl#grant select on x$ksppi to xiaohai;
grant select on x$ksppi to xiaohai
??????????????? *
第 1 行出現錯誤:
ORA-02030: 只能從固定的表/視圖查詢
grant select on x$ksppi to xiaohai
??????????????? *
第 1 行出現錯誤:
ORA-02030: 只能從固定的表/視圖查詢
可以看出內部表不允許進行授權。
研究內部表方法如下：
SYS@orcl#set autotrace trace explain;
SYS@orcl#select * from v$parameter;
研究內部表方法如下：
SYS@orcl#set autotrace trace explain;
SYS@orcl#select * from v$parameter;
執行計劃
----------------------------------------------------------
Plan hash value: 1128103955
----------------------------------------------------------
Plan hash value: 1128103955
------------------------------------------------------------------------------
| Id? | Operation???????? | Name???? | Rows? | Bytes | Cost (%CPU)| Time???? |
------------------------------------------------------------------------------
|?? 0 | SELECT STATEMENT? |????????? |???? 1 |? 4414 |???? 1 (100)| 00:00:01 |
|*? 1 |? HASH JOIN??????? |????????? |???? 1 |? 4414 |???? 1 (100)| 00:00:01 |
|*? 2 |?? FIXED TABLE FULL| X$KSPPI ? |???? 1 |?? 249 |???? 0?? (0)| 00:00:01 |
|?? 3 |?? FIXED TABLE FULL | X$KSPPCV |?? 100 |?? 406K|???? 0?? (0)| 00:00:01 |
------------------------------------------------------------------------------
| Id? | Operation???????? | Name???? | Rows? | Bytes | Cost (%CPU)| Time???? |
------------------------------------------------------------------------------
|?? 0 | SELECT STATEMENT? |????????? |???? 1 |? 4414 |???? 1 (100)| 00:00:01 |
|*? 1 |? HASH JOIN??????? |????????? |???? 1 |? 4414 |???? 1 (100)| 00:00:01 |
|*? 2 |?? FIXED TABLE FULL| X$KSPPI ? |???? 1 |?? 249 |???? 0?? (0)| 00:00:01 |
|?? 3 |?? FIXED TABLE FULL | X$KSPPCV |?? 100 |?? 406K|???? 0?? (0)| 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
---------------------------------------------------
?? 1 - access("X"."INDX"="Y"."INDX")
?????? filter(TRANSLATE("KSPPINM",'_','#') NOT LIKE '#%' OR
????????????? "KSPPSTDF"='FALSE' OR BITAND("KSPPSTVF",5)>0)
?? 2 - filter("X"."INST_ID"=USERENV('INSTANCE') AND
????????????? TRANSLATE("KSPPINM",'_','#') NOT LIKE '##%')
?????? filter(TRANSLATE("KSPPINM",'_','#') NOT LIKE '#%' OR
????????????? "KSPPSTDF"='FALSE' OR BITAND("KSPPSTVF",5)>0)
?? 2 - filter("X"."INST_ID"=USERENV('INSTANCE') AND
????????????? TRANSLATE("KSPPINM",'_','#') NOT LIKE '##%')
SYS@orcl#
autotrace 語句研究：
該語句工具主要是研究統計sql語句的執行計劃并生成報告，進而對dml語句進行檢測和優化。
Generates a report on the execution path used by the SQL optimizer and the statement execution statistics. The report is useful to monitor and tune the performance of DML statements.
Generates a report on the execution path used by the SQL optimizer and the statement execution statistics. The report is useful to monitor and tune the performance of DML statements.
set? autotrace off;??? 不產生報告和執行計劃，默認設置。
set autotrace on? explain;? 生成執行計劃并顯示查詢信息；
set autotrace on statistics；只生成語句的統計信息并顯示查詢信息。
set autotrace on ;?? 即生成語句報告有顯示語句的執行統計信息和計劃
set autotrace traceonly;? 和on一樣，區別就是不顯示查詢信息，查詢的數據依然會fetch但是不會print，只顯示統計信息和執行計劃；
eg：
SYS@orcl#set autotrace on explain
SYS@orcl#select * from t2;
set autotrace on? explain;? 生成執行計劃并顯示查詢信息；
set autotrace on statistics；只生成語句的統計信息并顯示查詢信息。
set autotrace on ;?? 即生成語句報告有顯示語句的執行統計信息和計劃
set autotrace traceonly;? 和on一樣，區別就是不顯示查詢信息，查詢的數據依然會fetch但是不會print，只顯示統計信息和執行計劃；
eg：
SYS@orcl#set autotrace on explain
SYS@orcl#select * from t2;
???? EMPNO ENAME
---------- --------------------
????? 7521 WARD
---------- --------------------
????? 7521 WARD
執行計劃
----------------------------------------------------------
Plan hash value: 1513984157
--------------------------------------------------------------------------
| Id? | Operation???????? | Name | Rows? | Bytes | Cost (%CPU)| Time???? |
--------------------------------------------------------------------------
|?? 0 | SELECT STATEMENT? |????? |???? 1 |??? 20 |???? 2?? (0)| 00:00:01 |
|?? 1 |? TABLE ACCESS FULL| T2?? |???? 1 |??? 20 |???? 2?? (0)| 00:00:01 |
--------------------------------------------------------------------------
| Id? | Operation???????? | Name | Rows? | Bytes | Cost (%CPU)| Time???? |
--------------------------------------------------------------------------
|?? 0 | SELECT STATEMENT? |????? |???? 1 |??? 20 |???? 2?? (0)| 00:00:01 |
|?? 1 |? TABLE ACCESS FULL| T2?? |???? 1 |??? 20 |???? 2?? (0)| 00:00:01 |
--------------------------------------------------------------------------
Note
-----
?? - dynamic sampling used for this statement
-----
?? - dynamic sampling used for this statement
SYS@orcl#set autotrace on statistices;
SP2-0735: 未知的 SET 選項開頭 "statistice..."
SYS@orcl#set autotrace on statistics;
SYS@orcl#select * from t2;
SP2-0735: 未知的 SET 選項開頭 "statistice..."
SYS@orcl#set autotrace on statistics;
SYS@orcl#select * from t2;
???? EMPNO ENAME
---------- --------------------
????? 7521 WARD
---------- --------------------
????? 7521 WARD
統計信息
----------------------------------------------------------
????????? 0? recursive calls
????????? 0? db block gets
????????? 3? consistent gets
????????? 0? physical reads
????????? 0? redo size
??????? 600? bytes sent via SQL*Net to client
??????? 524? bytes received via SQL*Net from client
????????? 2? SQL*Net roundtrips to/from client
????????? 0? sorts (memory)
????????? 0? sorts (disk)
????????? 1? rows processed
SYS@orcl#select * from t2;
???? EMPNO ENAME
---------- --------------------
????? 7521 WARD
---------- --------------------
????? 7521 WARD
執行計劃
----------------------------------------------------------
Plan hash value: 1513984157
--------------------------------------------------------------------------
| Id? | Operation???????? | Name | Rows? | Bytes | Cost (%CPU)| Time???? |
--------------------------------------------------------------------------
|?? 0 | SELECT STATEMENT? |????? |???? 1 |??? 20 |???? 2?? (0)| 00:00:01 |
|?? 1 |? TABLE ACCESS FULL| T2?? |???? 1 |??? 20 |???? 2?? (0)| 00:00:01 |
--------------------------------------------------------------------------
| Id? | Operation???????? | Name | Rows? | Bytes | Cost (%CPU)| Time???? |
--------------------------------------------------------------------------
|?? 0 | SELECT STATEMENT? |????? |???? 1 |??? 20 |???? 2?? (0)| 00:00:01 |
|?? 1 |? TABLE ACCESS FULL| T2?? |???? 1 |??? 20 |???? 2?? (0)| 00:00:01 |
--------------------------------------------------------------------------
Note
-----
?? - dynamic sampling used for this statement
-----
?? - dynamic sampling used for this statement
統計信息
----------------------------------------------------------
????????? 0? recursive calls
????????? 0? db block gets
????????? 3? consistent gets
????????? 0? physical reads
????????? 0? redo size
??????? 600? bytes sent via SQL*Net to client
??????? 524? bytes received via SQL*Net from client
????????? 2? SQL*Net roundtrips to/from client
????????? 0? sorts (memory)
????????? 0? sorts (disk)
????????? 1? rows processed
SYS@orcl#
SYS@orcl#set autotrace traceonly;
SYS@orcl#select * from t;
SYS@orcl#select * from t;
執行計劃
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id? | Operation???????? | Name | Rows? | Bytes | Cost (%CPU)| Time???? |
--------------------------------------------------------------------------
|?? 0 | SELECT STATEMENT? |????? |???? 5 |?? 180 |???? 3?? (0)| 00:00:01 |
|?? 1 |? TABLE ACCESS FULL| T??? |???? 5 |?? 180 |???? 3?? (0)| 00:00:01 |
--------------------------------------------------------------------------
| Id? | Operation???????? | Name | Rows? | Bytes | Cost (%CPU)| Time???? |
--------------------------------------------------------------------------
|?? 0 | SELECT STATEMENT? |????? |???? 5 |?? 180 |???? 3?? (0)| 00:00:01 |
|?? 1 |? TABLE ACCESS FULL| T??? |???? 5 |?? 180 |???? 3?? (0)| 00:00:01 |
--------------------------------------------------------------------------
統計信息
----------------------------------------------------------
????????? 0? recursive calls
????????? 0? db block gets
????????? 4? consistent gets
????????? 0? physical reads
????????? 0? redo size
?????? 1184? bytes sent via SQL*Net to client
??????? 524? bytes received via SQL*Net from client
????????? 2? SQL*Net roundtrips to/from client
????????? 0? sorts (memory)
????????? 0? sorts (disk)
????????? 5? rows processed
?
總結：該工具可以查看語句的統計信息可以變化命令使用非常靈活，
SYS@orcl#set autotrace
用法: SET AUTOT[RACE] {OFF | ON | TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]]
便于語句調優。在此記錄加深一下印象。呵呵，原來這個工具還有多種花樣。
用法: SET AUTOT[RACE] {OFF | ON | TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]]
便于語句調優。在此記錄加深一下印象。呵呵，原來這個工具還有多種花樣。
? X$kvit 內部表：英文名字叫：kernel layer performance layer information tables transitory instance parameters；
#select kvitval,kvittag,kvitdsc from x$kvit
? 2? ;
?? KVITVAL KVITTAG????????????? KVITDSC
---------- -------------------- --------------------------------------------------------------------------------
???????? 1 ksbcpu?????????????? number of logical CPUs in the system used by Oracle
???????? 0 ksbcpucore?????????? number of physical CPU cores in the system used by Oracle
???????? 0 ksbcpusocket???????? number of physical CPU sockets in the system used by Oracle
???????? 1 ksbcpu_hwm?????????? high water mark of number of CPUs used by Oracle
???????? 0 ksbcpucore_hwm?????? high water mark of number of CPU cores on system
???????? 0 ksbcpusocket_hwm???? high water mark of number of CPU sockets on system
???????? 1 ksbcpu_actual??????? number of available CPUs in the system
???????? 1 ksbcpu_dr??????????? CPU dynamic reconfiguration supported
???? 33456 kcbnbh?????????????? number of buffers
?????? 25 kcbldq?????????????? large dirty queue if kcbclw reaches this
??????? 40 kcbfsp?????????????? Max percentage of LRU list foreground can scan for free
---------- -------------------- --------------------------------------------------------------------------------
???????? 1 ksbcpu?????????????? number of logical CPUs in the system used by Oracle
???????? 0 ksbcpucore?????????? number of physical CPU cores in the system used by Oracle
???????? 0 ksbcpusocket???????? number of physical CPU sockets in the system used by Oracle
???????? 1 ksbcpu_hwm?????????? high water mark of number of CPUs used by Oracle
???????? 0 ksbcpucore_hwm?????? high water mark of number of CPU cores on system
???????? 0 ksbcpusocket_hwm???? high water mark of number of CPU sockets on system
???????? 1 ksbcpu_actual??????? number of available CPUs in the system
???????? 1 ksbcpu_dr??????????? CPU dynamic reconfiguration supported
???? 33456 kcbnbh?????????????? number of buffers
?????? 25 kcbldq?????????????? large dirty queue if kcbclw reaches this
??????? 40 kcbfsp?????????????? Max percentage of LRU list foreground can scan for free
?? KVITVAL KVITTAG????????????? KVITDSC
---------- -------------------- --------------------------------------------------------------------------------
???????? 2 kcbcln?????????????? Initial percentage of LRU list to keep clean
?????? 800 kcbnbf?????????????? number buffer objects
???????? 0 kcbwst?????????????? Flag that indicates recovery or db suspension
???????? 0 kcteln?????????????? Error Log Number for thread open
???????? 0 kcvgcw?????????????? SGA: opcode for checkpoint cross-instance call
???????? 0 kcvgcw?????????????? SGA:opcode for pq checkpoint cross-instance call
---------- -------------------- --------------------------------------------------------------------------------
???????? 2 kcbcln?????????????? Initial percentage of LRU list to keep clean
?????? 800 kcbnbf?????????????? number buffer objects
???????? 0 kcbwst?????????????? Flag that indicates recovery or db suspension
???????? 0 kcteln?????????????? Error Log Number for thread open
???????? 0 kcvgcw?????????????? SGA: opcode for checkpoint cross-instance call
???????? 0 kcvgcw?????????????? SGA:opcode for pq checkpoint cross-instance call
已選擇17行。
SYS@orcl#
kcbldq和kcbfsp是關于dbwr進程進行臟數據寫入磁盤的條件。
kcbldq和kcbfsp是關于dbwr進程進行臟數據寫入磁盤的條件。
如下：
SYS@orcl#@getsp.sql
輸入 par 的值:? db_large_dirty
輸入 par 的值:? db_large_dirty
NAME?????????????????????????? VALUE??????????????? PDESC
------------------------------ -------------------- --------------------------------------------------
_db_large_dirty_queue????????? 25?????????????????? Number of buffers which force dirty queue to be wr
??????????????????????????????????????????????????? itten
------------------------------ -------------------- --------------------------------------------------
_db_large_dirty_queue????????? 25?????????????????? Number of buffers which force dirty queue to be wr
??????????????????????????????????????????????????? itten
SYS@orcl#
SYS@orcl#@getsp.sql
輸入 par 的值:? db_block_max_scan
輸入 par 的值:? db_block_max_scan
NAME?????????????????????????? VALUE??????????????? PDESC
------------------------------ -------------------- --------------------------------------------------
_db_block_max_scan_pct???????? 40?????????????????? Percentage of buffers to inspect when looking for
??????????????????????????????????????????????????? free
------------------------------ -------------------- --------------------------------------------------
_db_block_max_scan_pct???????? 40?????????????????? Percentage of buffers to inspect when looking for
??????????????????????????????????????????????????? free
SYS@orcl#
?
總結：從這些信息我們可以研究出了lru原理，知道了dbwr進行臟數據寫入磁盤的條件。
為了學習，進行嘗試修改如下：
SYS@orcl#alter system set "_db_block_max_scan_pct"=50 scope=spfile;
系統已更改。
SYS@orcl#startup force;
ORACLE 例程已經啟動。
ORACLE 例程已經啟動。
Total System Global Area? 805875712 bytes
Fixed Size????????????????? 2148720 bytes
Variable Size???????????? 562038416 bytes
Database Buffers????????? 234881024 bytes
Redo Buffers??????????????? 6807552 bytes
Fixed Size????????????????? 2148720 bytes
Variable Size???????????? 562038416 bytes
Database Buffers????????? 234881024 bytes
Redo Buffers??????????????? 6807552 bytes
數據庫已經打開。
SYS@orcl#show parameter _db_block
SYS@orcl#show parameter _db_block
NAME???????????????????????????????? TYPE?????????????????? VALUE
------------------------------------ ---------------------- ------------------------------
_db_block_max_scan_pct?????????????? integer??????????????? 50
SYS@orcl#exit?
從 Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options 斷開
oracle@oracle:~/test> sqlplus "/as sysdba"
------------------------------------ ---------------------- ------------------------------
_db_block_max_scan_pct?????????????? integer??????????????? 50
SYS@orcl#exit?
從 Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options 斷開
oracle@oracle:~/test> sqlplus "/as sysdba"
SQL*Plus: Release 11.1.0.6.0 - Production on 星期四 6月 20 21:25:47 2013
Copyright (c) 1982, 2007, Oracle.? All rights reserved.
連 接到:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SYS@orcl#@getsp.sql
輸入 par 的值:? db_block_max_scan
原值??? 2: where x.indx=y.indx and x.ksppinm like '%&par%'
新值??? 2: where x.indx=y.indx and x.ksppinm like '%db_block_max_scan%'
輸入 par 的值:? db_block_max_scan
原值??? 2: where x.indx=y.indx and x.ksppinm like '%&par%'
新值??? 2: where x.indx=y.indx and x.ksppinm like '%db_block_max_scan%'
NAME?????????????????????????? VALUE??????????????? PDESC
------------------------------ -------------------- --------------------------------------------------
_db_block_max_scan_pct???????? 50?????????????????? Percentage of buffers to inspect when looking for
??????????????????????????????????????????????????? free
------------------------------ -------------------- --------------------------------------------------
_db_block_max_scan_pct???????? 50?????????????????? Percentage of buffers to inspect when looking for
??????????????????????????????????????????????????? free
SYS@orcl#
?
可以看到我們修改的實施在spfile中加入隱含參數，然后在數據啟動初始化的時候會引用隱含參數的值。（不建議修改）
SYS@orcl#alter system reset "_db_block_max_scan_pct";?????
系統已更改。
SYS@orcl#startup force;
2）數據字典表：
在創建數據庫的時候安裝sql.bsp文件進行創建，在數據庫啟動的時候進行先關字典表的創建。可以通過跟蹤數據庫啟動的過程進行分析。
eg:
startup nomount;
alter session set events '10046 trace nam context level 12';
alter database mount;
alter database open;
如下是截取的跟蹤文件信息：
509 CREATE TABLE USER$("USER#" NUMBER NOT NULL,"NAME" VARCHAR2(30) NOT NULL,"TYPE#" NUMBER NOT NULL,"PASSWORD" VARCHAR2(30),"DATATS#" NUMBER NOT NULL,"TEMPTS#" NUM??????? BER NOT NULL,"CTIME" DATE NOT NULL,"PTIME" DATE,"EXPTIME" DATE,"LTIME" DATE,"RESOURCE$" NUMBER NOT NULL,"AUDIT$" VARCHAR2(38),"DEFROLE" NUMBER NOT NULL,"DEFGRP??????? #" NUMBER,"DEFGRP_SEQ#" NUMBER,"ASTATUS" NUMBER NOT NULL,"LCOUNT" NUMBER NOT NULL,"DEFSCHCLASS" VARCHAR2(30),"EXT_USERNAME" VARCHAR2(4000),"SPARE1" NUMBER,"SPA??????? RE2" NUMBER,"SPARE3" NUMBER,"SPARE4" VARCHAR2(1000),"SPARE5" VARCHAR2(1000),"SPARE6" DATE) STORAGE (? OBJNO 22 TABNO 1) CLUSTER C_USER#(USER#)
??? 510 END OF STMT
??? 511 PARSE #1:c=0,e=456,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,tim=1371736252499680
??? 512 BINDS #1:
??? 513 EXEC #1:c=0,e=217,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1371736252499956
=====================
??? 480 PARSING IN CURSOR #1 len=637 dep=1 uid=0 oct=1 lid=0 tim=1371736252496043 hv=1077251290 ad='8fd93c08' sqlid='3cgdmzx03b36u'
??? 481 CREATE TABLE OBJ$("OBJ#" NUMBER NOT NULL,"DATAOBJ#" NUMBER,"OWNER#" NUMBER NOT NULL,"NAME" VARCHAR2(30) NOT NULL,"NAMESPACE" NUMBER NOT NULL,"SUBNAME" VARCHAR2??????? (30),"TYPE#" NUMBER NOT NULL,"CTIME" DATE NOT NULL,"MTIME" DATE NOT NULL,"STIME" DATE NOT NULL,"STATUS" NUMBER NOT NULL,"REMOTEOWNER" VARCHAR2(30),"LINKNAME" V??????? ARCHAR2(128),"FLAGS" NUMBER,"OID$" RAW(16),"SPARE1" NUMBER,"SPARE2" NUMBER,"SPARE3" NUMBER,"SPARE4" VARCHAR2(1000),"SPARE5" VARCHAR2(1000),"SPARE6" DATE) PCTFR??????? EE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE (? INITIAL 16K NEXT 1024K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 OBJNO 18 EXTENTS (FILE 1 BLOCK 121)??????? )
??? 482 END OF STMT
??? 483 PARSE #1:c=0,e=552,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,tim=1371736252496039
??? 484 BINDS #1:
??? 485 EXEC #1:c=0,e=205,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1371736252496308
當我們創建表的時候，其實是向先關數據字典寫入數據，分別是obj$,con$,col$,tab$等等。可以跟蹤sql語句進行分析。
當然了我們創建完表之后，可以通過包進行查看我們當時的ddl語句。
eg：
SYS@orcl#select table_name from user_tables where table_name='T';
在創建數據庫的時候安裝sql.bsp文件進行創建，在數據庫啟動的時候進行先關字典表的創建。可以通過跟蹤數據庫啟動的過程進行分析。
eg:
startup nomount;
alter session set events '10046 trace nam context level 12';
alter database mount;
alter database open;
如下是截取的跟蹤文件信息：
509 CREATE TABLE USER$("USER#" NUMBER NOT NULL,"NAME" VARCHAR2(30) NOT NULL,"TYPE#" NUMBER NOT NULL,"PASSWORD" VARCHAR2(30),"DATATS#" NUMBER NOT NULL,"TEMPTS#" NUM??????? BER NOT NULL,"CTIME" DATE NOT NULL,"PTIME" DATE,"EXPTIME" DATE,"LTIME" DATE,"RESOURCE$" NUMBER NOT NULL,"AUDIT$" VARCHAR2(38),"DEFROLE" NUMBER NOT NULL,"DEFGRP??????? #" NUMBER,"DEFGRP_SEQ#" NUMBER,"ASTATUS" NUMBER NOT NULL,"LCOUNT" NUMBER NOT NULL,"DEFSCHCLASS" VARCHAR2(30),"EXT_USERNAME" VARCHAR2(4000),"SPARE1" NUMBER,"SPA??????? RE2" NUMBER,"SPARE3" NUMBER,"SPARE4" VARCHAR2(1000),"SPARE5" VARCHAR2(1000),"SPARE6" DATE) STORAGE (? OBJNO 22 TABNO 1) CLUSTER C_USER#(USER#)
??? 510 END OF STMT
??? 511 PARSE #1:c=0,e=456,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,tim=1371736252499680
??? 512 BINDS #1:
??? 513 EXEC #1:c=0,e=217,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1371736252499956
=====================
??? 480 PARSING IN CURSOR #1 len=637 dep=1 uid=0 oct=1 lid=0 tim=1371736252496043 hv=1077251290 ad='8fd93c08' sqlid='3cgdmzx03b36u'
??? 481 CREATE TABLE OBJ$("OBJ#" NUMBER NOT NULL,"DATAOBJ#" NUMBER,"OWNER#" NUMBER NOT NULL,"NAME" VARCHAR2(30) NOT NULL,"NAMESPACE" NUMBER NOT NULL,"SUBNAME" VARCHAR2??????? (30),"TYPE#" NUMBER NOT NULL,"CTIME" DATE NOT NULL,"MTIME" DATE NOT NULL,"STIME" DATE NOT NULL,"STATUS" NUMBER NOT NULL,"REMOTEOWNER" VARCHAR2(30),"LINKNAME" V??????? ARCHAR2(128),"FLAGS" NUMBER,"OID$" RAW(16),"SPARE1" NUMBER,"SPARE2" NUMBER,"SPARE3" NUMBER,"SPARE4" VARCHAR2(1000),"SPARE5" VARCHAR2(1000),"SPARE6" DATE) PCTFR??????? EE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE (? INITIAL 16K NEXT 1024K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 OBJNO 18 EXTENTS (FILE 1 BLOCK 121)??????? )
??? 482 END OF STMT
??? 483 PARSE #1:c=0,e=552,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,tim=1371736252496039
??? 484 BINDS #1:
??? 485 EXEC #1:c=0,e=205,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1371736252496308
當我們創建表的時候，其實是向先關數據字典寫入數據，分別是obj$,con$,col$,tab$等等。可以跟蹤sql語句進行分析。
當然了我們創建完表之后，可以通過包進行查看我們當時的ddl語句。
eg：
SYS@orcl#select table_name from user_tables where table_name='T';
TABLE_NAME
------------------------------------------------------------
T
------------------------------------------------------------
T
SYS@orcl#select dbms_metadata.get_ddl('TABLE','T') FROM DUAL;
DBMS_METADATA.GET_DDL('TABLE','T')
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
? CREATE TABLE "SYS"."T"
?? (??? "TABLESPACE_NAME" VARCHAR2(30) NOT NULL ENABLE,
?? (??? "TABLESPACE_NAME" VARCHAR2(30) NOT NULL ENABLE,
SYS@orcl#SET LONG 20000
SYS@orcl#R
? 1* select dbms_metadata.get_ddl ('TABLE','T') FROM DUAL
DBMS_METADATA.GET_DDL('TABLE','T')
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
? CREATE TABLE "SYS"."T"
?? (??? "TABLESPACE_NAME" VARCHAR2(30) NOT NULL ENABLE,
??????? "USERNAME" VARCHAR2(30) NOT NULL ENABLE,
??????? "BYTES" NUMBER,
??????? "MAX_BYTES" NUMBER,
??????? "BLOCKS" NUMBER,
??????? "MAX_BLOCKS" NUMBER,
??????? "DROPPED" VARCHAR2(3)
?? ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
? STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
?? (??? "TABLESPACE_NAME" VARCHAR2(30) NOT NULL ENABLE,
??????? "USERNAME" VARCHAR2(30) NOT NULL ENABLE,
??????? "BYTES" NUMBER,
??????? "MAX_BYTES" NUMBER,
??????? "BLOCKS" NUMBER,
??????? "MAX_BLOCKS" NUMBER,
??????? "DROPPED" VARCHAR2(3)
?? ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
? STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
DBMS_METADATA.GET_DDL('TABLE','T')
--------------------------------------------------------------------------------
? PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
? TABLESPACE "USERS"
--------------------------------------------------------------------------------
? PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
? TABLESPACE "USERS"
研究一下這個dbms_metadata包。
這個包的接受如下：
Overview
This pkg implements the mdAPI, a means to retrieve the aggregated
definitions of database objects as either XML docs. or their creation DDL,
or to submit the XML documents to execute the DDL.
-------------------------------------------------------------------
SECURITY
This package is owned by SYS with execute access granted to PUBLIC.
It runs with invokers rights, i.e., with the security profile of
the caller.? It calls DBMS_METADATA_INT to perform privileged
functions.
The object views defined in catmeta.sql implement the package's security
policy via the WHERE clause on the public views which include syntax to
control user access to metadata: if the current user is SYS or has
SELECT_CATALOG_ROLE, then all objects are visible; otherwise, only
objects in the schema of the current user are visible.
然后我們在看看這個function是get_ddl內容如下：
? FUNCTION get_ddl (
??????????????? object_type???? IN? VARCHAR2,
??????????????? name??????????? IN? VARCHAR2,
??????????????? schema????????? IN? VARCHAR2 DEFAULT NULL,
??????????????? version???????? IN? VARCHAR2 DEFAULT 'COMPATIBLE',
??????????????? model?????????? IN? VARCHAR2 DEFAULT 'ORACLE',
??????????????? transform?????? IN? VARCHAR2 DEFAULT 'DDL')
??????? RETURN CLOB;
那么我剛剛傳的參數是object_type為table，name為T。 另外這個包還有很多功能，后續多多實踐進行學習。
3）靜態數據字典視圖。
Overview
This pkg implements the mdAPI, a means to retrieve the aggregated
definitions of database objects as either XML docs. or their creation DDL,
or to submit the XML documents to execute the DDL.
-------------------------------------------------------------------
SECURITY
This package is owned by SYS with execute access granted to PUBLIC.
It runs with invokers rights, i.e., with the security profile of
the caller.? It calls DBMS_METADATA_INT to perform privileged
functions.
The object views defined in catmeta.sql implement the package's security
policy via the WHERE clause on the public views which include syntax to
control user access to metadata: if the current user is SYS or has
SELECT_CATALOG_ROLE, then all objects are visible; otherwise, only
objects in the schema of the current user are visible.
然后我們在看看這個function是get_ddl內容如下：
? FUNCTION get_ddl (
??????????????? object_type???? IN? VARCHAR2,
??????????????? name??????????? IN? VARCHAR2,
??????????????? schema????????? IN? VARCHAR2 DEFAULT NULL,
??????????????? version???????? IN? VARCHAR2 DEFAULT 'COMPATIBLE',
??????????????? model?????????? IN? VARCHAR2 DEFAULT 'ORACLE',
??????????????? transform?????? IN? VARCHAR2 DEFAULT 'DDL')
??????? RETURN CLOB;
那么我剛剛傳的參數是object_type為table，name為T。 另外這個包還有很多功能，后續多多實踐進行學習。
3）靜態數據字典視圖。
?? 包括dba_視圖，all_視圖，user_視圖。上級包括下級。如dba_tables,all_tables,user_tables;
eg:
SYS@orcl#set autotrace traceonly explain;
SYS@orcl#select * from dba_tables;
Predicate Information (identified by operation id):
---------------------------------------------------
?? 1 - access("KSPPI"."INDX"="KSPPCV"."INDX")
?? 4 - access("T"."BOBJ#"="CO"."OBJ#"(+))
?? 5 - access("CX"."OWNER#"="CU"."USER#"(+))
?? 7 - access("T"."DATAOBJ#"="CX"."OBJ#"(+))
?? 8 - access("O"."OWNER#"="U"."USER#")
? 10 - access("T"."TS#"="TS"."TS#")
? 12 - access("T"."FILE#"="S"."FILE#"(+) AND "T"."BLOCK#"="S"."BLOCK#"(+) AND
????????????? "T"."TS#"="S"."TS#"(+))
? 13 - access("O"."OBJ#"="T"."OBJ#")
? 14 - filter(BITAND("T"."PROPERTY",1)=0)
? 15 - filter(BITAND("O"."FLAGS",128)=0)
? 20 - filter("KSPPI"."KSPPINM"='_dml_monitoring_enabled')
?? 4 - access("T"."BOBJ#"="CO"."OBJ#"(+))
?? 5 - access("CX"."OWNER#"="CU"."USER#"(+))
?? 7 - access("T"."DATAOBJ#"="CX"."OBJ#"(+))
?? 8 - access("O"."OWNER#"="U"."USER#")
? 10 - access("T"."TS#"="TS"."TS#")
? 12 - access("T"."FILE#"="S"."FILE#"(+) AND "T"."BLOCK#"="S"."BLOCK#"(+) AND
????????????? "T"."TS#"="S"."TS#"(+))
? 13 - access("O"."OBJ#"="T"."OBJ#")
? 14 - filter(BITAND("T"."PROPERTY",1)=0)
? 15 - filter(BITAND("O"."FLAGS",128)=0)
? 20 - filter("KSPPI"."KSPPINM"='_dml_monitoring_enabled')
SYS@orcl#select * from all_tables;
Predicate Information (identified by operation id):
---------------------------------------------------
---------------------------------------------------
?
1 - filter("O"."OWNER#"=USERENV('SCHEMAID') OR? EXISTS (SELECT 0 FROM
????????????? "SYS"."OBJAUTH$" "OA",SYS."X$KZSRO" "X$KZSRO" WHERE "GRANTEE#"="KZ
SROROL" AND
????????????? "SYS"."OBJAUTH$" "OA",SYS."X$KZSRO" "X$KZSRO" WHERE "GRANTEE#"="KZ
SROROL" AND
????????????? "OA"."OBJ#"=:B1) OR? EXISTS (SELECT 0 FROM SYS."X$KZSPR" "X$KZSPR"
WHERE
WHERE
????????????? "INST_ID"=USERENV('INSTANCE') AND ((-"KZSPRPRV")=(-45) OR (-"KZSPR
PRV")=(-47) OR
PRV")=(-47) OR
????????????? (-"KZSPRPRV")=(-48) OR (-"KZSPRPRV")=(-49) OR (-"KZSPRPRV")=(-50))
))
))
SYS@orcl#select * from user_tables;
Predicate Information (identified by operation id):
---------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
?? 1 - access("KSPPI"."INDX"="KSPPCV"."INDX")
?? 4 - access("T"."BOBJ#"="CO"."OBJ#"(+))
?? 5 - access("CX"."OWNER#"="CU"."USER#"(+))
?? 7 - access("T"."DATAOBJ#"="CX"."OBJ#"(+))
?? 8 - access("T"."TS#"="TS"."TS#")
? 10 - access("T"."FILE#"="S"."FILE#"(+) AND "T"."BLOCK#"="S"."BLOCK#"(+) AND
????????????? "T"."TS#"="S"."TS#"(+))
? 12 - filter("O"."OWNER#"=USERENV('SCHEMAID') AND BITAND("O"."FLAGS",128)=0)
? 13 - filter(BITAND("T"."PROPERTY",1)=0)
? 14 - access("O"."OBJ#"="T"."OBJ#")
? 19 - filter("KSPPI"."KSPPINM"='_dml_monitoring_enabled')
顯而易見了。呵呵。
4）動態視圖
明天繼續學習。。。。
+++++++++++++++++++++++++++++++++++++++++++↖(^ω^)↗小海￥￥￥￥￥￥￥￥￥￥￥
?? 4 - access("T"."BOBJ#"="CO"."OBJ#"(+))
?? 5 - access("CX"."OWNER#"="CU"."USER#"(+))
?? 7 - access("T"."DATAOBJ#"="CX"."OBJ#"(+))
?? 8 - access("T"."TS#"="TS"."TS#")
? 10 - access("T"."FILE#"="S"."FILE#"(+) AND "T"."BLOCK#"="S"."BLOCK#"(+) AND
????????????? "T"."TS#"="S"."TS#"(+))
? 12 - filter("O"."OWNER#"=USERENV('SCHEMAID') AND BITAND("O"."FLAGS",128)=0)
? 13 - filter(BITAND("T"."PROPERTY",1)=0)
? 14 - access("O"."OBJ#"="T"."OBJ#")
? 19 - filter("KSPPI"."KSPPINM"='_dml_monitoring_enabled')
顯而易見了。呵呵。
4）動態視圖
明天繼續學習。。。。
+++++++++++++++++++++++++++++++++++++++++++↖(^ω^)↗小海￥￥￥￥￥￥￥￥￥￥￥
?
更多文章、技術交流、商務合作、聯系博主
微信掃碼或搜索：z360901061
微信掃一掃加我為好友
QQ號聯系： 360901061
您的支持是博主寫作最大的動力，如果您喜歡我的文章，感覺我的文章對您有幫助，請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點擊下面給點支持吧，站長非常感激您！手機微信長按不能支付解決辦法：請將微信支付二維碼保存到相冊，切換到微信，然后點擊微信右上角掃一掃功能，選擇支付二維碼完成支付。
【本文對您有幫助就好】元
