This discussion is archived
6 Replies Latest reply: Dec 17, 2012 7:08 AM by khallas301 RSS

Export Import single table...

khallas301 Newbie
Currently Being Moderated
Gurus...

I am working on this single table which needs to be exported from prod and import into test.

As I understand I need to follow below steps:
1. Test - export table abc dump as backup
2. Prod - Export single table abc
3. Test - Drop table abc cascade constraints
4. Test - Import abc into test

Export par file:
Directory= dbpump
dumpfile= expdp_abc.dmp
logfile= expdp_abc.log
content= all
tables=user.abc
exclude=statistics,object_grant, tablespace_quota
flashback_time= systimestamp

Import par file:
Directory= dbpump
dumpfile= expdp_abc.dmp
logfile= impdp_abc.log
content= all
tables=user.abc
table_exist_action=replace
transform=segment_attributes:N

my doubts:
1. Is my process flow correct?
2. Export & Import file correct? or missing parameters?
3. What happens to all objects connected to the table, will that also imported?
4. Do I need to lock user during this process?
5. Any script to check whether all objects connected to table does exist in test after import?
  • 1. Re: Export Import single table...
    damorgan Oracle ACE Director
    Currently Being Moderated
    I wouldn't do it that way but you can if you wish.

    When you drop a table you drop permissions, constraints, indexes, triggers, invalidate views, etc. Maybe that doesn't matter in your case and maybe it does.

    What I would do is:

    1. CREATE TABLE data_hold AS SELECT * FROM target table in test.
    2. TRUNCATE the target table in test.
    3. Do an import using APPEND.
    4. If all looks good drop the data_hold table.
  • 2. Re: Export Import single table...
    XBOX Newbie
    Currently Being Moderated
    Hi,

    Process for table export & import.


    +1. Create database directory in test as well as production:-+
    --> create or replace directory directory_name as 'physical_path';

    --> grant acsess on that directory to user.

    +2. Backp table in test environment (in case if you need old data in your test env):-+

    --> Create table BKP_table_name as select * from table_name; (table_name u want to import)

    +3. Take Export backup in Production database:-+

    --> expdp dumpfile=file_name.dmp logfile=file_name.log directory=directory_name tables=Owner.table_name

    +4. On test server do the following actvity:-+

    a. Just check for dependencies on that table using DBA_DEPENDENCIES.

    b. Truncate table which u want to import

    c. import table-

    impdp dumpfile=file_name.dmp tables=owner.table_name logfile=file_name_imp.log directory=directory_name table_exists_action=APPEND

    d. Just check for dependencies on that table using DBA_DEPENDENCIES.

    And also you can use table_exists_action=replace
    This will also import all depedent objects to the table..

    Regards,

    Edited by: XBOX on Dec 14, 2012 10:15 PM
  • 3. Re: Export Import single table...
    khallas301 Newbie
    Currently Being Moderated
    thank you damorgan and xbox... I will test it out will let you know the result and mark as answered..
  • 4. Re: Export Import single table...
    khallas301 Newbie
    Currently Being Moderated
    Thanks guys for all useful tips.. table copy task completed...

    steps followed:
    1. test - create table backup
    2. Prod - Export table
    3. Test - Drop table (decided to have a clean copy.. just my thought)
    4. Test - Import table

    Export par file:
    directory = datapump
    dumpfile = expdp.abc.dmp
    logfile = user.abc.log
    content = all
    tables = user.abc
    exclude = statistics, object_grant
    flashback_time = systimestamp

    Import par file:
    directory = datapump
    dumpfile = expdp_abc.dmp
    logfile = test_user_abc.log
    content = all
    transform=segment_attributes:N
  • 5. Re: Export Import single table...
    XBOX Newbie
    Currently Being Moderated
    table_exists_action=replace does the same thing. Drop & Recreate...

    Regards,
  • 6. Re: Export Import single table...
    khallas301 Newbie
    Currently Being Moderated
    Ohkie.. thanks will try to use that in future for sure.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points