[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
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.