利用dbms_metedata包的get_ddl过程创建数据库对象

SQL> conn scott/tiger

Connected.

SQL> set long 99999

SQL> set pages 200

--提取表的元数据

SQL> select dbms_metadata.get_ddl('TABLE','EMP') FROM DUAL;

DBMS_METADATA.GET_DDL('TABLE','EMP')

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

  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"

--提取索引元数据

SQL> select dbms_metadata.get_ddl('INDEX','PK_EMP') FROM DUAL;

DBMS_METADATA.GET_DDL('INDEX','PK_EMP')

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

  CREATE UNIQUE INDEX "SCOTT"."PK_EMP" ON "SCOTT"."EMP" ("EMPNO")

  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> conn system/oracle

Connected.

SQL> select dbms_metadata.get_ddl('SEQUENCE','SYSTEM_GRANT','SYS') FROM DUAL;

DBMS_METADATA.GET_DDL('SEQUENCE','SYSTEM_GRANT','SYS')

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

   CREATE SEQUENCE  "SYS"."SYSTEM_GRANT"  MINVALUE 1 MAXVALUE 999999999999999999

9999999999 INCREMENT BY 1 START WITH 1362 CACHE 20 ORDER  NOCYCLE

--提取表空间元数据

SQL> select dbms_metadata.get_ddl('TABLESPACE','UNDOTBS1') FROM DUAL;

DBMS_METADATA.GET_DDL('TABLESPACE','UNDOTBS1')

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

  CREATE UNDO TABLESPACE "UNDOTBS1" DATAFILE

  '/u01/app/oracle/oradata/zlm11g/undotbs01.dbf' SIZE 26214400

  AUTOEXTEND ON NEXT 5242880 MAXSIZE 32767M

  BLOCKSIZE 8192

  EXTENT MANAGEMENT LOCAL AUTOALLOCATE

   ALTER DATABASE DATAFILE

  '/u01/app/oracle/oradata/zlm11g/undotbs01.dbf' RESIZE 178257920

--生成scott用户用于创建表和索引语句的脚本

SQL> conn scott/tiger

Connected.

SQL> show user

USER is "SCOTT"

SQL> set pages 999

SQL> select

  2  'select dbms_metadata.get_ddl('||''''||'TABLE'||''''||','||''''||table_name||''''||') from dual;'

  3  ||chr(10)

  4  ||'select '||''''||'/'||''''|| ' from dual;' from user_tables;

'SELECTDBMS_METADATA.GET_DDL('||''''||'TABLE'||''''||','||''''||TABLE_NAME||''''||')FROMDUAL;'||CHR(10)

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

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

select '/' from dual;

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

select '/' from dual;

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

select '/' from dual;

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

select '/' from dual;

SQL> SELECT 'SELECT DBMS_METADATA.GET_DDL(''INDEX'','''||

  2  INDEX_NAME||''',''SCOTT'')'||'|'||'|'||''';'''||' FROM DUAL'||';' FROM USER_INDEXES WHERE

  3  TABLESPACE_NAME='USERS';

'SELECTDBMS_METADATA.GET_DDL(''INDEX'','''||INDEX_NAME||''',''SCOTT'')'||'|'||'|'||''';'''||'F

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

SELECT DBMS_METADATA.GET_DDL('INDEX','PK_EMP','SCOTT')||';' FROM DUAL;

SELECT DBMS_METADATA.GET_DDL('INDEX','PK_DEPT','SCOTT')||';' FROM DUAL;

SQL> SELECT 'SELECT DBMS_METADATA.GET_DDL(''TABLE'','''||

  2  TABLE_NAME||''',''SCOTT'')'||'|'||'|'||''';'''||' FROM DUAL'||';' FROM USER_INDEXES WHERE

  3  TABLESPACE_NAME='USERS';

'SELECTDBMS_METADATA.GET_DDL(''TABLE'','''||TABLE_NAME||''',''SCOTT'')'||'|'||'|'||''';'''||'F

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

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

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

--根据刚才语句的显示结果，得到创建数据库中某schema对象（表和索引）的脚本，用“/”分隔后可以批量运行

set heading off

set echo off

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

select '/' from dual;

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

select '/' from dual;

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

select '/' from dual;

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

select '/' from dual;

SELECT DBMS_METADATA.GET_DDL('INDEX','PK_EMP','SCOTT')||';' FROM DUAL;

SELECT DBMS_METADATA.GET_DDL('INDEX','PK_DEPT','SCOTT')||';' FROM DUAL;

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

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

SQL> select dbms_metadata.get_ddl('TABLE','DEPT') from dual;

select '/' from dual;

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

select '/' from dual;

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

  CREATE TABLE "SCOTT"."DEPT"

   (    "DEPTNO" NUMBER(2,0),

        "DNAME" VARCHAR2(14),

        "LOC" VARCHAR2(13),

         CONSTRAINT "PK_DEPT" PRIMARY KEY ("DEPTNO")

  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

   ) 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"

SQL> 

/

SQL> select '/' from dual;

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

select '/' 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"

SQL> 

/

SQL> SELECT DBMS_METADATA.GET_DDL('INDEX','PK_EMP','SCOTT')||';' FROM DUAL;

  CREATE TABLE "SCOTT"."BONUS"

   (    "ENAME" VARCHAR2(10),

        "JOB" VARCHAR2(9),

        "SAL" NUMBER,

        "COMM" NUMBER

   ) SEGMENT CREATION DEFERRED

  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255

 NOCOMPRESS LOGGING

  TABLESPACE "USERS"

SQL> 

/

SQL> SELECT DBMS_METADATA.GET_DDL('INDEX','PK_DEPT','SCOTT')||';' FROM DUAL;

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

  CREATE TABLE "SCOTT"."SALGRADE"

   (    "GRADE" NUMBER,

        "LOSAL" NUMBER,

        "HISAL" NUMBER

   ) 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"

SQL> 

/

SQL> 

  CREATE UNIQUE INDEX "SCOTT"."PK_EMP" ON "SCOTT"."EMP" ("EMPNO")

  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> 

  CREATE UNIQUE INDEX "SCOTT"."PK_DEPT" ON "SCOTT"."DEPT" ("DEPTNO")

  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> 

  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" ;

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

  CREATE TABLE "SCOTT"."DEPT"

   (    "DEPTNO" NUMBER(2,0),

        "DNAME" VARCHAR2(14),

        "LOC" VARCHAR2(13),

         CONSTRAINT "PK_DEPT" PRIMARY KEY ("DEPTNO")

  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

   ) 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" ;

SQL> 

最后这些由DBMS_METADATA.GET_DDL获得的语句，就可以拿来创建schema的表和索引