Click here to Skip to main content
15,867,488 members
Articles / Programming Languages / SQL
Article

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

Rate me:
Please Sign up or sign in to vote.
4.75/5 (3 votes)
13 May 2011CPOL2 min read 33.7K   5   4
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

  1. Run the first export with parameter ROWS=N and parameter CONSTRAINTS=Y.
  2. 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)"
  3. Run the second export with ROWS=Y and CONSTRAINTS=N.
  4. Export all filtered tables without constraints and all rows are included within the date range for the filtered tables.

  5. Run the import for the first dump created in step 1 to build the tables with the constraints.
  6. Disable all the foreign key constraints using ALTER TABLE DISABLE CONSTRAINT.
  7. 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;
  8. Run the import for the second dump file created in step 2 that imports data without building constraints.
  9. Create an exception table “Exception”.
  10. 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));
  11. Enable all foreign key constraints.
  12. 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;
  13. Test the exception table.
  14. Test the exception table - Select row_id from Exceptions where owner=’USER_NAME’ and table_name='SOME_TABLE_NAME'.

  15. Delete the rows from the respective table whose parent keys are not found.
  16. 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');
  17. Apply the constraints again.
  18. 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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Technical Lead Trianz
India India
http://www.trianz.com

Comments and Discussions

 
QuestionThanks for the article, got a question Pin
VC Sekhar Parepalli2-Oct-14 17:35
VC Sekhar Parepalli2-Oct-14 17:35 
Am trying to back up a huge production Oracle11g-DB (highly disorganized with lots of data in it) just with 100 rows from each of the tables so I can restore it in my Development machine. Can you recommend how to go about it?

Thanks

VC
Questionhelpppp Pin
sanchi1515159-May-13 19:49
sanchi1515159-May-13 19:49 
GeneralMy vote of 5 Pin
whomwhom20-Jun-11 4:18
whomwhom20-Jun-11 4:18 
GeneralRe: My vote of 5 Pin
J Chandra Sekhar Achary27-Jun-11 1:55
J Chandra Sekhar Achary27-Jun-11 1:55 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.