使用DBMS_METADATA.GET_DDL獲取物件的DDL指令碼

--GET_DDL:       Return the metadata for a single object as DDL.

-- This interface is meant for casual browsing (e.g., from SQLPlus)

-- vs. the programmatic OPEN / FETCH / CLOSE interfaces above.

-- 引數  :

-- object_type - The type of object to be retrieved.

-- name - Name of the object.  表名或索引名, procedure名 等等   

-- schema - Schema containing the object. Defaults to

-- the caller's schema.

-- version - The version of the objects' metadata.

-- model - The object model for the metadata.

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

-- RETURNS: Metadata for the object transformed to DDL as a CLOB.

 

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;

 

1.得到一個表或索引的ddl語句

SELECT DBMS_METADATA.GET_DDL('TABLE','DEPT','SCOTT') FROM DUAL;

select dbms_metadata.get_ddl('INDEX','PK_DEPT','SCOTT') from dual;

2.得到一個使用者下的所有表，索引，儲存過程的ddl

SELECT DBMS_METADATA.GET_DDL(U.OBJECT_TYPE, u.object_name)
FROM USER_OBJECTS u
where U.OBJECT_TYPE IN ('TABLE','INDEX','PROCEDURE');

3.得到所有表空間的ddl語句

SELECT DBMS_METADATA.GET_DDL('TABLESPACE', TS.tablespace_name)
FROM DBA_TABLESPACES TS;

4.得到所有建立使用者的ddl

SELECT DBMS_METADATA.GET_DDL('USER',U.username)
FROM DBA_USERS U;

================================================================================

9i  中可以利用DBMS_METADATA.GET_DDL包得到資料庫的物件的ddl指令碼。如下（SQLPLUS中執行）：

a. 獲取單個的建表、檢視和建索引的語法

set pagesize 0
set long 90000
set feedback off
set echo off
spool DEPT.sql
select dbms_metadata.get_ddl('TABLE','TAB_NAME','SCOTT') from dual;
select dbms_metadata.get_ddl('VIEW','VIEW_NAME','SCOTT') from dual;
select dbms_metadata.get_ddl('INDEX','IDX_NAME','SCOTT') from dual;
spool off;
  
b.獲取一個SCHEMA下的所有建表、檢視和建索引的語法，以scott為例：
 
set pagesize 0
set long 90000
set feedback off
set echo off
spool schema.sql
connect scott/tiger;
SELECT DBMS_METADATA.GET_DDL('TABLE',u.table_name) FROM USER_TABLES u;
 SELECT DBMS_METADATA.GET_DDL('VIEW',u.VIEW_name) FROM USER_VIEWS u;
SELECT DBMS_METADATA.GET_DDL('INDEX',u.index_name) FROM USER_INDEXES u;
spool off;

c.    獲取某個SCHEMA的建全部儲存過程的語法
 
set pagesize 0
set long 90000
set feedback off
set echo off
spool procedures.sql 
select   DBMS_METADATA.GET_DDL('PROCEDURE',u.object_name) from     user_objects u where     object_type = 'PROCEDURE';
spool off;

d.    獲取某個SCHEMA的建全部函式的語法
 
set pagesize 0
set long 90000
set feedback off
set echo off
spool function.sql 
select   DBMS_METADATA.GET_DDL('FUNCTION',u.object_name) from     user_objects u where     object_type = 'FUNCTION';
spool off;  

------------------------------------------------

 

例子 : 

 

SQL> set pagesize 0
SQL> set long 90000
SQL> set feedback off
SQL> set echo off
SQL> spool   schema.sql
SQL>
SQL> select dbms_metadata.get_ddl('TABLE','EDIBTO850_PO1PID','DFMS') from dual;  

SQL>  spool off;
  CREATE TABLE "DFMS"."EDIBTO850_PO1PID"
   (    "CONTROLNUMBER" VARCHAR2(40) NOT NULL ENABLE,
        "BEG03" VARCHAR2(20) NOT NULL ENABLE,
        "PO101" VARCHAR2(20) NOT NULL ENABLE,
        "SEQNO" NUMBER(*,0) NOT NULL ENABLE,
        "PO109" VARCHAR2(20) DEFAULT (''),
        "PID01" CHAR(1) DEFAULT (''),
        "PID02" VARCHAR2(3) DEFAULT (''),
        "PID05" VARCHAR2(200) DEFAULT ('') NOT NULL ENABLE,
        "PID08" CHAR(1) DEFAULT (''),
        "LASTEDITDT" DATE DEFAULT (sysdate),
        "CREATE_TIME" DATE DEFAULT sysdate,
        "TIEGROUP" VARCHAR2(35),
         CONSTRAINT "EDIBTO850_PO1PID_PK" PRIMARY KEY ("CONTROLNUMBER", "BEG03", "PO101
", "SEQNO", "PID05")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 104857600 NEXT 104857600 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DE
FAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "SN_IDX"  ENABLE
   ) PCTFREE 10 PCTUSED 0 INITRANS 1 MAXTRANS 255 NOCOMPRESS  LOGGING
  STORAGE(
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "PART_D_TS2"
  PARTITION BY RANGE ("CREATE_TIME")
 (PARTITION "P200907"  VALUES LESS THAN (TO_DATE(' 2009-07-01 00:00:00', 'SYYYY-
MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 0 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 52428800 NEXT 52428800 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DE
FAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "PART_D_TS2" ,
 PARTITION "P201001"  VALUES LESS THAN (TO_DATE(' 2010-01-01 00:00:00', 'SYYYY-M
M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 0 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 52428800 NEXT 52428800 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DE
FAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "PART_D_TS2" ,
 PARTITION "P201007"  VALUES LESS THAN (TO_DATE(' 2010-07-01 00:00:00', 'SYYYY-M
M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 0 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 52428800 NEXT 52428800 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DE
FAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "PART_D_TS2" ,
 PARTITION "P201101"  VALUES LESS THAN (TO_DATE(' 2011-01-01 00:00:00', 'SYYYY-M
M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 0 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 52428800 NEXT 52428800 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DE
FAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "PART_D_TS2" ,
 PARTITION "P201107"  VALUES LESS THAN (TO_DATE(' 2011-07-01 00:00:00', 'SYYYY-M
M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 0 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 52428800 NEXT 52428800 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DE
FAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "PART_D_TS2" ,
 PARTITION "P201201"  VALUES LESS THAN (TO_DATE(' 2012-01-01 00:00:00', 'SYYYY-M
M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 0 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 52428800 NEXT 52428800 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DE
FAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "PART_D_TS2" ,
 PARTITION "P201207"  VALUES LESS THAN (TO_DATE(' 2012-07-01 00:00:00', 'SYYYY-M
M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 0 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 52428800 NEXT 52428800 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DE
FAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "PART_D_TS2" ,
 PARTITION "P201301"  VALUES LESS THAN (TO_DATE(' 2013-01-01 00:00:00', 'SYYYY-M
M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 0 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 52428800 NEXT 52428800 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DE
FAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "PART_D_TS2" ,
 PARTITION "P201307"  VALUES LESS THAN (TO_DATE(' 2013-07-01 00:00:00', 'SYYYY-M
M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 0 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 52428800 NEXT 52428800 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DE
FAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "PART_D_TS2" ,
 PARTITION "P201401"  VALUES LESS THAN (TO_DATE(' 2014-01-01 00:00:00', 'SYYYY-M
M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 0 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 52428800 NEXT 52428800 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DE
FAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "PART_D_TS2" ,
 PARTITION "P201407"  VALUES LESS THAN (TO_DATE(' 2014-07-01 00:00:00', 'SYYYY-M
M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 0 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 52428800 NEXT 52428800 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DE
FAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "PART_D_TS2" ,
 PARTITION "P201501"  VALUES LESS THAN (TO_DATE(' 2015-01-01 00:00:00', 'SYYYY-M
M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 0 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 52428800 NEXT 52428800 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DE
FAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "PART_D_TS2" ,
 PARTITION "PMAX"  VALUES LESS THAN (MAXVALUE) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 0 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 52428800 NEXT 52428800 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DE
FAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "PART_D_TS2" )  ENABLE ROW MOVEMENT