Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, Markus.

Asked: July 11, 2016 - 7:22 am UTC

Last updated: July 11, 2016 - 4:17 pm UTC

Version: 12.1.0.2

Viewed 1000+ times

You Asked

Hi,

I found a procedure for a network based impdp. The procedure sets the actual (flashback) scn of the target system (see below).

That’s a mistake, isn’t it? What will impdp do with this invalid scn? Simply ignore it?

Cheers, Markus



procedure in target database:
...
...
    h1 := dbms_datapump.open (operation => 'IMPORT', job_mode => 'SCHEMA', remote_link => l_link_name, version => 'COMPATIBLE');
    dbms_datapump.metadata_filter(handle => h1, name => 'SCHEMA_EXPR', value => 'LIKE ('''||substr(upper(l_owner),1,3)||'%'')');
    dbms_datapump.metadata_filter(handle=> h1, name => 'EXCLUDE_PATH_EXPR', value => 'IN(''STATISTICS''
                                                                                        ,''DB_LINK''
                                                                                        ,''USER''
                                                                                        ,''PROCEDURE''
                                                                                        ,''FUNCTION''
                                                                                        ,''PACKAGE_BODY''
                                                                                        ,''PACKAGE''
                                                                                        ,''REF_CONSTRAINT'')');
    dbms_datapump.set_parameter(handle => h1, name => 'FLASHBACK_SCN', value => sys.dbms_flashback.get_system_change_number);              <<<<===== ????????
    dbms_datapump.set_parameter(handle => h1, name => 'TABLE_EXISTS_ACTION', value => 'TRUNCATE' );
    dbms_datapump.start_job(handle => h1);
    dbms_datapump.wait_for_job(handle => h1, job_state => v_job_state);
...
...


and Chris said...

Your suspicion is correct. The FLASHBACK_SCN parameter sets the SCN of the source database for network imports:

"If the NETWORK_LINK parameter is specified, then the SCN refers to the SCN of the source database."

http://docs.oracle.com/database/121/SUTIL/GUID-D408B112-1A81-4F68-BEFF-7403A9588DDB.htm#SUTIL849

The code snippet gets the SCN from the local database. If you're "lucky", this will map to a valid SCN in the source. But it probably won't export the data you expect!

If local SCN is not valid on the source, the import will fail with:

ORA-08181: specified number is not a valid system change number


Is this answer out of date? If it is, please let us know via a Comment

More to Explore

Data Pump

All of the database utilities including Data Pump are explained in the Utilities guide.