當我們想要檢視某個表或者是表空間的DDL的時候，可以利用dbms_metadata.get_ddl這個包來檢視。

dbms_metadata包中的get_ddl函式詳細引數

GET_DDL函式返回建立物件的原資料的DDL語句，詳細引數如下

-- object_type ---需要返回原資料的DDL語句的物件型別

-- name --- 物件名稱

-- schema ---物件所在的Schema，預設為當前使用者所在所Schema

-- version ---物件原資料的版本

-- model ---原資料的型別預設為ORACLE

-- transform. - XSL-T transform. to be applied.

-- RETURNS: 物件的原資料預設以CLOB型別返回

dbms_metadata包中的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;

SET SERVEROUTPUT ON
SET LINESIZE 1000
SET FEEDBACK OFF
SET LONG 999999
檢視錶的DDL 語句。
yang@rac1>select dbms_metadata.get_ddl('TABLE','YANGTAB') FROM DUAL;
DBMS_METADATA.GET_DDL('TABLE','YANGTAB')
---------------------------------------------------------------------------
CREATE TABLE "YANG"."YANGTAB"
   (    "ID" NUMBER,
        "NAME" VARCHAR2(15),
         PRIMARY KEY ("ID")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS" ENABLE
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS"
檢視錶空間的DDL 語句。
sys@rac1>select dbms_metadata.get_ddl('TABLESPACE', 'USERS') FROM DUAL;
DBMS_METADATA.GET_DDL('TABLESPACE','USERS')
---------------------------------------------------------------------------
CREATE TABLESPACE "USERS" DATAFILE
'D:\ORACLE\ORADATA\ORACL\USERS01.DBF' SIZE 5242880
AUTOEXTEND ON NEXT 1310720 MAXSIZE 32767M,
'D:\ORACLE\ORADATA\ORACL\USERS02.DBF' SIZE 943718400
LOGGING ONLINE PERMANENT BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO
   ALTER DATABASE DATAFILE
'D:\ORACLE\ORADATA\ORACL\USERS01.DBF' RESIZE 347340800
sys@rac1>select dbms_metadata.get_ddl('TABLESPACE', 'EXAMPLE') FROM DUAL;
DBMS_METADATA.GET_DDL('TABLESPACE','EXAMPLE')
---------------------------------------------------------------------------
CREATE TABLESPACE "EXAMPLE" DATAFILE
'D:\ORACLE\ORADATA\ORACL\EXAMPLE01.DBF' SIZE 104857600
AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M
NOLOGGING ONLINE PERMANENT BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO
   ALTER DATABASE DATAFILE
'D:\ORACLE\ORADATA\ORACL\EXAMPLE01.DBF' RESIZE 314572800
檢視使用者的定義語句。
sys@rac1>select dbms_metadata.get_ddl('USER', 'YANG') FROM DUAL;
DBMS_METADATA.GET_DDL('USER','YANG')
---------------------------------------------------------------------------
   CREATE USER "YANG" IDENTIFIED BY VALUES 'S:269264CD30B5AC166D2C9882AB88F
DA20'
      DEFAULT TABLESPACE "USERS"
      TEMPORARY TABLESPACE "TEMP"
檢視索引的定義語句：
yang@rac1>select index_name ,table_name from user_indexes;

INDEX_NAME                     TABLE_NAME
------------------------------ ------------------------------
SYS_C0010473                   YANG_B
SYS_C0010476                   YANG_A
SYS_C0010278                   YANGTAB2
SYS_C0010277                   YANGTAB
SYS_C0010286                   MV_YANGTAB2
SYS_C0010285                   MV_YANGTAB
ID_SEQNO                       DA_TEST
yang@rac1>select dbms_metadata.get_ddl('INDEX','ID_SEQNO') FROM DUAL;
DBMS_METADATA.GET_DDL('INDEX','ID_SEQNO')
--------------------------------------------------------------------------------
CREATE INDEX "YANG"."ID_SEQNO" ON "YANG"."DA_TEST" ("SEQNO")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS"
yang@rac1>select dbms_metadata.get_ddl('INDEX','SYS_C0010285') FROM DUAL;
DBMS_METADATA.GET_DDL('INDEX','SYS_C0010285')
--------------------------------------------------------------------------------
CREATE UNIQUE INDEX "YANG"."SYS_C0010285" ON "YANG"."MV_YANGTAB" ("ID")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS"

使用dbms_metadata.get_ddl檢視物件的定義語句。

相關文章