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






4.75/5 (3 votes)
Useful tips for Oracle import/export to address 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 1st Jan to 31st Jan. After some reconciliation, the child table gets the data for 29th Jan, 30th Jan, and 31st Jan on 1st February 2011.
When you want to import all data from 1st February 2011 to 28th February 2011, the cTable table will have all the parent table data, i.e., data entered from 29th Jan to 31st Jan and inserted on 1st February 2011. But the parent table (pTable) won't have those data that are inserted before 1st 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
- Run the first export with parameter
ROWS=N
and parameterCONSTRAINTS=Y
. - Run the second export with
ROWS=Y
andCONSTRAINTS=N
. - Run the import for the first dump created in step 1 to build the tables with the constraints.
- Disable all the foreign key constraints using
ALTER TABLE DISABLE CONSTRAINT
. - Run the import for the second dump file created in step 2 that imports data without building constraints.
- Create an exception table “Exception”.
- Enable all foreign key constraints.
- Test the exception table.
- Delete the rows from the respective table whose parent keys are not found.
- Apply the constraints again.
Find all the tables which have parent child relationships. Export filtered tables with only constraints and schema. Do not include rows.
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:
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)"
Export all filtered tables without constraints and all rows are included within the date range for the filtered tables.
--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;
Create an exception table “Exception” using the SQL statement below, or get it from @?/rdbms/admin/UTLEXCPT.SQL.
create table exceptions(row_id rowid,
owner varchar2(30),
table_name varchar2(30),
constraint varchar2(30));
Enable all foreign key constraints using ALTER TABLE ENABLE CONSTRAINT
. Add all the errors into the exception table.
--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;
Test the exception table - Select row_id from Exceptions where owner=’USER_NAME’ and table_name='SOME_TABLE_NAME'
.
--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:
delete 'TABLE1' where rowid in (select row_id
from exceptions where owner=user and table_name=''TABLE1');
--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.