如何在Oracle中使用DBMS_METADATA.GET_DDL获取对象的创建脚本

DBMS_METADATA.GET_DDL

Oracle数据库有DBMS_METADATA包，它提供了一种从数据库字典中检索元数据的方法。

Get DDL ( Create Script ) DBMS_METADATA.GET_DDL

DBMS_METADATA.GET_DDL 的语法如下。

DBMS_METADATA.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;

DBMS_METADATA – User Create Script

可以按如下方式获取任何用户（Schema）的 DDL（创建脚本）

select dbms_metadata.get_ddl('USER', du.username) AS DDL_SCRIPT
from dba_users du
where du.username = 'TYPE_USER_NAME'
union all
select dbms_metadata.get_granted_ddl('TABLESPACE_QUOTA', dtq.username) AS DDL_SCRIPT
from dba_ts_quotas dtq
where dtq.username = 'TYPE_USER_NAME'
and rownum = 1
union all
select dbms_metadata.get_granted_ddl('ROLE_GRANT', drp.grantee) AS DDL_SCRIPT
from dba_role_privs drp
where drp.grantee = 'TYPE_USER_NAME'
and rownum = 1
union all
select dbms_metadata.get_granted_ddl('SYSTEM_GRANT', dsp.grantee) AS DDL_SCRIPT
from dba_sys_privs dsp
where dsp.grantee = 'TYPE_USER_NAME'
and rownum = 1
union all
select dbms_metadata.get_granted_ddl('OBJECT_GRANT', dtp.grantee) AS DDL_SCRIPT
from dba_tab_privs dtp
where dtp.grantee = 'TYPE_USER_NAME'
and rownum = 1
union all
select dbms_metadata.get_granted_ddl('DEFAULT_ROLE', drp.grantee) AS DDL_SCRIPT
from dba_role_privs drp
where drp.grantee = 'TYPE_USER_NAME'
and drp.default_role = 'YES'
and rownum = 1;

GET_DDL

For example: You can get DDL of SCOTT user as follows.

SQL> set long 100000
SQL> set head off
SQL> set echo off
SQL> set pagesize 0
SQL> set verify off
SQL> set feedback off
SQL> select dbms_metadata.get_ddl('USER', du.username) AS DDL_SCRIPT
from dba_users du
where du.username = 'SCOTT'
union all
select dbms_metadata.get_granted_ddl('TABLESPACE_QUOTA', dtq.username) AS DDL_SCRIPT
from dba_ts_quotas dtq
where dtq.username = 'SCOTT'
and rownum = 1
union all
select dbms_metadata.get_granted_ddl('ROLE_GRANT', drp.grantee) AS DDL_SCRIPT
from dba_role_privs drp
where drp.grantee = 'SCOTT'
and rownum = 1
union all
select dbms_metadata.get_granted_ddl('SYSTEM_GRANT', dsp.grantee) AS DDL_SCRIPT
from dba_sys_privs dsp
where dsp.grantee = 'SCOTT'
and rownum = 1
union all
select dbms_metadata.get_granted_ddl('OBJECT_GRANT', dtp.grantee) AS DDL_SCRIPT
from dba_tab_privs dtp
where dtp.grantee = 'SCOTT'
and rownum = 1
union all
select dbms_metadata.get_granted_ddl('DEFAULT_ROLE', drp.grantee) AS DDL_SCRIPT
from dba_role_privs drp
where drp.grantee = 'SCOTT'
and drp.default_role = 'YES'
and rownum = 1;

CREATE USER "SCOTT" IDENTIFIED BY VALUES 'S:BE2891F45D57E8E7897914A39E21F4B3D7F9F23D72E8E0E65DF1F51C2262;435E3EB39C74D939' DEFAULT TABLESPACE "USERS" TEMPORARY TABLESPACE "TEMP"

   GRANT "CONNECT" TO "SCOTT"
   GRANT "RESOURCE" TO "SCOTT"
   GRANT "DBA" TO "SCOTT"

   GRANT CREATE TABLE TO "SCOTT"
   GRANT UNLIMITED TABLESPACE TO "SCOTT"

   GRANT SELECT ON "SYS"."DBA_USERS" TO "SCOTT"
   GRANT INSERT ON "SYS"."TEST402" TO "SCOTT"
   GRANT UPDATE ON "SYS"."TEST402" TO "SCOTT"
   GRANT SELECT ON "SYS"."TEST402" TO "SCOTT"
   GRANT DELETE ON "SYS"."TEST402" TO "SCOTT"

   ALTER USER "SCOTT" DEFAULT ROLE ALL;

SQL>

Get DDL of a Table ( Table Create Script )

可以按如下方式获取表的创建脚本

select dbms_metadata.get_ddl( 'TABLE', 'TABLE_NAME','SCHEMA_NAME' ) from dual;

For example; You can get SCOTT.EMP table create script as follows.

select dbms_metadata.get_ddl('TABLE', 'EMP','SCOTT') from dual;

SQL> set long 100000
SQL> set head off
SQL> set echo off
SQL> set pagesize 0
SQL> set verify off
SQL> set feedback off
SQL> select dbms_metadata.get_ddl('TABLE', 'EMP','SCOTT') from dual;

CREATE TABLE "SCOTT"."EMP"
   (    "EMPNO" NUMBER(4,0),
        "ENAME" VARCHAR2(10),
        "JOB" VARCHAR2(9),
        "MGR" NUMBER(4,0),
        "HIREDATE" DATE,
        "SAL" NUMBER(7,2),
        "COMM" NUMBER(7,2),
        "DEPTNO" NUMBER(2,0),
         CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO")
  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 FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS"  ENABLE,
         CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO")
          REFERENCES "SCOTT"."DEPT" ("DEPTNO") ENABLE
   ) SEGMENT CREATION IMMEDIATE
  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 FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS"
  PARALLEL

SQL>

还可以按如下方式获取所有表或任何用户模型下表的创建脚本。

SELECT DBMS_METADATA.get_ddl ('TABLE', table_name, owner)
FROM dba_tables
WHERE owner = UPPER('&1');

Index Create Script

You can get DDL ( Create Script ) of any index as follows.

select dbms_metadata.get_ddl( 'INDEX', 'INDEX_NAME','SCHEMA_NAME' ) from dual;

For example; You can get HR.EMP_DEPARTMENT_IX index create script as follows.

SQL> set long 100000
SQL> set head off
SQL> set echo off
SQL> set pagesize 0
SQL> set verify off
SQL> set feedback off
SQL> select dbms_metadata.get_ddl('INDEX','EMP_DEPARTMENT_IX','HR') from dual;

CREATE INDEX "HR"."EMP_DEPARTMENT_IX" ON "HR"."EMPLOYEES" ("DEPARTMENT_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 FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS";

SQL>

You can get all indexes get ddl ( create script ) as follows.

SELECT DBMS_METADATA.GET_DDL('INDEX', INDEX_NAME) FROM USER_INDEXES WHERE INDEX_TYPE ='NORMAL';

Tablespace Create Script

You can generate all tablespaces get ddl ( create script ) as follows.

set head off echo off
select 'select dbms_metadata.get_ddl(''TABLESPACE'','''
|| tablespace_name || ''') from dual;' from dba_tablespaces;

You can get the Users tablespace get ddl ( create script ) as follows.

SQL> select dbms_metadata.get_ddl('TABLESPACE','USERS') from dual;

CREATE TABLESPACE "USERS" DATAFILE
  '/u01/app/oracle/oradata/orcl/users01.dbf' SIZE 5242880
  AUTOEXTEND ON NEXT 1310720 MAXSIZE 32767M
  LOGGING ONLINE PERMANENT BLOCKSIZE 8192
  EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT
 NOCOMPRESS  SEGMENT SPACE MANAGEMENT AUTO
   ALTER DATABASE DATAFILE
  '/u01/app/oracle/oradata/orcl/users01.dbf' RESIZE 85196800;

DBMS_METADATA.GET_DDL VIEW, FUNCTION,PACKAGE

You can get all views get ddl ( create script ) as follows.

SELECT DBMS_METADATA.GET_DDL('VIEW',U.OBJECT_NAME, U.OWNER) FROM DBA_OBJECTS U WHERE U.OBJECT_TYPE = 'VIEW' AND OWNER='USER_NAME';

You can get all functions, procedures get ddl ( create script ) as follows.

SELECT DBMS_METADATA.GET_DDL('PROCEDURE',U.OBJECT_NAME, U.OWNER) FROM DBA_OBJECTS U WHERE U.OBJECT_TYPE = 'PROCEDURE' AND OWNER='USER_NAME';
SELECT DBMS_METADATA.GET_DDL('FUNCTION',U.OBJECT_NAME, U.OWNER) FROM DBA_OBJECTS U WHERE U.OBJECT_TYPE = 'FUNCTION'AND OWNER='USER_NAME';

You can get any package’s get ddl ( create script ) as follows.

SQL> SELECT DBMS_METADATA.GET_DDL('PACKAGE','OBJECT_NAME','SCHEMA_NAME') FROM DUAL;

You can get any package’s body get ddl ( create script ) as follows.

SQL> SELECT DBMS_METADATA.GET_DDL('PACKAGE_BODY','OBJECT_NAME','SCHEMA_NAME') FROM DUAL;

You can get any constraint’s get ddl ( create script ) as follows.

SQL> SELECT DBMS_METADATA.GET_DEPENDENT_DDL('REF_CONSTRAINT','OBJECT_NAME','SCHEMA_NAME') from dual;

You can get any user’s system grant ( create script ) as follows.

SQL> SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','SCHEMA_NAME') from dual;

You can get any user’s role grant ( create script ) as follows.

SQL> SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT','SCHEMA_NAME') from dual;

上述sql脚本，可根据实际需求进行调整，还有部分其他用法此处未完全罗列，使用者可利用搜索引擎自行检索。