如何匯出儲存過程、函式、包和觸發器的定義語句？如何匯出表和索引的建立語句？

lhrbest發表於2018-04-13

Oracle中如何匯出儲存過程、函式、包和觸發器的定義語句？如何匯出表的結構？如何匯出索引的建立語句？

QQ群裡有人問：如何匯出一個使用者下的儲存過程？
麥苗答：方法有多種，可以使用DBMS_METADATA.GET_DDL包。

使用PL/SQL DEVELOPER工具

-- 下面的SQL語句，如果報錯：ORA-22835: 緩衝區對於 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，複製內容到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

開啟檔案後，簡單處理一下即可。

總體來說有兩種方式來獲取，第一，利用系統包DBMS_METADATA包中的GET_DDL函式來獲取，第二，利用exp或expdp來獲取。

下面來看第一種方式，如何利用系統包DBMS_METADATA包中的GET_DDL函式來獲取物件的定義語句。下面是該函式的入參和出參：

							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

其詳細引數如下：

l OBJECT_TYPE 需要返回原資料的DDL語句的物件型別

l NAME 物件名稱

l SCHEMA 物件所在的SCHEMA，預設為當前使用者所在所SCHEMA

l VERSION 物件原資料的版本

l MODEL 原資料的型別預設為ORACLE

l TRANSFORM 預設值為DDL

l RETURNS 物件的原資料預設以CLOB型別返回

一般情況下，只需要給出OBJECT_TYPE、NAME和SCHEMA3個引數即可。

n 檢視建立表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 檢視建立索引的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 檢視建立主鍵的SQL語句：

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

n 檢視建立外來鍵的SQL語句：

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

n 檢視建立檢視（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 檢視建立儲存過程（PROCEDURE）的SQL語句：

								SELECT DBMS_METADATA.GET_DDL('PROCEDURE',
U.OBJECT_NAME) 
							
								  FROM
USER_OBJECTS U 
							
								 WHERE
OBJECT_TYPE = 'PROCEDURE';

n 檢視建立觸發器（TRIGGER）的SQL語句：

								SELECT DBMS_METADATA.GET_DDL('TRIGGER',
U.OBJECT_NAME) 
							
								  FROM
USER_OBJECTS U 
							
								 WHERE
OBJECT_TYPE = 'TRIGGER';

n 檢視建立函式（FUNCTION）的SQL語句：

								SELECT DBMS_METADATA.GET_DDL('FUNCTION',
U.OBJECT_NAME) 
							
								  FROM
USER_OBJECTS U 
							
								 WHERE
OBJECT_TYPE = 'FUNCTION';

n 檢視建立包（PACKAGE）的SQL語句：

								SELECT DBMS_METADATA.GET_DDL('PACKAGE',
U.OBJECT_NAME) 
							
								  FROM
USER_OBJECTS U 
							
								 WHERE
OBJECT_TYPE = 'PACKAGE';

n 檢視建立序列（SEQUENCE）的SQL語句：

								SELECT DBMS_METADATA.GET_DDL('SEQUENCE',
U.OBJECT_NAME) 
							
								  FROM
USER_OBJECTS U 
							
								 WHERE
OBJECT_TYPE = 'SEQUENCE';

n 檢視建立同義詞（SYNONYM）的SQL語句：

								SELECT DBMS_METADATA.GET_DDL('SYNONYM',
U.OBJECT_NAME) 
							
								  FROM
USER_OBJECTS U 
							
								 WHERE
OBJECT_TYPE = 'SYNONYM';

n 檢視建立表空間（TABLESPACE）的SQL語句：

								SELECT DBMS_METADATA.GET_DDL('TABLESPACE',
U.TABLESPACE_NAME) 
							
								  FROM
USER_TABLESPACES U;

n 檢視建立角色（ROLE）的SQL語句：

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

n 檢視建立使用者（USER）的SQL語句：

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

n 得到某個SCHEDULER JOB的建立語句：

								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 得到一個使用者下的所有表、索引、儲存過程、函式的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');

如果想去掉表的儲存引數（例如，INITIAL、NEXT、FREELISTS等引數），那麼可以使用DBMS_METADATA包中的函式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()包內的引數都要大寫，否則會報ORA-31600: invalid input value table for parameter OBJECT_TYPE in function GET_DDL的錯誤。

（2）是否查的當前使用者的DDL語句，若不是則需要加上物件的屬主資訊即SCHEMA引數。

（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”，就可以將資料匯出到Excel檔案中，接著，將Excel中的資料複製到PLSQL Developer工具的“SQL Window”中皆可。需要注意的是，最後複製到“SQL Window”中的時候，需要選擇右鍵的“Past from host Language”，否則貼上的程式碼含有雙引號，需要做特殊處理，比較麻煩。

可以使用如下的SQL指令碼生成某個使用者下的所有物件的DDL語句：

									sqlplus<<eof <="" eof 
								
									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

下面介紹第二種匯出後設資料的方法，就是採用exp或expdp命令。資料泵工具（impdp）提供了SQLFILE的命令列選項，只獲取DDL語句，並未真正地執行資料匯入。另外，若單純為了匯出DDL語句則可以在使用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 
							
								Copyright (c) 1982, 2011, Oracle and/or its
affiliates.  All rights reserved. 
							
								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 
							
								Copyright (c) 1982, 2011, Oracle and/or its
affiliates.  All rights reserved. 
							
								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;  
							
								/ 
							
								《《《《。。。。。。。。篇幅原因，有省略,剩下的都是統計資訊，生成sqlfile的時候也可以不用生成。。。。。。。。》》》》

imp工具使用SHOW=Y LOG=GET_DDL.sql的方式，可以看到清晰的DDL指令碼，同時也不會真正的執行資料匯入。另外，若單純為了匯出DDL語句則可以在使用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 
							
								Copyright (c) 1982, 2011, Oracle and/or its
affiliates.  All rights reserved. 
							
								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 
							
								Copyright (c) 1982, 2011, Oracle and/or its
affiliates.  All rights reserved. 
							
								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 
							
								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]:/tmp>more /tmp/gettabddl.sh 
							
								awk ' 
							
								  /
\"BEGIN /   { N=1; } 
							
								  /
\"CREATE /   { N=1; } 
							
								  /
\"CREATE INDEX/   { N=1; } 
							
								  /
\"CREATE UNIQUE INDEX/  { N=1; } 
							
								  /
\"ALTER /   { N=1; } 
							
								  / \"
ALTER /   { N=1; } 
							
								  /
\"ANALYZE /   { N=1; } 
							
								  /
\"GRANT /    { N=1; } 
							
								  /
\"COMMENT /   { N=1; } 
							
								  /
\"AUDIT /     { N=1; } 
							
								  N==1 {
printf "\n/\n"; N++ } 
							
								  /\"$/
{ 
							
								    if (N==0)
next; 
							
								    s=index(
$0, "\"" ); 
							
ln0=length( $0 ) 
							
								    if ( s!=0
) { 
							
								      lcnt++ 
							
								      if (
lcnt >= 30 ) { 
							
ln=substr( $0,s+1,length( substr($0,s+1))-1) 
							
t=index( ln, ")," ) 
							
								        if (
t==0 ) { t=index( ln, ", " ) } 
							
								        if (
t==0 ) { t=index( ln, ") " ) } 
							
								        if (
t > 0 ) {  
							
printf "%s\n%s",substr( ln,1,t+1), substr(ln, t+2) 
							
lcnt=0 
							
								        } 
							
								        else
{ 
							
printf "%s", ln 
							
								          if
( ln0 < 78 ) { printf "\n" ; lcnt=0 } 
							
								        } 
							
								      } 
							
								      else { 
							
printf "%s",substr( $0,s+1,length( substr($0,s+1))-1 )  
							
								        if (
ln0 < 78 ) { printf "\n" ; lcnt=0 } 
							
								      } 
							
								    }  
							
								  } 
							
								  END {
printf "\n/\n"} 
							
								' $* |sed '1,2d; /^$/ d; 
							
								s/STORAGE *(INI/~   
STORAGE (INI/g; 
							
								s/, "/,~   
"/g; 
							
								s/ (\"/~  
&/g; 
							
								s/PCT[FI]/~   
&/g; 
							
								s/[( ]PARTITION /~&/g; 
							
								s/) TABLESPACE/)~   
TABLESPACE/g; 
							
								s/   , /
,~/g; 
							
								s/ DATAFILE 
/&~/' | tr "~" "\n" 
							
								[ZFZHLHRDB1:oracle]:/tmp> 
							
								[ZFZHLHRDB1:oracle]:/tmp>ksh /tmp/gettabddl.sh  /tmp/get_ddl.sql > /tmp/gen_tabddl.sql 
							
								[ZFZHLHRDB1:oracle]:/tmp>more /tmp/gen_tabddl.sql 
							
								ALTER SESSION SET CURRENT_SCHEMA= "SCOTT" 
							
								/ 
							
								CREATE TABLE "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))   
							
								    PCTFREE
10 PCTUSED 40 INITRANS 1 MAXTRANS 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 MAXTRANS 255  
							
								    STORAGE
(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 FREELISTS 1 FREELIST 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 INDEX  
							
								    PCTFREE
10 INITRANS 2 MAXTRANS 255  
							
								    STORAGE
(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT) 
							
TABLESPACE "USERS" LOGGING ENABLE  
							
								/ 
							
								ALTER TABLE "EMP" ADD CONSTRAINT
"FK_DEPTNO" FOREIGN KEY 
							
("DEPTNO") REFERENCES "DEPT" 
							
("DEPTNO") ENABLE NOVALIDATE 
							
								/ 
							
								ALTER TABLE "EMP" ENABLE CONSTRAINT
"FK_DEPTNO" 
							
								/

這樣執行起來就方便多了。

另外，使用imp工具的indexfile選項也可以把dmp檔案中的表和索引的建立語句匯出而不匯入任何物件，命令如下：

								imp userid/userid@service_name
file=/tmp/exp_ddl_lhr_01.dmp FULL=Y indexfile=/tmp/get_ti_ddl.sql rows=n 
							

示例如下所示：

								[oracle@rhel6lhr tmp]$ 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.3.0 - Production on Wed May
3 21:36:47 2017 
							
								Copyright (c) 1982, 2011, Oracle and/or its
affiliates.  All rights reserved. 
							
								Connected to: Oracle Database 11g Enterprise
Edition Release 11.2.0.3.0 - 64bit Production 
							
								With the Partitioning, Automatic Storage
Management, OLAP, Data Mining 
							
								and Real Application Testing options 
							
								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. 
							
								[oracle@rhel6lhr tmp]$ imp 
\'/ AS SYSDBA\' file=/tmp/exp_ddl_lhr_01.dmp FULL=Y
indexfile=/tmp/get_ti_ddl.sql rows=n 
							
								Import: Release 11.2.0.3.0 - Production on Wed May
3 21:38:10 2017 
							
								Copyright (c) 1982, 2011, Oracle and/or its
affiliates.  All rights reserved. 
							
								Connected to: Oracle Database 11g Enterprise
Edition Release 11.2.0.3.0 - 64bit Production 
							
								With the Partitioning, Automatic Storage
Management, OLAP, Data Mining 
							
								and Real Application Testing options 
							
								Export file created by EXPORT:V11.02.00 via
conventional path 
							
								import done in ZHS16GBK character set and AL16UTF16
NCHAR character set 
							
								Import terminated successfully without warnings. 
							
								[oracle@rhel6lhr tmp]$ more /tmp/get_ti_ddl.sql 
							
								REM  CREATE
TABLE "SCOTT"."EMP" ("EMPNO" NUMBER(4, 0),
"ENAME"  
							
								REM 
VARCHAR2(10), "JOB" VARCHAR2(9), "MGR" NUMBER(4, 0),
"HIREDATE" DATE,  
							
								REM 
"SAL" NUMBER(7, 2), "COMM" NUMBER(7, 2),
"DEPTNO" NUMBER(2, 0))  
							
								REM  PCTFREE
10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536  
							
								REM  NEXT
1048576 MINEXTENTS 1 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL  
							
								REM  DEFAULT)
TABLESPACE "USERS" LOGGING NOCOMPRESS ; 
							
								CONNECT SCOTT; 
							
								CREATE UNIQUE INDEX
"SCOTT"."PK_EMP" ON "EMP" ("EMPNO" )
PCTFREE 10  
							
								INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 NEXT
1048576 MINEXTENTS 1  
							
								FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS"  
							
								LOGGING ; 
							
								REM  ALTER
TABLE "SCOTT"."EMP" ADD CONSTRAINT "PK_EMP"
PRIMARY KEY  
							
								REM 
("EMPNO") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255  
							
								REM 
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 FREELISTS 1 FREELIST  
							
								REM  GROUPS 1
BUFFER_POOL DEFAULT) TABLESPACE "USERS" LOGGING ENABLE ; 
							
								REM  ALTER
TABLE "SCOTT"."EMP" ADD CONSTRAINT "FK_DEPTNO"
FOREIGN KEY  
							
								REM 
("DEPTNO") REFERENCES "DEPT" ("DEPTNO")
ENABLE NOVALIDATE ; 
							
								REM  ALTER
TABLE "SCOTT"."EMP" ENABLE CONSTRAINT "FK_DEPTNO"
; 
							
								[oracle@rhel6lhr tmp]$

可以看到其中的建立表的SQL語句被註釋掉了，這個可以用vi命令或者文字工具來處理，處理之後就可以直接使用了。

About Me

.............................................................................................................................................

● 本文作者：小麥苗，部分內容整理自網路，若有侵權請聯絡小麥苗刪除

● 本文在itpub（http://blog.itpub.net/26736162/abstract/1/）、部落格園（http://www.cnblogs.com/lhrbest）和個人微信公眾號（xiaomaimiaolhr）上有同步更新

● 本文itpub地址：http://blog.itpub.net/26736162/abstract/1/

● 本文部落格園地址：http://www.cnblogs.com/lhrbest

● 本文pdf版、個人簡介及小麥苗雲盤地址：http://blog.itpub.net/26736162/viewspace-1624453/

● 資料庫筆試面試題庫及解答：http://blog.itpub.net/26736162/viewspace-2134706/

● DBA寶典今日頭條號地址：http://www.toutiao.com/c/user/6401772890/#mid=1564638659405826

.............................................................................................................................................

● QQ群號：230161599（滿）、618766405

● 微信群：可加我微信，我拉大家進群，非誠勿擾

● 聯絡我請加QQ好友（646634621），註明新增緣由

● 於 2018-04-01 06:00 ~ 2018-04-31 24:00 在魔都完成

● 最新修改時間：2018-04-01 06:00 ~ 2018-04-31 24:00

● 文章內容來源於小麥苗的學習筆記，部分整理自網路，若有侵權或不當之處還請諒解

● 版權所有，歡迎分享本文，轉載請保留出處

.............................................................................................................................................

● 小麥苗的微店：https://weidian.com/s/793741433?wfr=c&ifr=shopdetail

● 小麥苗出版的資料庫類叢書：http://blog.itpub.net/26736162/viewspace-2142121/

● 小麥苗OCP、OCM、高可用網路班：http://blog.itpub.net/26736162/viewspace-2148098/

.............................................................................................................................................

使用微信客戶端掃描下面的二維碼來關注小麥苗的微信公眾號（xiaomaimiaolhr）及QQ群（DBA寶典），學習最實用的資料庫技術。

小麥苗的微信公眾號小麥苗的DBA寶典QQ群2 《DBA筆試面試寶典》讀者群小麥苗的微店

.............................................................................................................................................

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26736162/viewspace-2152892/，如需轉載，請註明出處，否則將追究法律責任。

mysql 匯入匯出資料庫以及函式、儲存過程的介紹
2021-09-09
MySql資料庫函式儲存過程
Mysql中儲存過程、儲存函式、自定義函式、變數、流程控制語句、游標/遊標、定義條件和處理程式的使用示例
2024-07-11
MySql儲存過程儲存函式變數
PostgreSQL獲取建表語句儲存過程
2021-06-07
SQL儲存過程
MSSQL 如何匯出db所有使用者許可權建立語句
2019-10-08
SQL
SQL Server如何匯出db所有使用者許可權建立語句
2019-10-10
SQLServer
MySQL儲存過程語句及呼叫
2018-11-30
MySql儲存過程
【SQL】Oracle BLOB 批量匯入匯出圖片語句
2021-03-11
SQLOracle
MySQL 匯出一條資料的插入語句
2024-06-05
MySql
七、函式-儲存過程-觸發器
2022-04-08
函式儲存過程觸發器
【PG】ora2pg 分別匯出表，索引，儲存過程等
2024-03-14
索引儲存過程
語句觸發器
2018-06-14
觸發器
【MySQL】MySQL（三）儲存過程和函式、觸發器、事務
2021-10-17
MySql儲存過程函式觸發器
oracle儲存過程和觸發器
2019-01-06
Oracle儲存過程觸發器
SSH框架控制檯輸出HQL語句和SQL語句的方法
2021-01-01
框架SQL
EF中使用SQL語句或儲存過程
2018-04-10
SQL儲存過程
mysql和orcale的儲存過程和儲存函式
2018-06-04
MySql儲存過程儲存函式
如何在MySQL 5.7中使用SELECT … INTO語句匯出資料檔案？
2021-09-03
MySql
oracle匯出使用者、表空間和角色的建立語句
2020-09-29
Oracle
day25-索引和函式及儲存過程
2024-04-26
索引函式儲存過程
Oracle儲存過程乾貨（二）：PLSQL控制語句
2024-03-06
Oracle儲存過程SQL
Oracle儲存過程乾貨（三）：PLSQL迴圈語句
2024-03-07
Oracle儲存過程SQL
金倉資料庫KingbaseES儲存過程 RETURN語句
2023-03-31
資料庫儲存過程
oracle使用儲存過程將表資料以excel格式匯出
2024-07-24
Oracle儲存過程Excel
MySQL 儲存過程和函式
2022-04-23
MySql儲存過程函式
MySQL儲存過程和函式
2020-12-12
MySql儲存過程函式
瞭解使用mysql 的檢視、儲存過程、觸發器、函式....
2020-12-03
MySql儲存過程觸發器函式
如何使用 JavaScript 匯入和匯出 Excel
2024-03-26
JavaScriptExcel
MySQL查詢語句過程和EXPLAIN語句的基本概念及其最佳化
2021-09-03
MySqlAI
IDEA自定義設定快捷鍵輸出你想要的語句！
2018-11-20
Idea
java匯出Excel定義匯出模板
2020-04-05
JavaExcel
分支語句和迴圈語句
2020-11-07
DBeave如何檢視資料庫表的DDL建立語句
2024-10-30
資料庫
Mysql儲存過程變數，條件，迴圈語句用法
2018-06-26
MySql儲存過程變數
[20190118]toad下如何除錯儲存過程和函式.txt
2019-01-18
除錯儲存過程函式
Navicat如何匯入和匯出sql檔案
2022-03-01
SQL
Docker容器和映象的建立/匯出/刪除/匯入
2020-09-18
Docker
深入mysql建立自定義函式與儲存過程的詳解
2021-09-09
MySql函式儲存過程
表示式和基本語句的規範
2020-04-06

如何匯出儲存過程、函式、包和觸發器的定義語句？如何匯出表和索引的建立語句？

Oracle中如何匯出儲存過程、函式、包和觸發器的定義語句？如何匯出表的結構？如何匯出索引的建立語句？

相關文章