12c 数据泵提取建表空间语句和建表语句

数据泵含有很多灵活的功能，比如使用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

Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved.

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

Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved.

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

...

全文完

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29047826/viewspace-1441621/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29047826/viewspace-1441621/