Export/Import an Oracle database with date range without constraint issues

Introduction

While exporting/importing a huge Oracle database with a certain date range, we may come across dangling records. That means a child table that has missing parent keys. In this article, steps are given to ensure safe export/import without any database constraint issues.

For example:

• Parent table: pTable
• Child table: cTable (FK to pTable)

pTable has all the data for 1^st Jan to 31^st Jan. After some reconciliation, the child table gets the data for 29^th Jan, 30^th Jan, and 31^st Jan on 1^st February 2011.

When you want to import all data from 1^st February 2011 to 28^th February 2011, the cTable table will have all the parent table data, i.e., data entered from 29^th Jan to 31^st Jan and inserted on 1^st February 2011. But the parent table (pTable) won't have those data that are inserted before 1^st February 2011.

What if this scenario is present in multiple tables in a database?

Writing a simple date range query while exporting and than deleting missing data after import can solve these types of problem.

Given below are steps to ensure safe export/import without any database constraint issues. I hope this will be useful to somebody.

Steps

1. Run the first export with parameter ROWS=N and parameter CONSTRAINTS=Y.

Find all the tables which have parent child relationships. Export filtered tables with only constraints and schema. Do not include rows.

SQL

exp user/pwd@mydatabase parfile=FilteredRecordTables.par

In the par file, you can mention the tables with the date range query. Here is a sample par file:

SQL

buffer=2000000 
compress=YES
FILE=C:\Dumps\Data_Extraction\DB_FilteredRecordTables.dmp
LOG=C:\Dumps\Data_Extraction\DB_FilteredRecordTables.log
Statistics=NONE
triggers=N
tables=Table1
,Table2
,Table3
,...
,...
, Table 200
query= "WHERE DATETIME_STAMP >= (SYSDATE - 90)"

2. Run the second export with ROWS=Y and CONSTRAINTS=N.

Export all filtered tables without constraints and all rows are included within the date range for the filtered tables.

3. Run the import for the first dump created in step 1 to build the tables with the constraints.
4. Disable all the foreign key constraints using ALTER TABLE DISABLE CONSTRAINT.
SQL

--this will disable all foreign key constraints
BEGIN
for i in (select constraint_name, table_name 
          from user_constraints where constraint_type ='R'
and status = 'ENABLED') 
LOOP
execute immediate 'alter table '||i.table_name||' 
        disable constraint '||i.constraint_name||'';
END LOOP;
END;

5. Run the import for the second dump file created in step 2 that imports data without building constraints.
6. Create an exception table “Exception”.

Create an exception table “Exception” using the SQL statement below, or get it from @?/rdbms/admin/UTLEXCPT.SQL.

SQL

create table exceptions(row_id rowid,
owner varchar2(30),
table_name varchar2(30),
constraint varchar2(30));

7. Enable all foreign key constraints.

Enable all foreign key constraints using ALTER TABLE ENABLE CONSTRAINT. Add all the errors into the exception table.

SQL

--this will enable them again
BEGIN
for i in (select constraint_name, table_name from 
          user_constraints where constraint_type ='R'
and status = 'ENABLED') 
LOOP
execute immediate 'alter table '||i.table_name||' enable constraint 
    '||i.constraint_name|| 'EXCEPTIONS INTO EXCEPTIONS' '';
END LOOP;
END;

8. Test the exception table.

Test the exception table - Select row_id from Exceptions where owner=’USER_NAME’ and table_name='SOME_TABLE_NAME'.

9. Delete the rows from the respective table whose parent keys are not found.
SQL

--this will delete all rows from table with parent keys not found
BEGIN
for i in (select row_id , table_name from EXCEPTIONS) 
LOOP
execute immediate 'delete '||i.table_name||'where rowid='||i.row_id;
END LOOP;
END;

Sample testing:

SQL

delete 'TABLE1' where rowid in (select row_id 
   from exceptions where owner=user and table_name=''TABLE1');

10. Apply the constraints again.
SQL

--this will enable them again
BEGIN
for i in (select constraint_name, table_name 
          from user_constraints where constraint_type ='R'
and status = 'ENABLED') 
LOOP
execute immediate 'alter table '||i.table_name||' enable constraint 
     '||i.constraint_name|| 'EXCEPTIONS INTO EXCEPTIONS' '';
END LOOP;
END;

Export all the filtered tables without constraints and all the rows that are included within the date range for the filtered tables.

Conclusion

From Oracle 10g, users can choose between using the old imp/exp utilities, or the newly introduced data pump utilities, called expdp and impdp. These new utilities introduce much needed performance improvements, network based exports and imports, etc.