資料泵含有很多靈活的功能，比如使用expdp匯出用於重建表空間的DDL語句，或者建表的DDL語句。

使用expdp匯出建立表空間的後設資料，其中引數include會限制匯出範圍為表空間

[oracle@snow ~]$ expdp dp/dp directory=dp_dir dumpfile=ex.dmp full=y include=tablespace

Export: Release 12.1.0.1.0 - Production on Mon Feb 9 13:24:18 2015

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

Starting "DP"."SYS_EXPORT_FULL_01": dp/******** directory=dp_dir dumpfile=ex.dmp full=y include=tablespace

Estimate in progress using BLOCKS method...

Total estimation using BLOCKS method: 0 KB

Processing object type DATABASE_EXPORT/TABLESPACE

Master table "DP"."SYS_EXPORT_FULL_01" successfully loaded/unloaded

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

Dump file set for DP.SYS_EXPORT_FULL_01 is:

/home/oracle/ex.dmp

Job "DP"."SYS_EXPORT_FULL_01" successfully completed at Mon Feb 9 13:24:24 2015 elapsed 0 00:00:04

使用impdp工具生成建立表空間的sql語句，sqlfile引數定義了儲存sql語句指令碼的名稱為tbsp.sql

這個impdp語句看上去會有些困惑，但它不會像資料庫匯入任何資料，只是生成一個sql指令碼而已。

[oracle@snow ~]$ impdp dp/dp directory=dp_dir dumpfile=ex.dmp sqlfile=tbsp.sql

Import: Release 12.1.0.1.0 - Production on Mon Feb 9 13:27:31 2015

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

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

Starting "DP"."SYS_SQL_FILE_FULL_01": dp/******** directory=dp_dir dumpfile=ex.dmp sqlfile=tbsp.sql

Processing object type DATABASE_EXPORT/TABLESPACE

Job "DP"."SYS_SQL_FILE_FULL_01" successfully completed at Mon Feb 9 13:27:33 2015 elapsed 0 00:00:01

看看生成的sql語句全貌

[oracle@snow ~]$ more tbsp.sql

-- CONNECT DP

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: DATABASE_EXPORT/TABLESPACE

CREATE UNDO TABLESPACE "UNDOTBS1" DATAFILE

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

AUTOEXTEND ON NEXT 5242880 MAXSIZE 32767M

BLOCKSIZE 8192

EXTENT MANAGEMENT LOCAL AUTOALLOCATE;

ALTER DATABASE DATAFILE

'/u01/app/oracle/oradata/ora12c/undotbs01.dbf' RESIZE 183500800;

CREATE TEMPORARY TABLESPACE "TEMP" TEMPFILE

'/u01/app/oracle/oradata/ora12c/temp01.dbf' SIZE 92274688

AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M

EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1048576;

CREATE TABLESPACE "USERS" DATAFILE

'/u01/app/oracle/oradata/ora12c/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/ora12c/users01.dbf' RESIZE 11796480;

CREATE TABLESPACE "EXAMPLE" DATAFILE

'/u01/app/oracle/oradata/ora12c/example01.dbf' SIZE 104857600

AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M

NOLOGGING ONLINE PERMANENT BLOCKSIZE 8192

EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT

NOCOMPRESS SEGMENT SPACE MANAGEMENT AUTO;

ALTER DATABASE DATAFILE

'/u01/app/oracle/oradata/ora12c/example01.dbf' RESIZE 338821120;

下面的例子是使用資料泵提取建表語句，注意引數content=metadata_only

[oracle@snow ~]$ expdp dp/dp directory=dp_dir dumpfile=tab.dmp full=y include=table content=metadata_only

[oracle@snow ~]$ impdp dp/dp directory=dp_dir dumpfile=tab.dmp sqlfile=tabs.sql

[oracle@snow ~]$ more tabs.sql

-- CONNECT DP

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: DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE

CREATE TABLE "SYS"."KU$_USER_MAPPING_VIEW"

( "USER#" NUMBER NOT NULL ENABLE,

"NAME" VARCHAR2(128 BYTE) NOT NULL ENABLE

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

...

全文完

12c 資料泵提取建表空間語句和建表語句

相關文章