How the Oracle DBA can use datapump in consistency mode with “flashback_time” :
– Create the export directory
– Verify the grantees on the export directory
– Execute the datapupmp export with expdp in consistency mode
– Execute an example of datapump import

 
-- Create the datapump directory
SQL> create directory export as '/oracle/export/SID/’;


-- Verify the grantees on the datapump directory
SQL> SELECT dbms_metadata.get_dependent_ddl('OBJECT_GRANT', 'EXPORT', 'SYS') from dual;

-- You should have a result like this as :
-- DBMS_METADATA.GET_DEPENDENT_DDL('OBJECT_GRANT','EXPORT','SYS')
-- --------------------------------------------------------------------------------
--
--  GRANT READ, WRITE ON DIRECTORY "EXPORT" TO "EXP_FULL_DATABASE"


-- Execute the export datapump with expdp in consistency mode:
$ expdp \'/ as sysdba\' dumpfile=exp_nomBase_02052011_0956.dmp logfile=exp_nomBase_02052011_0956.log full=y flashback_time=\"TO_TIMESTAMP\(TO_CHAR\(SYSDATE, \'YYYY-MM-DD HH24:MI:SS\'\),\'YYYY-MM-DD HH24:MI:SS\'\)\" directory=export 

-- Execute the import datapump of one schema. Remap User Scott to Vinc and 2 tablespaces to DATA:
$ impdp \'/ as sysdba\' dumpfile=exp_nomBase_02052011_0956.dmp logfile=imp_nomBase_02052011_0956.log REMAP_SCHEMA=Scott:Vinc SCHEMAS=scott REMAP_TABLESPACE=USER1:DATA  REMAP_TABLESPACE=USER2:DATA directory=export 

-- export a single schema
expdp \'/ as sysdba\' SCHEMAS=myschema directory=DATA_PUMP_DIR dumpfile=myschema.dmp logfile=myschema.log VERSION=LATEST

-- export a single table of a schema
expdp \'/ as sysdba\' tables=myschema.mytable directory=DATA_PUMP_DIR dumpfile=myschemamytable.dmp logfile=myschemamytable.log VERSION=LATEST

-- Import single table
impdp \'/ as sysdba\' tables=myschema.mytable directory=DATA_PUMP_DIR dumpfile=myschemamytable.dmp logfile=impmyschemamytable.log
-- If the table to import exists, use the option: TABLE_EXISTS_ACTION=[SKIP | APPEND | TRUNCATE | REPLACE]

— Optionally you can easily get DDL from dumpfile using the parameter sqlfile=My_file.sql

Sometimes, we may get a requirement to delete datapump jobs which are stopped abruptly due to some reason, you can purge data pump jobs.

Author: Vincent Fenoll – Oracle DBA

Compatibility: Oracle 10 to Oracle 18.