oracle 提取建表语句,数据泵提取建表空间语句和建表语句

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