oracle之数据字典屣履造门(六)
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
DBMS_METADATA.GET_DDL('TABLE','T')
--------------------------------------------------------------------------------
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）静态数据字典视图。
包括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')
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
"OA".