4 Replies Latest reply: Dec 10, 2012 4:31 AM by Sahar RSS

    Large data copy

      Hi guys,

      What would be the most easiest/effecient way in terms of time,space,n/w concern while doing a 1TB of data copy (from single table growing constantly due to batch jobs running over) from one oracle DB to other (running on Linux) ?

      -- Export/Import
      -- copy via DB link
      -- ???
      -- ???

      Awaiting response,

        • 1. Re: Large data copy
          what is the total DB size ? is is much larger than 1T ? (meaning much larger than the table to copy) -
          if no, and the DB in archive mode,
          you might consider using RMAN ?

          does this table on its own tablespace ? - if so , what about transportable tablespace ?

          does the two DB (source and target) on same location (lan) ? or on remote sites (wan) ?

          • 2. Re: Large data copy
            Salman Qureshi
            Fastest way will be using datapump export/import (expdp/impdp). using this method, you have following options.
            Do tablespace export using transportable tablespace option and plug in this tablespace into the other database holding this large table. This should be the fastest way.
            Do export (wil parallel option), move dump file to the other server and do import (with parallel option).
            If you have less space for dump file, use compression=all to have a reduced size of dump file during export(this will increase export/import time a bit and will not fastes way).
            Use dblink to directly import from the current production which is holding this 1TB table. This will be a bit slower but will not require any space for the dump file on the file system.

            • 3. Re: Large data copy

              DB size is 5TB and unfortunetly DB is not in archival mode.
              And table not belongs to single tablespace.
              But both source and target in same network..

              • 4. Re: Large data copy
                so i guess that leaves you with
                either expdp or db link .

                as previous said, if you have the disk space to store the temporary expdp files, I would go with that option, doing it in parallel slaves depending what is the hardware you have (cpu especially).