[20151126]IMPDP TRANSFORM参数.TXT
--最近要建立一个测试库，原来生产系统的一些表在定义时
STORAGE (
INITIAL 8G
....
)
NOPARALLEL;
--实际我不需要建立这个大的INITIAL表，而且可能许多还是空的。有些我可能仅仅导入少量数据，这样要浪费大量磁盘空间，并且测试
--机器磁盘空间也不足。不能这样导入。
--另外一个问题有一些表我设置PCTFREE很大(缺省10)，主要问题是避免行迁移。如果这样导入也会导致磁盘空间浪费。
--这导致我开始想通过先建立空表，然后导入的想法，我仔细阅读参数TABLE_EXISTS_ACTION，参考链接
--http://blog.itpub.net/267265/viewspace-1846224/
--晚上我仔细看一些文档，发现oracle 11G还支持参数TRANSFORM,通过这个参数可以屏蔽原来的参数，还是通过例子来说明：
1.建立测试环境：
SCOTT@book> @ &r/ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
--建立如下表：
CREATE TABLE SCOTT.EMPX
(
EMPNO NUMBER(4),
ENAME VARCHAR2(10 BYTE),
JOB VARCHAR2(9 BYTE),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2)
)
TABLESPACE USERS
RESULT_CACHE (MODE DEFAULT)
PCTUSED 0
PCTFREE 50
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 50M
NEXT 1M
MAXSIZE UNLIMITED
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
FLASH_CACHE DEFAULT
CELL_FLASH_CACHE DEFAULT
)
NOPARALLEL;
SCOTT@book> insert into empx select * from emp ;
14 rows created.
SCOTT@book> commit ;
Commit complete.
--建立表empx，INITIAL=50M,PCTFREE=50.
SCOTT@book> select segment_name,bytes,blocks,INITIAL_EXTENT,NEXT_EXTENT from dba_segments where owner=user and segment_name='EMPX';
SEGMENT_NAME BYTES BLOCKS INITIAL_EXTENT NEXT_EXTENT
-------------------- ---------- ---------- -------------- -----------
EMPX 52428800 6400 52428800 1048576
SCOTT@book> column PARTITION_NAME noprint
SCOTT@book> select * from dba_extents where owner=user and segment_name='EMPX';
OWNER SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME EXTENT_ID FILE_ID BLOCK_ID BYTES BLOCKS RELATIVE_FNO
------ -------------------- ------------------ -------------------------- ---------- ---------- ---------- ---------- ------------
SCOTT EMPX TABLE USERS 0 4 1280 8388608 1024 4
SCOTT EMPX TABLE USERS 1 4 2304 8388608 1024 4
SCOTT EMPX TABLE USERS 2 4 3328 8388608 1024 4
SCOTT EMPX TABLE USERS 3 4 4352 8388608 1024 4
SCOTT EMPX TABLE USERS 4 4 5376 8388608 1024 4
SCOTT EMPX TABLE USERS 5 4 6400 8388608 1024 4
SCOTT EMPX TABLE USERS 6 4 7424 1048576 128 4
SCOTT EMPX TABLE USERS 7 4 7552 1048576 128 4
8 rows selected.
--可以看到分配6个8M的段，2个1M的段。这样占用很大的磁盘空间。
2.导出：
$ expdp scott/book dumpfile=empx78.dmp logfile=empx78.log tables=scott.empx
Export: Release 11.2.0.4.0 - Production on Thu Nov 26 09:47:13 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SCOTT"."SYS_EXPORT_TABLE_01": scott/a******* dumpfile=empx78.dmp logfile=empx78.log tables=scott.empx
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 50 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "SCOTT"."EMPX" 8.562 KB 14 rows
Master table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:
/u01/app/oracle/admin/book/dpdump/empx78.dmp
Job "SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at Thu Nov 26 09:47:28 2015 elapsed 0 00:00:11
3.如果按照原来导入，占用50M太浪费：
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.
$ impdp scott/book dumpfile=empx78.dmp logfile=empx78x.log remap_table=scott.empx:empy full=y transform=SEGMENT_ATTRIBUTES:n:table,STORAGE:N:table
Import: Release 11.2.0.4.0 - Production on Thu Nov 26 09:53:46 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SCOTT"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SCOTT"."SYS_IMPORT_FULL_01": scott/a***** dumpfile=empx78.dmp logfile=empx78x.log remap_table=scott.empx:empy full=y transform=SEGMENT_ATTRIBUTES:n:table,STORAGE:N:table
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "SCOTT"."EMPY" 8.562 KB 14 rows
Job "SCOTT"."SYS_IMPORT_FULL_01" successfully completed at Thu Nov 26 09:53:51 2015 elapsed 0 00:00:03
SCOTT@book> @ &r/ddl scott.empy
C100
---------------------------------------------------------------------------------
CREATE TABLE "SCOTT"."EMPY"
( "EMPNO" NUMBER(4,0),
"ENAME" VARCHAR2(10),
"JOB" VARCHAR2(9),
"MGR" NUMBER(4,0),
"HIREDATE" DATE,
"SAL" NUMBER(7,2),
"COMM" NUMBER(7,2),
"DEPTNO" NUMBER(2,0)
) SEGMENT CREATION IMMEDIATE
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 "USERS" ;
--注意看INITIAL=64k,PCTFREE=10.
SCOTT@book> select * from dba_extents where owner=user and segment_name='EMPY';
OWNER SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME EXTENT_ID FILE_ID BLOCK_ID BYTES BLOCKS RELATIVE_FNO
------ -------------------- ------------------ ------------------------------ ---------- ---------- ---------- ---------- ---------- ------------
SCOTT EMPY TABLE USERS 0 4 744 65536 8 4
--这样就符合我的需要了。我还可以先导入表定义，这样再通过其它的方式插入数据。使用参数CONTENT=METADATA_ONLY。
--例子：
$ impdp scott/book dumpfile=empx78.dmp logfile=empx78x.log remap_table=scott.empx:empz full=y transform=SEGMENT_ATTRIBUTES:n:table,STORAGE:N:table CONTENT=METADATA_ONLY
Import: Release 11.2.0.4.0 - Production on Thu Nov 26 09:58:50 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SCOTT"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SCOTT"."SYS_IMPORT_FULL_01": scott/a******* dumpfile=empx78.dmp logfile=empx78x.log remap_table=scott.empx:empz full=y transform=SEGMENT_ATTRIBUTES:n:table,STORAGE:N:table CONTENT=METADATA_ONLY
Processing object type TABLE_EXPORT/TABLE/TABLE
Job "SCOTT"."SYS_IMPORT_FULL_01" successfully completed at Thu Nov 26 09:58:54 2015 elapsed 0 00:00:02
SCOTT@book> select * from dba_extents where owner=user and segment_name='EMPZ';
no rows selected
--如果不指定后面的参数:table，改成如下SEGMENT_ATTRIBUTES:n, 对其它object_type也有效。