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