Now is the time to play with Data Pump. Firstly, we will set up the environment, and then we will start playing with it.
$ 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;
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;
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
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!
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
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'
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