Forum Stats

  • 3,813,901 Users
  • 2,258,789 Discussions
  • 7,892,437 Comments

Discussions

why are named constraints missing after data only refresh?

i have refreshed our dev database (PEMS) from production using a script that has always worked (12c and earlier) in our new 19c database (first time i have run it on 19c). the procedure is to disable constraints and import across the network link, then re-enable the constraints. However i have found that after the refresh, the only constraints that still exist on the target dev db are the type "C" or check constraints that have names that start with SYS_. I can use Toad to recreate the constraints of non type C in dev but i am wondering:

  1. why are the constraints missing after import?
  2. Is there a way to avoid this circumstance when refreshing data only? Here is my data only refresh shell script:


sqlplus -L / as sysdba @disable_pems_const.sql

sqlplus -L / as sysdba @disable_pems_triggers.sql

impdp / parfile=refresh_data_pems.par

sqlplus -L / as sysdba @enable_pems_const.sql

sqlplus -L / as sysdba @enable_pems_triggers.sql

sqlplus -L / as sysdba @drop_pems_seq.sql

impdp / parfile=dp_pems_sequences.par

Tagged:

Answers

  • EdStevens
    EdStevens Member Posts: 28,778 Gold Crown

    You show the parent script, but it only calls a bunch of other scripts. We have no idea what is in these files:

    disable_pems_const.sql

    disable_pems_triggers.sql

    refresh_data_pems.par

    enable_pems_const.sql

    enable_pems_triggers.sql

    drop_pems_seq.sql

    dp_pems_sequences.par


    You are asking us to debug things we cannot see.

  • FBP_FLUOR
    FBP_FLUOR Member Posts: 7 Red Ribbon

    i will post them here but what they are doing is disabling the constraints, and then importing the data across the network link. Here is the info:

    disable_pems_const.sql:

    set feedback off

    set heading off

    set termout off

    set linesize 1000

    set trimspool on

    set VERIFY off

    SPOOL disable_pems_const_run.sql

    select 'ALTER TABLE ' || OWNER ||'.'|| TABLE_NAME || ' MODIFY CONSTRAINT ' || CO

    NSTRAINT_NAME || ' DISABLE;' from DBA_CONSTRAINTS

    where OWNER = 'PEMS';


    spool off;

    @disable_pems_const_run.sql

    exit;

    disable_pems_triggers.sql


    set feedback off

    set heading off

    set termout off

    set linesize 1000

    set trimspool on

    set VERIFY off

    SPOOL disable_pems_trigger_run.sql

    select 'ALTER TRIGGER ' || OWNER ||'.'|| TRIGGER_NAME || ' DISABLE;' from DBA_TR

    IGGERS

    where OWNER = 'PEMS';

    spool off;

    @disable_pems_trigger_run.sql

    exit;

    refresh_data_pems.par


    SCHEMAS=PEMS

    LOGFILE=PEMS_Refresh_data

    DIRECTORY=EXPORT_DIR

    TABLE_EXISTS_ACTION=TRUNCATE

    CONTENT=DATA_ONLY

    NETWORK_LINK=REFRESH.WORLD

    then, the enable scripts are the same only using ENABLE in place of DISABLE. The sequences work fine. My question was kind of wondering if something had changed in 19c because this process worked perfectly in 12c. So i was hoping someone might have info on that rather than specifics of my scripts, which have always worked and we just tried them on the new version and had this unfortunate result of the non check constraints disappearing.