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.