advertisements
_____________________________________________________________________________________________________________________
Data pump impdp has many options to transform
the metadata during the import operation. Those are REMAP_DATAFILE, REMAP_SCHEMA,
REMAP_TABLESPACE and the TRANSFORM.
Using this option you can
alter specific objects ddls and applicable for all the objects being imported.
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.
If
you specify transform=OID:y:type the import job will import the type with same
OID during the export, otherwise it will assign a new OID during the import.
Example is mentioned below.
Example
on SEGMENT_ATTRIBUTES & STORAGE
I have table called emp in my schema and tablespace as siebel. I
am going to change its default storage parameters, tablespace etc.
SQL> ALTER TABLE SCOTT.EMP STORAGE( NEXT 50M);
Table altered.
SQL> ALTER TABLE SCOTT.EMP PCTFREE 20;
Table altered.
I am changing the default tablespace for the user from Siebel to
users.
SQL> ALTER USER SCOTT DEFAULT TABLESPACE USERS;
User altered.
Now the table definition is as follows
SQL> select dbms_metadata.get_ddl('TABLE','EMP','SCOTT') from
dual;
DBMS_METADATA.GET_DDL('TABLE','EMP','SCOTT')
---------------------------------------------------------
CREATE TABLE "SCOTT"."EMP"
( "EMPNO" NUMBER(4,0) NOT NULL
ENABLE,
"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
20 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536
NEXT 52428800 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS
1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DE
FAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "SIEBEL"
Taking the export of the emp table.
expdp job_name=schemaexp1
tables=scott.emp dumpfile=exp_emp.dmp logfile=exp_emp.log directory=EXP_DIR
compression=all
Export: Release 11.2.0.2.0 - Production on Wed Nov 28 06:19:05
2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Username: / as sysdba
Connected to: Oracle Database 11g Enterprise Edition Release
11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic
Storage Management, OLAP,
Data Mining and Real Application Testing options
Starting "SYS"."SCHEMAEXP1": /******** AS SYSDBA job_name=schemaexp1
tables=scott.emp dumpfile=exp_emp.dmp logfile=exp_emp.log directory=EXP_DIR
compression=all
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "SCOTT"."EMP" 5.617 KB 14 rows
Master table "SYS"."SCHEMAEXP1" successfully
loaded/unloaded
******************************************************************************
Dump file set for SYS.SCHEMAEXP1 is:
/u01/backup1/export/exp_emp.dmp
Job "SYS"."SCHEMAEXP1" successfully
completed at 06:19:16
Dropping the table from the schema.
SQL> drop table scott.emp;
Table dropped.
Importing the table with SEGMENT_ATTRIBUTES:n:table,STORAGE:N:table
which will create the table with user default values. See the values marked in RED
color after import.
$ impdp dumpfile=exp_emp.dmp logfile=imp_emp.log
directory=EXP_DIR full=y transform=SEGMENT_ATTRIBUTES:n:table,STORAGE:N:table
Import: Release 11.2.0.2.0 - Production on Wed Nov 28 06:24:05
2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Username: / AS SYSDBA
Connected to: Oracle Database 11g Enterprise Edition Release
11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic
Storage Management, OLAP,
Data Mining and Real Application Testing options
Master table "SYS"."SYS_IMPORT_FULL_01"
successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_FULL_01": /******** AS SYSDBA dumpfile=exp_emp.dmp
logfile=imp_emp.log directory=EXP_DIR 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"."EMP" 5.617 KB 14 rows
Processing object type
TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SYS"."SYS_IMPORT_FULL_01" successfully
completed at 06:24:10
SQL> select dbms_metadata.get_ddl('TABLE','EMP','SCOTT') from
dual;
DBMS_METADATA.GET_DDL('TABLE','EMP','SCOTT')
---------------------------------------------------------
CREATE TABLE "SCOTT"."EMP"
( "EMPNO" NUMBER(4,0) NOT NULL
ENABLE,
"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 DE
FAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS"
Example on OID
Create a type with unique OID.
SQL> SELECT SYS_OP_GUID() FROM DUAL;
SYS_OP_GUID()
--------------------------------
CF8C98BA272CA45CE0400C0A8D6776F1
CREATE TYPE APPtype OID 'TESTOID'
AS OBJECT (attrib1 NUMBER);
SQL> CREATE TYPE APPtype OID
'CF8C98BA272CA45CE0400C0A8D6776F1'
AS OBJECT (attrib1 NUMBER);
/
Type created.
SQL> select TYPE_NAME, TYPE_OID from user_types where
TYPE_NAME='APPTYPE';
TYPE_NAME
TYPE_OID
------------------------------ --------------------------------
APPTYPE
CF8C98BA272CA45CE0400C0A8D6776F1
Taking the export of only TYPE objects from the schema using
include option
expdp job_name=schemaexp1 schemas=scott include=type
dumpfile=exp_type.dmp logfile=exp_type.log directory=EXP_DIR compression=all
Export: Release 11.2.0.2.0 - Production on Wed Nov 28 05:31:07
2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Username: / as sysdba
Connected to: Oracle Database 11g Enterprise Edition Release
11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic
Storage Management, OLAP,
Data Mining and Real Application Testing options
Starting "SYS"."SCHEMAEXP1": /******** AS SYSDBA job_name=schemaexp1
schemas=scott include=type dumpfile=exp_type.dmp logfile=exp_type.log
directory=EXP_DIR compression=all
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 0 KB
Processing object type SCHEMA_EXPORT/TYPE/TYPE_SPEC
Master table "SYS"."SCHEMAEXP1" successfully
loaded/unloaded
******************************************************************************
Dump file set for SYS.SCHEMAEXP1 is:
/u01/backup1/export/exp_type.dmp
Job "SYS"."SCHEMAEXP1" successfully
completed at 05:31:17
Dropping the type
SQL> drop type APPTYPE;
Type dropped.
Importing the type object with transform=OID:n:type which will
create the type object with new OID value.
$ impdp dumpfile=exp_type.dmp logfile=imp_exp_type.log
directory=EXP_DIR full=y transform=OID:n:type
Import: Release 11.2.0.2.0 - Production on Wed Nov 28 05:34:40
2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Username: / as sysdba
Connected to: Oracle Database 11g Enterprise Edition Release
11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic
Storage Management, OLAP,
Data Mining and Real Application Testing options
Master table "SYS"."SYS_IMPORT_FULL_01"
successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_FULL_01": /******** AS SYSDBA dumpfile=exp_type.dmp
logfile=imp_exp_type.log directory=EXP_DIR full=y transform=OID:n:type
Processing object type SCHEMA_EXPORT/TYPE/TYPE_SPEC
Job "SYS"."SYS_IMPORT_FULL_01" successfully
completed at 05:34:45
Object created with new OID value.
SQL> select TYPE_NAME, TYPE_OID from user_types where
TYPE_NAME='APPTYPE';
TYPE_NAME
TYPE_OID
------------------------------ --------------------------------
APPTYPE
CF8CB70D758454DAE0400C0A8D671725
SQL> drop type APPTYPE;
Type dropped.
Importing the dump with transform=OID:y:type which will maintain
the same OID values during the export.
$ impdp dumpfile=exp_type.dmp logfile=imp_exp_type.log
directory=EXP_DIR full=y transform=OID:y:type
Import: Release 11.2.0.2.0 - Production on Wed Nov 28 05:35:47
2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Username: / as sysdba
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0
- 64bit Production
With the Partitioning, Real Application Clusters, Automatic
Storage Management, OLAP,
Data Mining and Real Application Testing options
Master table "SYS"."SYS_IMPORT_FULL_01"
successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_FULL_01": /******** AS SYSDBA dumpfile=exp_type.dmp
logfile=imp_exp_type.log directory=EXP_DIR full=y transform=OID:y:type
Processing object type SCHEMA_EXPORT/TYPE/TYPE_SPEC
Job "SYS"."SYS_IMPORT_FULL_01" successfully
completed at 05:35:56
Type object imported with same OID during the export.
SQL> select TYPE_NAME, TYPE_OID from user_types where
TYPE_NAME='APPTYPE';
TYPE_NAME
TYPE_OID
------------------------------ --------------------------------
APPTYPE
CF8C98BA272CA45CE0400C0A8D6776F1
_____________________________________________________________________________________________________________________
0 comments:
Post a Comment