Use of System Change Number (SCN)

Use of System Change Number (SCN)

Ora2Pg is able to export data as of a specific SCN. You can set it at command line using the -S or --scn option. You can give a specific SCN or if you want to use the current SCN at first connection time set the value to 'current'. In this last case if connection user has the "SELECT ANY DICTIONARY" or the "SELECT_CATALOG_ROLE" role, the current SCN is looked up in the v$database view.

Example of use:

ora2pg -c ora2pg.conf -t COPY --scn 16605281

This adds the following clause to the query used to retrieve data for example:

AS OF SCN 16605281

You can also use the --scn option to use the Oracle flashback capability by specifying a timestamp expression instead of a SCN. For example:

ora2pg -c ora2pg.conf -t COPY --scn "TO_TIMESTAMP('2021-12-01 00:00:00', 'YYYY-MM-DD HH:MI:SS')"

This will add the following clause to the query used to retrieve data:

AS OF TIMESTAMP TO_TIMESTAMP('2021-12-01 00:00:00', 'YYYY-MM-DD HH:MI:SS')

or for example to only retrieve yesterday's data:

ora2pg -c ora2pg.conf -t COPY --scn "SYSDATE - 1"