6 Replies Latest reply: Dec 17, 2012 9:08 AM by khallas301 RSS

    Export Import single table...

    khallas301
      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
          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
            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
              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
                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
                  table_exists_action=replace does the same thing. Drop & Recreate...

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