transform parameter in impdp

Transform parameter allows you to alter object creation DDL while performing import.  I have tried all the attempt to make the understanding of transform parameter easy.

TRANSFORM = transform_name:value[:object_type]

The transform_name specifies the name of the transform. The possible options are as follows:

  SEGMENT_ATTRIBUTES – If the value is specified as y, then segment attributes (physical attributes, storage attributes, tablespaces, and logging) are included, with appropriate DDL. The default is y.
STORAGE – If the value is specified as y, the storage clauses are included, with appropriate DDL. The default is y. This parameter is ignored if SEGMENT_ATTRIBUTES=n.
OID – If the value is specified as n, the assignment of the exported OID during the creation of object tables and types is inhibited. Instead, a new OID is assigned. This can be useful for cloning schemas, but does not affect referenced objects. The default value is y.
PCTSPACE – The value supplied for this transform must be a number greater than zero. It represents the percentage multiplier used to alter extent allocations and the size of data files.

The object_type is optional. If supplied, it designates the object type to which the transform will be applied. If no object type is specified then the transform applies to all valid object types.

Take an example,

Lets take an export  for tablespace users.

expdp system/sys dumpfile=export:users.dmp tablespaces=users;

Now, take an import without actually performing import of objects on the database. Instead just create create the sql file which will contain all the DDL statements that an import would follow while actually performing import.

impdp system/sys tablespaces=users dumpfile=export:users.dmp sqlfile=export:ss1.sql

Here is the content of ss1.sql

— CONNECT SYSTEM
— new object type path is: TABLE_EXPORT/TABLE/TABLE
CREATE TABLE “SCOTT”.”DEPT”
(    “DEPTNO” NUMBER(2,0),
“DNAME” VARCHAR2(14),
“LOC” VARCHAR2(13)
) 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)
TABLESPACE “USERS” ;

CREATE TABLE “SCOTT”.”EMP”
(    “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)
) 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)
TABLESPACE “USERS” ;
// content trimmed //

Here all the DDL statement contains all the segment attributes. Lets use transform attribute to skip segment attributes in total.

[oracle@host1 exp]$ impdp system/sys dumpfile=export:users.dmp tablespaces=users sqlfile=export:s1.sql transform=segment_attributes:n

Here is the content of s1.sql

— CONNECT SYSTEM
— new object type path is: TABLE_EXPORT/TABLE/TABLE
CREATE TABLE “SCOTT”.”DEPT”
(    “DEPTNO” NUMBER(2,0),
“DNAME” VARCHAR2(14),
“LOC” VARCHAR2(13)
) ;

CREATE TABLE “SCOTT”.”EMP”
(    “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)
) ;

Now, use transform attribute to skip only the storage clause.

[oracle@host1 exp]$ impdp system/sys dumpfile=export:users.dmp tablespaces=users sqlfile=export:s1.sql transform=storage:n

Here is the content of s1.sql

— CONNECT SYSTEM
— new object type path is: TABLE_EXPORT/TABLE/TABLE
CREATE TABLE “SCOTT”.”DEPT”
(    “DEPTNO” NUMBER(2,0),
“DNAME” VARCHAR2(14),
“LOC” VARCHAR2(13)
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
TABLESPACE “USERS” ;

CREATE TABLE “SCOTT”.”EMP”
(    “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)
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
TABLESPACE “USERS” ;

Now, lets try to alter buffer_pool parameter storage clause. Here is a simple demo:

SQL> create table hr.tes4(c1 number) storage(buffer_pool keep);

Table created

[oracle@host1 exp]$ expdp hr/hr tables=tes4 dumpfile=tes4.dmp directory=export

Export: Release 10.2.0.1.0 – Production on Monday, 11 March, 2013 15:07:58

Copyright (c) 2003, 2005, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Production
With the Partitioning, OLAP and Data Mining options
Starting “HR”.”SYS_EXPORT_TABLE_01″:  hr/******** tables=tes4 dumpfile=export.tes4.dmp directory=export
Estimate in progress using BLOCKS method…
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 0 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported “HR”.”TES4″                                     0 KB       0 rows
Master table “HR”.”SYS_EXPORT_TABLE_01″ successfully loaded/unloaded
******************************************************************************
Dump file set for HR.SYS_EXPORT_TABLE_01 is:
/home/oracle/exp/export.tes4.dmp
Job “HR”.”SYS_EXPORT_TABLE_01″ successfully completed at 15:08:01

[oracle@host1 exp]$ impdp hr/hr tables=tes4 dumpfile=export.tes4.dmp directory=export sqlfile=s1.sql

Import: Release 10.2.0.1.0 – Production on Monday, 11 March, 2013 15:08:15

Copyright (c) 2003, 2005, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Production
With the Partitioning, OLAP and Data Mining options
Master table “HR”.”SYS_SQL_FILE_TABLE_01″ successfully loaded/unloaded
Starting “HR”.”SYS_SQL_FILE_TABLE_01″:  hr/******** tables=tes4 dumpfile=export.tes4.dmp directory=export sqlfile=s1.sql
Processing object type TABLE_EXPORT/TABLE/TABLE
Job “HR”.”SYS_SQL_FILE_TABLE_01″ successfully completed at 15:08:17

Here is the content of s1.sql.

— CONNECT HR
— new object type path is: TABLE_EXPORT/TABLE/TABLE
CREATE TABLE “HR”.”TES4″
(    “C1” NUMBER
) 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 KEEP)
TABLESPACE “USERS” ;

Now take an import again but use transform attribute this time.

[oracle@host1 exp]$ impdp hr/hr tables=tes4 dumpfile=export.tes4.dmp directory=export sqlfile=s1.sql transform=storage:n;

Import: Release 10.2.0.1.0 – Production on Monday, 11 March, 2013 15:11:08

Copyright (c) 2003, 2005, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Production
With the Partitioning, OLAP and Data Mining options
Master table “HR”.”SYS_SQL_FILE_TABLE_01″ successfully loaded/unloaded
Starting “HR”.”SYS_SQL_FILE_TABLE_01″:  hr/******** tables=tes4 dumpfile=export.tes4.dmp directory=export sqlfile=s1.sql transform=storage:n
Processing object type TABLE_EXPORT/TABLE/TABLE
Job “HR”.”SYS_SQL_FILE_TABLE_01″ successfully completed at 15:11:09

[oracle@host1 exp]$ cat s1.sql
— CONNECT HR
— new object type path is: TABLE_EXPORT/TABLE/TABLE
CREATE TABLE “HR”.”TES4″
(    “C1” NUMBER
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
TABLESPACE “USERS” ;

Here table will be imported using default storage attribute for buffer pool.

Lets take another example using pctspace attributes.

SQL>create tablespace test datafile ‘/u01/app/oracle/oradata/orcl/test01.dbf’ size 10m

Tablespace created.

[oracle@host1 exp]$ expdp system/sys  full=y dumpfile=export:test.dmp  parfile=par.txt

Here is the content of par.txt

include=tablespace:”in’TEST'”

[oracle@host1 ~]$ impdp system/sys  dumpfile=export:test.dmp  parfile=par.txt sqlfile=export:ts.sql

Import: Release 10.2.0.1.0 – Production on Monday, 11 March, 2013 15:17:01

Copyright (c) 2003, 2005, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Production
With the Partitioning, OLAP and Data Mining options
Master table “SYSTEM”.”SYS_SQL_FILE_FULL_01″ successfully loaded/unloaded
Starting “SYSTEM”.”SYS_SQL_FILE_FULL_01″:  system/******** dumpfile=export:test.dmp parfile=par.txt sqlfile=export:ts.sql
Processing object type DATABASE_EXPORT/TABLESPACE
Job “SYSTEM”.”SYS_SQL_FILE_FULL_01″ successfully completed at 15:17:03

[oracle@host1 exp]$ cat ts.sql

— CONNECT SYSTEM
— new object type path is: DATABASE_EXPORT/TABLESPACE
CREATE TABLESPACE “TEST” DATAFILE
‘/u01/app/oracle/oradata/orcl/test01.dbf’ SIZE 10485760
LOGGING ONLINE PERMANENT BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO;

Now take the import again, but using transform attribute this time.

[oracle@host1 exp]$ impdp system/sys  dumpfile=export:test.dmp  parfile=/home/oracle/par.txt sqlfile=export:ts.sqltransform=pctspace:10

Import: Release 10.2.0.1.0 – Production on Monday, 11 March, 2013 15:18:23

Copyright (c) 2003, 2005, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Production
With the Partitioning, OLAP and Data Mining options
Master table “SYSTEM”.”SYS_SQL_FILE_FULL_01″ successfully loaded/unloaded
Starting “SYSTEM”.”SYS_SQL_FILE_FULL_01″:  system/******** dumpfile=export:test.dmp parfile=/home/oracle/par.txt sqlfile=export:ts.sql transform=pctspace:10
Processing object type DATABASE_EXPORT/TABLESPACE
Job “SYSTEM”.”SYS_SQL_FILE_FULL_01″ successfully completed at 15:18:24

[oracle@host1 exp]$ cat ts.sql
— CONNECT SYSTEM
— new object type path is: DATABASE_EXPORT/TABLESPACE
CREATE TABLESPACE “TEST” DATAFILE
‘/u01/app/oracle/oradata/orcl/test01.dbf’ SIZE 1048576
LOGGING ONLINE PERMANENT BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO;

Have a look at the bold text . Here we have changed datafile size to 10% of original datafile size.

Flashback database

prerequisite :On the Flashback in Database

SQL> alter database flashback on;

Database altered.

SQL> select log_mode,flashback_on from v$database;

LOG_MODE     FLASHBACK_ON

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

ARCHIVELOG   YES

Note:To off the flashback use the below statement.

SQL> alter database flashback off;

Database altered.

SQL> select log_mode,flashback_on from v$database;

LOG_MODE     FLASHBACK_ON

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

ARCHIVELOG   NO



Example 1: Let us consider one eg.of restoring a table to a particular scn(system change number):

Below i'm creating one 'test' user with some test data,than I will check the scn after committing the changes,than I will delete the data and finally I will restore that table usingflashback scn technique.

SQL> create user test identified by test;

User created.

SQL> grant connect,resource to test;

Grant succeeded


 SQL> insert into test.flashback_test values(1);

    1 row created.

    SQL> insert into test.flashback_test values(2);

    1 row created.

    SQL> insert into test.flashback_test values(3);

    1 row created.

    SQL> commit;

    SQL> select current_scn, scn_to_timestamp(current_scn) from v$database;

CURRENT_SCN SCN_TO_TIMESTAMP(CURRENT_SCN)
----------- ---------------------------------------------------------------------------
     931892 28-JUL-12 09.49.40.000000000 AM


    SQL> delete from test.flashback_test;

    3 rows deleted.

    SQL> commit;

    Commit complete.

    SQL> select * from test.flashback_test;

    no rows selected


Restore of table flashback_test using flashback database:
=====================================================

 step 1:Shut down the instances of Database

SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down

Step 2:Bring the Database in mount state
SQL> startup mount
ORACLE instance started.

Total System Global Area 1071333376 bytes
Fixed Size                  1341312 bytes
Variable Size             620759168 bytes
Database Buffers          444596224 bytes
Redo Buffers                4636672 bytes
Database mounted.

Step 3:Use flashback database command to go to scn when table was dropped.

If  you want to revert the database to SCN=930717 where flashback_test table has 3 rows

SQL> flashback database to scn 930717;

Flashback complete.

    SQL> alter database open resetlogs;
--set log sequence no. and the database will be synchonized.

Verify the Data:

    SQL> select * from flashback_test;

    COL1
    ———-
    1
    2
    3

Example 2:Flashback to timestamp

Lets say you have to drop some table on a particular day and the user know the time when he had dropped that table,than we can use the below steps to recover that table using flashback technique.

flashshback to timestamp:
======================

step 1:Shut down the instances of Database

SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down

Step 2:Bring the Database in mount state
SQL> startup mount
ORACLE instance started.

Total System Global Area 1071333376 bytes
Fixed Size                  1341312 bytes
Variable Size             620759168 bytes
Database Buffers          444596224 bytes
Redo Buffers                4636672 bytes
Database mounted.

step 3: flashback to timestamp 28-JUL-12 09.49.40

SQL>flashback database to timestamp TO_TIMESTAMP('2012-07-28 09:49:40','YYYY-MM-DD HH24:MI:SS');

    Flashback complete.

    SQL> select * from test.flashback_test;

    COL1
    ———-
    1
    2
    3

Example 3: Enable recyclebin and get back the lost data.

Step 1:Enable recycle bin when the Database is in open state.

SQL>alter session enable recyclebin=on;

Step 2:Recover lost table using below command.
  
    SQL> FLASHBACK TABLE test.flashback_test TO BEFORE DROP;

    Flashback complete.

    SQL> select * from test.flashback_test;

    COL1
    ———-
    1
    2
    3

Example 4:Create guaranteed restore point and restore that restore point:
========


How we can restore the entire data which is lost in a particular period,this can be use to test data also.
 This is very vital feature of Oracle flashback.

Create Restore point:
=====================

Step 1:Create restore point 'test_rest1':

    SQL> create restore point test_rest1 guarantee flashback database;

    Restore point created.

    * To view the restore points:

    SQL> select NAME,SCN,TIME,GUARANTEE_FLASHBACK_DATABASE, STORAGE_SIZE
         from V$RESTORE_POINT
         where GUARANTEE_FLASHBACK_DATABASE='YES';


SQL> select NAME, SCN, TIME, GUARANTEE_FLASHBACK_DATABASE, STORAGE_SIZE
         from V$RESTORE_POINT
         where GUARANTEE_FLASHBACK_DATABASE='YES';  2    3

NAME
--------------------------------------------------------------------------------
       SCN
----------
TIME                                                                        GUA
--------------------------------------------------------------------------- ---
STORAGE_SIZE
------------
TEST_REST1
    930537
28-JUL-12 08.57.51.000000000 AM                                             YES
           0


NAME
--------------------------------------------------------------------------------
       SCN
----------
TIME                                                                        GUA
--------------------------------------------------------------------------- ---
STORAGE_SIZE
------------
TEST_REST2
    930660
28-JUL-12 09.02.54.000000000 AM                                             YES
    30203904


NAME
--------------------------------------------------------------------------------
       SCN
----------
TIME                                                                        GUA
--------------------------------------------------------------------------- ---
STORAGE_SIZE
------------
TEST1
    932549
28-JUL-12 10.30.01.000000000 AM                                             YES
     8192000

Restore Restore point:
=======================

step 1:shut the database

SQL> shut immediate
ORA-01031: insufficient privileges
SQL> conn /as sysdba
Connected.
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down

step 2:Startup in mount state

SQL> startup mount
ORACLE instance started.

Total System Global Area 1071333376 bytes
Fixed Size                  1341312 bytes
Variable Size             620759168 bytes
Database Buffers          444596224 bytes
Redo Buffers                4636672 bytes
Database mounted.


step 3:flashback Database to restore point 'test1':

SQL> FLASHBACK database TO RESTORE POINT test1;

Flashback complete

Note:To restore a table below command can be used.
FLASHBACK TABLE emp TO RESTORE POINT test1;


step 4:Open the Database with resetlogs

SQL> alter database open resetlogs;

Database altered

step 6:Verify the data

SQL> select *from test.flashback_test;

      COL1
----------
         1
         2
         3

Step 7:Monitor flashback  v$flashback_database_log

    SQL> select oldest_flashback_scn from v$flashback_database_log;

OLDEST_FLASHBACK_SCN
--------------------
              930314

Note: Dropping restore point.

SQL> DROP RESTORE POINT TEST_REST1;

Restore point dropped.

Note:
Possible flashback options available are:

SQL>flashback table test.flashback_test to (SYSDATE-1/24);

-- be sure to substitute your SCN
FLASHBACK DATABASE TO SCN 19513917;
or
FLASHBACK DATABASE TO RESTORE POINT bef_damage;

/*
FLASHBACK DATABASE TO TIMESTAMP (SYSDATE-1/24);

FLASHBACK DATABASE TO TIMESTAMP timestamp'2002-11-05 14:00:00';

FLASHBACK DATABASE
TO TIMESTAMP to_timestamp('2002-11-11 16:00:00','YYYY-MM-DD HH24:MI:SS');