Oracle中如何導出存儲過程谷市、函數(shù)、包和觸發(fā)器的定義語句击孩？如何導出表的結構迫悠？如何導出索引的創(chuàng)建語句？

Oracle中如何導出存儲過程巩梢、函數(shù)创泄、包和觸發(fā)器的定義語句？如何導出表的結構括蝠？如何導出索引的創(chuàng)建語句鞠抑？

QQ群里有人問：如何導出一個用戶下的存儲過程？

? 麥苗答：方法有多種忌警，可以使用DBMS_METADATA.GET_DDL包搁拙。

使用PL/SQL DEVELOPER工具

-- 下面的SQL語句，如果報錯：ORA-22835: 緩沖區(qū)對于 CLOB 到 CHAR 轉換或 BLOB 到 RAW 轉換而言太小 (實際: 4994, 最大: 4000)，那么去掉TO_CAHR

SELECT TO_CHAR(DBMS_METADATA.GET_DDL('PROCEDURE', U.OBJECT_NAME)) ||CHR(10)||'/'

? FROM USER_OBJECTS U

?WHERE OBJECT_TYPE = 'PROCEDURE'

然后將結果拷貝到Excel中箕速，

打開Excel酪碘，復制內(nèi)容到plsql developer里邊，注意粘貼的時候使用右鍵的“Past from host Language”盐茎，否則粘貼后的代碼含有雙引號：

? 運行這些腳本腳本即可：

使用SQL*Plus

使用如下的腳本即可導出某個用戶下的存儲過程代碼到/tmp/a.sql文件中：

SET PAGESIZE 0

SET TRIMSPOOL ON

SET LINESIZE 10000

SET LONG 90000

SET FEEDBACK OFF

SET FEED OFF;

SET ECHO OFF

spool /tmp/a.sql

SELECT DBMS_METADATA.GET_DDL('PROCEDURE', U.OBJECT_NAME)||CHR(10)||'/'

? FROM USER_OBJECTS U

?WHERE OBJECT_TYPE = 'PROCEDURE';?

spool OFF

打開文件后婆跑，簡單處理一下即可。

總體來說有兩種方式來獲取庭呜，第一滑进，利用系統(tǒng)包DBMS_METADATA包中的GET_DDL函數(shù)來獲取，第二募谎，利用exp或expdp來獲取扶关。

下面來看第一種方式，如何利用系統(tǒng)包DBMS_METADATA包中的GET_DDL函數(shù)來獲取對象的定義語句数冬。下面是該函數(shù)的入?yún)⒑统鰠ⅲ?/p>

SQL> DESC DBMS_METADATA.GET_DDL

PARAMETER?? TYPE???? MODE DEFAULT?

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

(RESULT)??? CLOB??????????????????

OBJECT_TYPE VARCHAR2 IN???????????

NAME??????? VARCHAR2 IN???????????

SCHEMA????? VARCHAR2 IN?? Y???????

VERSION???? VARCHAR2 IN?? Y???????

MODEL?????? VARCHAR2 IN?? Y???????

TRANSFORM?? VARCHAR2 IN?? Y???????

其詳細參數(shù)如下：

l?OBJECT_TYPE?需要返回原數(shù)據(jù)的DDL語句的對象類型

l?NAME?對象名稱

l?SCHEMA?對象所在的SCHEMA节槐，默認為當前用戶所在所SCHEMA

l?VERSION?對象原數(shù)據(jù)的版本

l?MODEL?原數(shù)據(jù)的類型默認為ORACLE

l?TRANSFORM?默認值為DDL

l?RETURNS?對象的原數(shù)據(jù)默認以CLOB類型返回

一般情況下，只需要給出OBJECT_TYPE拐纱、NAME和SCHEMA3個參數(shù)即可铜异。

n?查看創(chuàng)建表SQL語句：

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

SELECT DBMS_METADATA.GET_DDL('TABLE',U.TABLE_NAME) FROM USER_TABLES U;

n?查看創(chuàng)建索引的SQL語句：

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

SELECT DBMS_METADATA.GET_DDL('INDEX',U.INDEX_NAME) FROM USER_INDEXES U;

n?查看創(chuàng)建主鍵的SQL語句：

SELECT DBMS_METADATA.GET_DDL('CONSTRAINT','EMP_PK') FROM DUAL;

n?查看創(chuàng)建外鍵的SQL語句：

SELECT DBMS_METADATA.GET_DDL('REF_CONSTRAINT','EMP_FK_DEPT') FROM DUAL;

n?查看創(chuàng)建視圖（VIEW）的SQL語句：

SELECT DBMS_METADATA.GET_DDL('VIEW', 'MY_TABLES','SCOTT') FROM DUAL;

SELECT DBMS_METADATA.GET_DDL('VIEW', U.OBJECT_NAME)

? FROM USER_OBJECTS U

?WHERE OBJECT_TYPE = 'VIEW';

SELECT TEXT FROM USER_VIEWS WHERE VIEW_NAME=UPPER('&VIEW_NAME');

n?查看創(chuàng)建存儲過程（PROCEDURE）的SQL語句：

SELECT DBMS_METADATA.GET_DDL('PROCEDURE', U.OBJECT_NAME)

? FROM USER_OBJECTS U

?WHERE OBJECT_TYPE = 'PROCEDURE';

n?查看創(chuàng)建觸發(fā)器（TRIGGER）的SQL語句：

SELECT DBMS_METADATA.GET_DDL('TRIGGER', U.OBJECT_NAME)

? FROM USER_OBJECTS U

?WHERE OBJECT_TYPE = 'TRIGGER';

n?查看創(chuàng)建函數(shù)（FUNCTION）的SQL語句：

SELECT DBMS_METADATA.GET_DDL('FUNCTION', U.OBJECT_NAME)

? FROM USER_OBJECTS U

?WHERE OBJECT_TYPE = 'FUNCTION';

n?查看創(chuàng)建包（PACKAGE）的SQL語句：

SELECT DBMS_METADATA.GET_DDL('PACKAGE', U.OBJECT_NAME)

? FROM USER_OBJECTS U

?WHERE OBJECT_TYPE = 'PACKAGE';

n?查看創(chuàng)建序列（SEQUENCE）的SQL語句：

SELECT DBMS_METADATA.GET_DDL('SEQUENCE', U.OBJECT_NAME)

? FROM USER_OBJECTS U

?WHERE OBJECT_TYPE = 'SEQUENCE';

n?查看創(chuàng)建同義詞（SYNONYM）的SQL語句：

SELECT DBMS_METADATA.GET_DDL('SYNONYM', U.OBJECT_NAME)

? FROM USER_OBJECTS U

?WHERE OBJECT_TYPE = 'SYNONYM';

n?查看創(chuàng)建表空間（TABLESPACE）的SQL語句：

SELECT DBMS_METADATA.GET_DDL('TABLESPACE', U.TABLESPACE_NAME)

? FROM USER_TABLESPACES U;

n?查看創(chuàng)建角色（ROLE）的SQL語句：

SELECT DBMS_METADATA.GET_DDL('ROLE', U.ROLE) FROM DBA_ROLES U;

n?查看創(chuàng)建用戶（USER）的SQL語句：

SELECT DBMS_METADATA.GET_DDL('USER','SYS') FROM DUAL;

n?得到某個SCHEDULER JOB的創(chuàng)建語句：

SELECT DBMS_METADATA.GET_DDL('PROCOBJ', D.JOB_NAME, D.OWNER)

? FROM DBA_SCHEDULER_JOBS D

?WHERE D.JOB_TYPE = 'STORED_PROCEDURE'

?? AND D.STATE = 'SCHEDULED'

?? AND D.SCHEDULE_NAME IS NULL;

n?得到一個用戶下的所有表、索引秸架、存儲過程揍庄、函數(shù)的DDL語句：

SELECT DBMS_METADATA.GET_DDL(U.OBJECT_TYPE, U.OBJECT_NAME)

FROM?? USER_OBJECTS U

WHERE? U.OBJECT_TYPE IN ('TABLE', 'INDEX', 'PROCEDURE', 'FUNCTION');

如果想去掉表的存儲參數(shù)（例如，INITIAL东抹、NEXT蚂子、FREELISTS等參數(shù)），那么可以使用DBMS_METADATA包中的函數(shù)SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'STORAGE',FALSE)來完成缭黔，代碼如下所示：

SYS@lhrdb> SELECT DBMS_METADATA.GET_DDL('TABLE','DEPT','SCOTT') FROM DUAL;

DBMS_METADATA.GET_DDL('TABLE','DEPT','SCOTT')

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

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

SYS@lhrdb>?EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'STORAGE',FALSE);

PL/SQL procedure successfully completed.

SYS@lhrdb> SELECT DBMS_METADATA.GET_DDL('TABLE','DEPT','SCOTT') FROM DUAL;

DBMS_METADATA.GET_DDL('TABLE','DEPT','SCOTT')

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

? 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

? TABLESPACE "USERS"? ENABLE

?? ) SEGMENT CREATION IMMEDIATE

? PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255

?NOCOMPRESS LOGGING

? TABLESPACE "USERS"

使用DBMS_METADATA.GET_DDL需要注意以下問題：

（1）DBMS_METADATA.GET_DDL()包內(nèi)的參數(shù)都要大寫食茎，否則會報ORA-31600: invalid input value table for parameter OBJECT_TYPE in function GET_DDL的錯誤。

（2）是否查的當前用戶的DDL語句馏谨，若不是則需要加上對象的屬主信息即SCHEMA參數(shù)别渔。

（3）若在SQL*Plus中顯示不全，則需要set long 9999惧互。

（4）對于DBMS_METADATA.GET_DDL包哎媚，可以在PLSQL Developer工具中運行，也可以在SQL*Plus中運行壹哺。

如果要導出SCOTT用戶下的所有定義抄伍，那么在SQL*Plus中代碼如下所示：

SET PAGESIZE 0

SET TRIMSPOOL ON

SET LINESIZE 10000

SET LONG 90000

SET FEEDBACK OFF

SET FEED OFF;

SET ECHO OFF

SPOOL /tmp/schema_scott.sql

SELECT CASE

???????? WHEN U.OBJECT_TYPE IN

('PROCEDURE', 'FUNCTION'?艘刚，?'PACKAGE', 'TRIGGER') THEN

????????? DBMS_METADATA.GET_DDL(U.OBJECT_TYPE, U.OBJECT_NAME, U.OWNER) ||

????????? CHR(10) || '/'

???????? ELSE

????????? DBMS_METADATA.GET_DDL(U.OBJECT_TYPE, U.OBJECT_NAME, U.OWNER)||

????????? CHR(10) || ';'

?????? END AS SCOTT_DDL

? FROM DBA_OBJECTS U

?WHERE U.OBJECT_TYPE IN

('TABLE', 'INDEX', 'PROCEDURE', 'FUNCTION'?管宵，?'PACKAGE', 'TRIGGER')

AND U.OWNER='SCOTT';

SPOOL OFF;

則可以導出SCOTT用戶下所有的DDL語句到/tmp/schema_scott.sql文件中。

如果在PLSQL Developer工具中運行，那么可以單獨運行如下的SQL語句：

SELECT CASE

???????? WHEN U.OBJECT_TYPE IN

('PROCEDURE', 'FUNCTION'?箩朴，?'PACKAGE', 'TRIGGER') THEN

????????? DBMS_METADATA.GET_DDL(U.OBJECT_TYPE, U.OBJECT_NAME, U.OWNER) ||

????????? CHR(10) || '/'

???????? ELSE

????????? DBMS_METADATA.GET_DDL(U.OBJECT_TYPE, U.OBJECT_NAME, U.OWNER)||

????????? CHR(10) || ';'

?????? END AS SCOTT_DDL

? FROM DBA_OBJECTS U

?WHERE U.OBJECT_TYPE IN

('TABLE', 'INDEX', 'PROCEDURE', 'FUNCTION'?岗喉，?'PACKAGE', 'TRIGGER')

AND U.OWNER='SCOTT';

然后選擇整列，右鍵選擇“Copy to Excel”炸庞，就可以將數(shù)據(jù)導出到Excel文件中钱床，接著，將Excel中的數(shù)據(jù)復制到PLSQL Developer工具的“SQL Window”中皆可埠居。需要注意的是查牌，最后復制到“SQL Window”中的時候，需要選擇右鍵的“Past from host Language”滥壕，否則粘貼的代碼含有雙引號纸颜，需要做特殊處理，比較麻煩绎橘。

?可以使用如下的SQL腳本生成某個用戶下的所有對象的DDL語句：

sqlplus<

set long 100000

set head off

set echo off

set pagesize 0

set verify off

set feedback off

spool schema.out

select dbms_metadata.get_ddl(object_type, object_name, owner)

from

(

??? --Convert DBA_OBJECTS.OBJECT_TYPE to DBMS_METADATA object type:

??? select

??????? owner,

??????? --Java object names may need to be converted with DBMS_JAVA.LONGNAME.

??????? --That code is not included since many database don't have Java installed.

??????? object_name,

??????? decode(object_type,

??????????? 'DATABASE LINK',????? 'DB_LINK',

??????????? 'JOB',??????????????? 'PROCOBJ',

??????????? 'RULE SET',?????????? 'PROCOBJ',

??????????? 'RULE',?????????????? 'PROCOBJ',

??????????? 'EVALUATION CONTEXT', 'PROCOBJ',

??????????? 'PACKAGE',??????????? 'PACKAGE_SPEC',

??????????? 'PACKAGE BODY',?????? 'PACKAGE_BODY',

??????????? 'TYPE',?????????????? 'TYPE_SPEC',

??????????? 'TYPE BODY',????????? 'TYPE_BODY',

??????????? 'MATERIALIZED VIEW',? 'MATERIALIZED_VIEW',

??????????? 'QUEUE',????????????? 'AQ_QUEUE',

??????????? 'JAVA CLASS',???????? 'JAVA_CLASS',

??????????? 'JAVA TYPE',????????? 'JAVA_TYPE',

??????????? 'JAVA SOURCE',????? ??'JAVA_SOURCE',

??????????? 'JAVA RESOURCE',????? 'JAVA_RESOURCE',

??????????? object_type

??????? ) object_type

??? from dba_objects

??? where owner in ('LHR')

??????? --These objects are included with other object types.

??????? and object_type not in ('INDEX PARTITION','INDEX SUBPARTITION',

?????????? 'LOB','LOB PARTITION','LOB SUBPARTITION','TABLE PARTITION','TABLE SUBPARTITION','PROGRAM')

??????? --Ignore system-generated types that support collection processing.

??????? and not (object_type = 'TYPE' and object_name like 'SYS_PLSQL_%')

??????? --Exclude nested tables, their DDL is part of their parent table.

??????? and (owner, object_name) not in (select owner, table_name from dba_nested_tables)

??????? --Exlclude overflow segments, their DDL is part of their parent table.

??????? and (owner, object_name) not in (select owner, table_name from dba_tables where iot_type = 'IOT_OVERFLOW')

)

order by owner, object_type, object_name;

spool off

quit

EOF

cat schema.out|sed 's/OWNER1/MYOWNER/g'>schema.out.change.sql

下面介紹第二種導出元數(shù)據(jù)的方法胁孙，就是采用exp或expdp命令。數(shù)據(jù)泵工具（impdp）提供了SQLFILE的命令行選項称鳞，只獲取DDL語句涮较，并未真正地執(zhí)行數(shù)據(jù)導入。另外冈止，若單純?yōu)榱藢С鯠DL語句則可以在使用expdp導出的時候使用CONTENT=METADATA_ONLY和EXCLUDE=STATISTICS選項狂票，這樣導出的DMP文件比較小。如下所示：

expdp \'/ AS SYSDBA\' DIRECTORY=DATA_PUMP_DIR DUMPFILE=lhrsql20161215.dmp LOGFILE=lhrsql20161215.log CONTENT=METADATA_ONLY SCHEMAS=SCOTT? EXCLUDE=STATISTICS

impdp? \'/ AS SYSDBA\'? DIRECTORY=DATA_PUMP_DIR DUMPFILE=lhrsql20161215.dmp? LOGFILE=imp_exptest.log SQLFILE=expddl_lhr.sql

查看expddl_lhr.sql文件即可獲取DDL語句熙暴。整個示例如下所示：

[ZFZHLHRDB1:oracle]:/oracle>expdp \'/ AS SYSDBA\' directory=DATA_PUMP_DIR schemas=SCOTT? dumpfile=exptest_sql.dmp logfile=exp_exptest.dmp

Export: Release 11.2.0.4.0 - Production on Wed Aug 3 15:14:55 2016

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Data Mining and Real Application Testing options

Starting "SYS"."SYS_EXPORT_SCHEMA_01":? "/******** AS SYSDBA" directory=DATA_PUMP_DIR schemas=SCOTT dumpfile=exptest_sql.dmp logfile=exp_exptest.dmp

Estimate in progress using BLOCKS method...

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 256 KB

Processing object type SCHEMA_EXPORT/USER

Processing object type SCHEMA_EXPORT/SYSTEM_GRANT

Processing object type SCHEMA_EXPORT/ROLE_GRANT

Processing object type SCHEMA_EXPORT/DEFAULT_ROLE

Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

Processing object type SCHEMA_EXPORT/TABLE/TABLE

Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX

Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT

Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

. . exported "SCOTT"."DEPT"????????????????????????????? 5.929 KB?????? 4 rows

. . exported "SCOTT"."EMP"?????????????????????????????? 8.562 KB????? 14 rows

. . exported "SCOTT"."SALGRADE"????????????????????????? 5.859 KB?????? 5 rows

. . exported "SCOTT"."TEST"????????????????????????????? 5.007 KB?????? 1 rows

. . exported "SCOTT"."BONUS"???????????????????????????????? 0 KB?????? 0 rows

Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded

******************************************************************************

Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is:

? /oracle/app/oracle/admin/lhrdb/dpdump/exptest_sql.dmp

Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at Wed Aug 3 15:15:16 2016 elapsed 0 00:00:20

[ZFZHLHRDB1:oracle]:/oracle>impdp? \'/ AS SYSDBA\'? directory=DATA_PUMP_DIR dumpfile=exptest_sql.dmp? logfile=imp_exptest.log sqlfile=exptest.sql

Import: Release 11.2.0.4.0 - Production on Wed Aug 3 15:16:06 2016

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Data Mining and Real Application Testing options

Master table "SYS"."SYS_SQL_FILE_FULL_01" successfully loaded/unloaded

Starting "SYS"."SYS_SQL_FILE_FULL_01":? "/******** AS SYSDBA" directory=DATA_PUMP_DIR dumpfile=exptest_sql.dmp logfile=imp_exptest.log sqlfile=exptest.sql

Processing object type SCHEMA_EXPORT/USER

Processing object type SCHEMA_EXPORT/SYSTEM_GRANT

Processing object type SCHEMA_EXPORT/ROLE_GRANT

Processing object type SCHEMA_EXPORT/DEFAULT_ROLE

Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

Processing object type SCHEMA_EXPORT/TABLE/TABLE

Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX

Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT

Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Job "SYS"."SYS_SQL_FILE_FULL_01" successfully completed at Wed Aug 3 15:16:09 2016 elapsed 0 00:00:02

[ZFZHLHRDB1:oracle]:/oracle>cd /oracle/app/oracle/admin/lhrdb/dpdump/

[ZFZHLHRDB1:oracle]:/oracle/app/oracle/admin/lhrdb/dpdump>more exptest.sql

-- CONNECT SYS

ALTER SESSION SET EVENTS '10150 TRACE NAME CONTEXT FOREVER, LEVEL 1';

ALTER SESSION SET EVENTS '10904 TRACE NAME CONTEXT FOREVER, LEVEL 1';

ALTER SESSION SET EVENTS '25475 TRACE NAME CONTEXT FOREVER, LEVEL 1';

ALTER SESSION SET EVENTS '10407 TRACE NAME CONTEXT FOREVER, LEVEL 1';

ALTER SESSION SET EVENTS '10851 TRACE NAME CONTEXT FOREVER, LEVEL 1';

ALTER SESSION SET EVENTS '22830 TRACE NAME CONTEXT FOREVER, LEVEL 192 ';

-- new object type path: SCHEMA_EXPORT/USER

-- CONNECT SYSTEM

?CREATE USER "SCOTT" IDENTIFIED BY VALUES 'S:268AB71B15071D81F19C6FC5041FA8F8E49397470FFE05458B8C90D9E7F8;F894844C34402B67'

????? DEFAULT TABLESPACE "USERS"

????? TEMPORARY TABLESPACE "TEMP"

????? PASSWORD EXPIRE

????? ACCOUNT LOCK;

-- new object type path: SCHEMA_EXPORT/SYSTEM_GRANT

GRANT UNLIMITED TABLESPACE TO "SCOTT";

-- new object type path: SCHEMA_EXPORT/ROLE_GRANT

?GRANT "CONNECT" TO "SCOTT";

?GRANT "RESOURCE" TO "SCOTT";

-- new object type path: SCHEMA_EXPORT/DEFAULT_ROLE

?ALTER USER "SCOTT" DEFAULT ROLE ALL;

-- new object type path: SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

-- CONNECT SCOTT

BEGIN

sys.dbms_logrep_imp.instantiate_schema(schema_name=>SYS_CONTEXT('USERENV','CURRENT_SCHEMA'), export_db_name=>'LHRDB', inst_scn=>'4225469');

COMMIT;

END;

-- new object type path: SCHEMA_EXPORT/TABLE/TABLE

-- CONNECT SYS

CREATE TABLE "SCOTT"."DEPT"

?? (??? "DEPTNO" NUMBER(2,0),

??????? "DNAME" VARCHAR2(14 BYTE),

??????? "LOC" VARCHAR2(13 BYTE)

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

CREATE TABLE "SCOTT"."EMP"

?? (??? "EMPNO" NUMBER(4,0),

??????? "ENAME" VARCHAR2(10 BYTE),

??????? "JOB" VARCHAR2(9 BYTE),

??????? "MGR" NUMBER(4,0),

??????? "HIREDATE" DATE,

??????? "SAL" NUMBER(7,2),

??????? "COMM" NUMBER(7,2),

??????? "DEPTNO" NUMBER(2,0)

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

CREATE TABLE "SCOTT"."BONUS"

?? (??? "ENAME" VARCHAR2(10 BYTE),

??????? "JOB" VARCHAR2(9 BYTE),

??????? "SAL" NUMBER,

??????? "COMM" NUMBER

?? ) SEGMENT CREATION DEFERRED

? PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255

?NOCOMPRESS LOGGING

? TABLESPACE "USERS" ;

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

CREATE TABLE "SCOTT"."TEST"

?? (??? "DUMMY" VARCHAR2(1 BYTE)

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

-- new object type path: SCHEMA_EXPORT/TABLE/INDEX/INDEX

-- CONNECT SCOTT

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

? PCTFREE 10 INITRANS 2 MAXTRANS 255

? 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 1 ;

? ALTER INDEX "SCOTT"."PK_DEPT" NOPARALLEL;

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

? PCTFREE 10 INITRANS 2 MAXTRANS 255

? 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 1 ;

? ALTER INDEX "SCOTT"."PK_EMP" NOPARALLEL;

-- new object type path: SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

-- CONNECT SYS

ALTER TABLE "SCOTT"."DEPT" ADD CONSTRAINT "PK_DEPT" PRIMARY KEY ("DEPTNO")

? USING INDEX "SCOTT"."PK_DEPT"? ENABLE;

ALTER TABLE "SCOTT"."EMP" ADD CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO")

? USING INDEX "SCOTT"."PK_EMP"? ENABLE;

-- new object type path: SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

DECLARE I_N VARCHAR2(60);

? I_O VARCHAR2(60);

? NV VARCHAR2(1);

? c DBMS_METADATA.T_VAR_COLL;

? df varchar2(21) := 'YYYY-MM-DD:HH24:MI:SS';

?stmt varchar2(300) := ' INSERT INTO "SYS"."IMPDP_STATS" (type,version,flags,c1,c2,c3,c5,n1,n2,n3,n4,n5,n6,n7,n8,n9,n10,n11,n12,d1,cl1) VALUES (''I'',6,:1,:2,:3,:4,:5,

:6,:7,:8,:9,:10,:11,:12,:13,NULL,:14,:15,NULL,:16,:17)';

BEGIN

? DELETE FROM "SYS"."IMPDP_STATS";

? i_n := 'PK_DEPT';

? i_o := 'SCOTT';

? EXECUTE IMMEDIATE stmt USING 2,I_N,NV,NV,I_O,4,1,4,1,1,1,0,4,NV,NV,TO_DATE('2016-07-07 22:00:11',df),NV;

? DBMS_STATS.IMPORT_INDEX_STATS('"' || i_o || '"','"' || i_n || '"',NULL,'"IMPDP_STATS"',NULL,'"SYS"');

? DELETE FROM "SYS"."IMPDP_STATS";

END;

《《《《苫亦。。怨咪。屋剑。。诗眨。唉匾。。篇幅原因匠楚，有省略,剩下的都是統(tǒng)計信息巍膘，生成sqlfile的時候也可以不用生成。芋簿。峡懈。。与斤。肪康。荚恶。×字В》》》》

imp工具使用SHOW=Y LOG=GET_DDL.sql的方式谒撼，可以看到清晰的DDL腳本，同時也不會真正的執(zhí)行數(shù)據(jù)導入雾狈。另外廓潜，若單純?yōu)榱藢С鯠DL語句則可以在使用exp導出的時候使用ROWS=N選項，這樣導出的DMP文件比較小善榛。如下所示：

exp? \'/ AS SYSDBA\'? TABLES=SCOTT.EMP? FILE=/tmp/exp_ddl_lhr_01.dmp? LOG=/tmp/exp_table.log? BUFFER=41943040 ROWS=N COMPRESS=N

imp \'/ AS SYSDBA\' FILE=/tmp/exp_ddl_lhr_01.dmp SHOW=Y LOG=/tmp/get_ddl.sql BUFFER=20480000? FULL=Y

查看get_ddl.sql文件即可獲取DDL語句辩蛋。不過對于exp生成的DDL語句不能直接使用，需要使用SHELL腳本做相應的處理后才能使用移盆。整個示例如下所示：

[ZFZHLHRDB1:oracle]:/oracle>exp? \'/ AS SYSDBA\'? tables=scott.emp? file=/tmp/exp_ddl_lhr_01.dmp? log=/tmp/exp_table.log? buffer=41943040 rows=n compress=n

Export: Release 11.2.0.4.0 - Production on Tue Aug 2 15:42:11 2016

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Data Mining and Real Application Tes

Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set

Note: table data (rows) will not be exported

About to export specified tables via Conventional Path ...

Current user changed to SCOTT

. . exporting table??????????????????????????? EMP

Export terminated successfully without warnings.

[ZFZHLHRDB1:oracle]:/oracle>imp \'/ AS SYSDBA\' file=/tmp/exp_ddl_lhr_01.dmp show=y log=/tmp/get_ddl.sql buffer=20480000? full=y

Import: Release 11.2.0.4.0 - Production on Tue Aug 2 15:42:44 2016

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Data Mining and Real Application Tes

Export file created by EXPORT:V11.02.00 via conventional path

import done in ZHS16GBK character set and AL16UTF16 NCHAR character set

. importing SYS's objects into SYS

. importing SCOTT's objects into SCOTT

?"ALTER SESSION SET CURRENT_SCHEMA= "SCOTT""

?"CREATE TABLE "EMP" ("EMPNO" NUMBER(4, 0), "ENAME" VARCHAR2(10), "JOB" VARCH"

?"AR2(9), "MGR" NUMBER(4, 0), "HIREDATE" DATE, "SAL" NUMBER(7, 2), "COMM" NUM"

?"BER(7, 2), "DEPTNO" NUMBER(2, 0))? PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRAN"

?"S 255 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 FREELISTS 1 FREELIST "

?"GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS" LOGGING NOCOMPRESS"

?"CREATE UNIQUE INDEX "PK_EMP" ON "EMP" ("EMPNO" )? PCTFREE 10 INITRANS 2 MAX"

?"TRANS 255 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 FREELISTS 1 FREEL"

?"IST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS" LOGGING"

?"ALTER SESSION SET CURRENT_SCHEMA= "SCOTT""

?"ALTER TABLE "EMP" ADD? CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO") USING INDE"

?"X PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 NEXT 1048576 MIN"

?"EXTENTS 1 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "US"

?"ERS" LOGGING ENABLE "

?"ALTER TABLE "EMP" ADD CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO") REFEREN"

?"CES "DEPT" ("DEPTNO") ENABLE NOVALIDATE"

?"ALTER TABLE "EMP" ENABLE CONSTRAINT "FK_DEPTNO""

Import terminated successfully without warnings.

[ZFZHLHRDB1:oracle]:/oracle>

由于格式比較混亂堪澎，直接運行會報錯，建榮的書中給了一段代碼來格式化：

[ZFZHLHRDB1:oracle]:/tmp>more /tmp/get_ddl.sql