3 Replies Latest reply: Feb 19, 2014 3:55 AM by oralicious RSS

    move partition to different database

    oralicious

      11.2.0.2.0

       

      Have a large application audit table I'm going to archive to a warehouse instance and just keep current days data in the source via A nightly job around 1am to move all data from previous day.

       

      The warehouse table will be partitioned and I want to compress the partitions using basic compression as we don't have advanced licence.  The DWH table will be queried only so ideal candidate for basic compression.

       

      If I partition the source by day range Is there a way to move the previous days partition  from the source table to a compressed partition on the remote over database link.

       

      If archived table was on the source instance, Id just do a alter move compress.  I could run an insert delete but interested to see whats possible with moving an uncompressed partitions to a compressed partition over a link.

        • 1. Re: move partition to different database
          rp0428

          If I partition the source by day range Is there a way to move the previous days partition  from the source table to a compressed partition on the remote over database link.

           

          If archived table was on the source instance, Id just do a alter move compress.  I could run an insert delete but interested to see whats possible with moving an uncompressed partitions to a compressed partition over a link.

          The simplest way to 'move' the data is to use Transportable Tablespaces.

           

          See the DBA doc

          http://docs.oracle.com/cd/B28359_01/server.111/b28310/tspaces013.htm

           

          1. Use a separate tablespace for each partition of your warehouse table

          2. Take the tablespace offline when you are ready to move it

          3. 'Transport' the tablespace to the other server - rename it to a 'working' name

          4. Do your 'alter move compress' on the other server if you need to and compress the data to the actual tablespace and name

           

          In order to compress the data you will HAVE to move it.

          • 2. Re: move partition to different database
            Hemant K Chitale

            You could use datapump export-import to copy the data of the "older" partitions and then truncate them from the source database.

             

            Hemant K Chitale

             


            • 3. Re: move partition to different database
              oralicious

              I know if a table or tablespace was in default compress mode and inserts are done direct insert then the rows are compressed. 

               

              My understanding is data is imported direct append with impdp so interested to see will it compress on the other side naturally due to the direct append.  I'll post back here after I try it.