CAN WE TAKE A CONSISTENT BACKUP OF TABLE USING EXPDP AFTER DELETING RECORDS IN THE TABLE?
Answer for the topic is yes!
For demonstration, i create a table, insert some records and commit the transaction
SQL> create table t(name varchar(10),num number(10));
Table created.
SQL> insert into t values('xyz',1);
1 row created.
SQL> insert into t values('abc',2);
1 row created.
SQL> commit;
Commit complete.
Note down the scn for backup purpose and delete records from the table
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
9195869
Now i delete the records after the SCN before delete is noted
SQL> delete from t;
2 rows deleted.
SQL> commit;
Commit complete.
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
9196472
SQL> exit
Now using the flashback_scn parameter, the data can be pulled using the noted SCN.
The amount of data that can be retained also depends on the undo retention
[oracle@dpprod ~]$ expdp \'/ as sysdba\' directory=dpexp tables=TEST.T dumpfile=fb.dmp flashback_scn=9195869 nologfile=Y reuse_dumpfiles=Y
Export: Release 12.1.0.2.0 - Production on Tue Jan 4 20:42:26 2022
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Starting "SYS"."SYS_EXPORT_TABLE_01": "/******** AS SYSDBA" directory=dpexp tables=TEST.T dumpfile=fb.dmp flashback_scn=9195869 nologfile=Y reuse_dumpfiles=Y
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
. . exported "TEST"."T" 5.531 KB 2 rows
Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TABLE_01 is:
/home/oracle/fb.dmp
Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at Tue Jan 4 20:42:38 2022 elapsed 0 00:00:12