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.