[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
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 "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
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 "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(*)
----------
0
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.