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的表和索引