[20161002]impdp导入空表.txt-白红宇

[20161002]impdp导入空表.txt

--业务需求要求建立新的测试库,由于磁盘空间有限,要求几个大表导入空表,11g支持段延迟提交,即使表init很大也不会出现空间问题.

--全表的数据已经通过expdp导出.自己测试一下如何实现:

1.环境:

SCOTT@test01p> @ ver1

PORT_STRING VERSION BANNER CON_ID

-------------------- ---------- ---------------------------------------------------------------------------- ------

IBMPC/WIN_NT64-9.1.0 12.1.0.1.0 Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production 0

create table tx as select rownum id,'test' name from dual connect by level<=1e4;

create table ty as select rownum id,'TEST' name from dual connect by level<=1e4;

D:\>expdp scott/btbtms@test01p directory=TMP_EXPDP dumpfile=t_all.dp logfile=t_all.log tables=(tx,ty)

expdp scott/btbtms@test01p directory=TMP_EXPDP dumpfile=t_all.dp logfile=t_all.log tables=(tx,ty)

Export: Release 12.1.0.1.0 - Production on Sun Oct 2 21:41:44 2016

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 "SCOTT"."SYS_EXPORT_TABLE_01": scott/a**@test01p directory=TMP_EXPDP dumpfile=t_all.dp logfile=t_all.log tables=(tx,ty)

Estimate in progress using BLOCKS method...

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 384 KB

Processing object type TABLE_EXPORT/TABLE/TABLE

Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER

. . exported "SCOTT"."TX" 132.2 KB 10000 rows

. . exported "SCOTT"."TY" 132.2 KB 10000 rows

Master table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded

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

Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:

D:\TMP\EXPDP\T_ALL.DP

Job "SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at Sun Oct 2 21:42:31 2016 elapsed 0 00:00:45

2.导入:

--建立导入参数文件impdo.txt

directory=TMP_EXPDP

dumpfile=t_all.dp

logfile=t_all_imp.log

QUERY=SCOTT.TX:"where 1=0"

#QUERY=SCOTT.TY:"where 1=0"

--两个表没有索引，我直接改名看看,这样导入不会报错。

SCOTT@test01p> alter table tx rename to tx1;

Table altered.

SCOTT@test01p> alter table ty rename to ty1;

Table altered.

impdp scott/btbtms@test01p PARFILE=impdo.txt

D:\tools\rlwrap>impdp scott/btbtms@test01p PARFILE=impdo.txt

impdp scott/btbtms@test01p PARFILE=impdo.txt

Import: Release 12.1.0.1.0 - Production on Sun Oct 2 21:51:28 2016

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 "SCOTT"."SYS_IMPORT_FULL_01" successfully loaded/unloaded

Starting "SCOTT"."SYS_IMPORT_FULL_01": scott/a*@test01p PARFILE=impdo.txt

Processing object type TABLE_EXPORT/TABLE/TABLE

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

. . imported "SCOTT"."TX" 132.2 KB 0 out of 10000 rows

. . imported "SCOTT"."TY" 132.2 KB 10000 rows

Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER

Job "SCOTT"."SYS_IMPORT_FULL_01" successfully completed at Sun Oct 2 21:52:03 2016 elapsed 0 00:00:33

SCOTT@test01p> select count(*) from tx;

COUNT(*)

----------

SCOTT@test01p> select count(*) from ty;

COUNT(*)

----------

10000

--可以发现表tx没有导入，提示0 out of 10000 rows，不过我的测试段还是分配的。

SCOTT@test01p> column PARTITION_NAME noprint

SCOTT@test01p> select * from dba_extents where segment_name in ('TX','TY');

OWNER SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME EXTENT_ID FILE_ID BLOCK_ID BYTES BLOCKS RELATIVE_FNO

------ ------------ ------------ --------------- ---------- ---------- ---------- ---------- ---------- ------------

SCOTT TX TABLE USERS 0 9 19744 65536 8 9

SCOTT TY TABLE USERS 0 9 19752 65536 8 9

SCOTT TY TABLE USERS 1 9 19760 65536 8 9

SCOTT TY TABLE USERS 2 9 19768 65536 8 9

3.另外11g impdp支持TRANSFORM参数，参考

http://blog.itpub.net/267265/viewspace-1846944/

Usage: TRANSFORM = transform_name:value[:object_type]

These are the applicable transform_names

SEGMENT_ATTRIBUTES: by default value is y which will copy the objects as it is in the export dump with all segment

attributes. If you specify the value as n the import job will omit the segment_attributes in the dump file and it

will use the tablespace/user default values.

STORAGE: by default the value for this parameter is y which will include all storage clauses during the import job.

If you specify the parameter value as n then it will omit the storage clause in the dump file and it will follow the

default values in the tablespace.

PCTSPACE: it is the percent multiplier for the extent allocations and size of the datafiles during the import.

OID: object id (OID) mainly used for the TYPE objects. Each and every type is identified by OID which will be

unique. If you create a type without specifying the OID the RDBMS itself will create and assign unique OID to the

new TYPE object. See below examples for more details.