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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s