Magic with Data Pump

Now is the time to play with Data Pump. Firstly, we will set up the environment, and then we will start playing with it.

Preparing Data Pump

$ sqlplus / as sysdba
SQL> ALTER SESSION SET CONTAINER=pdborcl;
SQL> CREATE USER fcomunoz IDENTIFIED BY alvarez DEFAULT TABLESPACE users QUOTA UNLIMITED ON users;
SQL> GRANT CREATE SESSION, RESOURCE, DATAPUMP_EXP_FULL_DATABASE, DATAPUMP_IMP_FULL_DATABASE TO fcomunoz;
SQL> CREATE DIRECTORY datapump AS '/data/pdborcl/backups;
SQL> GRANT READ, WRITE ON DIRECTORY datapump to fcomunoz;

Data masking

Have a look at the following command:

SQL> CREATE TABLE fcomunoz.EMPLOYEE  
( EMP_ID   NUMBER(10) NOT NULL,
  EMP_NAME VARCHAR2(30),
  EMP_SSN  VARCHAR2(9),
  EMP_DOB  DATE
)
/

SQL> INSERT INTO fcomunoz.employee VALUES (101,'Francisco Munoz',123456789,'30-JUN-73');

SQL> INSERT INTO fcomunoz.employee VALUES (102,'Gonzalo Munoz',234567890,'02-OCT-96');

SQL> INSERT INTO fcomunoz.employee VALUES (103,'Evelyn Aghemio',659812831,'02-OCT-79');

SQL> CREATE OR REPLACE PACKAGE fcomunoz.pkg_masking 
 as 
 FUNCTION mask_ssn (p_in VARCHAR2) RETURN VARCHAR2;
END;
/

SQL> CREATE OR REPLACE PACKAGE BODY fcomunoz.pkg_masking 
 2   AS 
 3     FUNCTION mask_ssn (p_in varchar2) 
 4     RETURN VARCHAR2
 5     IS 
 6   BEGIN 
 7     RETURN LPAD (
 8     ROUND(DBMS_RANDOM.VALUE (001000000,999999999)),9,0);
 9   END;
 10  END;
 11  /

SQL> SELECT * FOM fcomunozemployees;

$expdp fcomunoz/alvarez@pdborcl tables=fcomunoz.employee dumpfile=mask_ssn.dmp directory=datapump remap_data=fcomunoz.employee.emp_ssn:pkg_masking.mask_ssn

$ impdp fcomunoz/alvarez@pdborcl table_exists_action=truncate directory=datapump dumpfile=mask_ssn.dmp

SQL> SELECT * FROM fcomunoz.employees;

Metadata repository

Have a look at the following command:

$ expdp fcomunoz/alvarez@pdborcl content=metadata_only full=y directory=datapump dumpfile=metadata_06192013.dmp

$ impdp fcomunoz/alvarez@pdborcl directory=datapump dumpfile= metadata_06192013.dmp sqlfile=metadata_06192013.sql

$ expdp fcomunoz/alvarez@pdborcl content=metadata_only tables=fcomunoz.employee directory=datapump dumpfile= refresh_of_table_employee_06192013.dmp

$ impdp fcomunoz/alvarez@pdborcl table_exists_action=replace directory=datapump dumpfile= refresh_of_table_name_06192013.dmp

Cloning a user

Have a look at the following command:

$ expdp fcomunoz/alvarez@pdborcl schemas=fcomunoz content=metadata_only directory=datapump dumpfile= fcomunoz_06192013.dmp

SQL> CREATE USER fcomunoz2 IDENTIFIED BY alvarez DEFAULT TABLESPACE users QUOTA UNLIMITED ON users;
SQL> GRANT connect,resource TO fcomunoz2;

$ impdp fcomunoz/alvarez@pdborcl remap_schema=fcomunoz:fcomunoz2 directory=datapump dumpfile= fcomunoz_06192013.dmp

And, your new user fcomunoz2 is now created like your existing user fcomunoz that easily!

Creating smaller copies of production

Let us see how this cab be done for metadata-only and metadata and data:

• Metadata only:

  Have a look at the following command:

  $ expdp fcomunoz/alvarez@pdborcl content=metadata_only tables=fcomunoz.employee directory=datapump dumpfile=example_206192013.dmp
  
  $ impdp fcomunoz/alvarez@pdborcl content=metadata_only directory=datapump dumpfile=example_206192013.dmp sqlfile=employee_06192013.sql
  
  $ cat /data/pdborcl/backups/employee_06192013.sql
  
  $ impdp fcomunoz/alvarez@pdborcl transform=pctspace:70 content=metadata_only directory=datapump dumpfile= example_206192013.dmp sqlfile=transform_06192013.sql
  
  $ cat /data/pdborcl/backups/transform_06192013.sql
  

• Metadata and data:

  Have a look at the following command:

  $ expdp fcomunoz/alvarez@pdborcl sample=70 full=y directory=datapump dumpfile=expdp_70_06192013.dmp
  
  $ impdp fcomunoz/alvarez@pdborcl2 transform=pctspace:70 directory=datapump dumpfile=expdp_70_06192013.dmp
  

Creating your database in a different structure

Have a look at the following command:

$ expdp fcomunoz/alvarez@pdborcl full=y directory=datapump dumpfile=expdp_full_06192013.dmp

$ impdp fcomunoz/alvarez@pdborcl directory=datapump dumpfile= expdp_full_06192013.dmp remap_datafile='/u01/app/oracle/oradata/pdborcl/datafile_01.dbf':'/u01/app/oracle/oradata/pdborcl2/datafile_01.dbf'

Time-based flashback

Have a look at the following command:

SQL> conn / as sysdba

SQL> SELECT dbms_flashback.get_system_change_number
 2   FROM dual;

SQL> SELECT SCN_TO_TIMESTAMP(dbms_flashback.get_system_change_number)
 2   FROM dual;

SQL> exit

$ expdp fcomunoz/alvarez@pdborcl directory=datapump tables=fcomunoz.employee dumpfile=employee_flashback_06192013.dmp
flashback_time="to_timestamp('19-06-2013 14:30:00', 'dd-mm-yyyy hh24:mi:ss')"

$ expdp fcomunoz/alvarez@pdborcl directory=datapump tables=fcomunoz.employee dumpfile=employee_flashback_06192013.dmp
flashback_scn=123 

Note

In the preceding example with FLASHBACK_SCN, please replace 123 with a valid SCN from your database, where you are running this scenario.