9 Replies Latest reply: Feb 20, 2013 11:00 PM by Step_Into_Oracle_DBA RSS

    Move data from Non Partitioned Table to Partitioned Table

    Arun Natarajan-OC
      Hi Friends,

      I am using Oracle 11.2.0.1 DB

      Please let me know how can i copy /move the data from Non -Partitioned Oracle table to the currently created Partiotioned table.

      Regards,
      DB
        • 1. Re: Move data from Non Partitioned Table to Partitioned Table
          rp0428
          >
          I am using Oracle 11.2.0.1 DB

          Please let me know how can i copy /move the data from Non -Partitioned Oracle table to the currently created Partiotioned table.
          >
          ALL of the data needs to be moved using INSERTS since every partition has its own segment.

          Since this is the obvious way to do it
          INSERT INTO partitionedTable SELECT * FROM nonPartitionedTable.
          Why do you think there is anything more to it than that?
          • 2. Re: Move data from Non Partitioned Table to Partitioned Table
            Chanchal Wankhade
            Hi,

            Take a table level export backup of the table.
            Create partition table.
            Import data into the new partition table.
            when successfully import, look at the indexes and the constraint of the old table and newly created table.
            If you are satisfy with the structure then rename the old unpartition table as table_name_bkp.
            Rename newly created partition table as a table you want.
            • 3. Re: Move data from Non Partitioned Table to Partitioned Table
              Arun Natarajan-OC
              Hi All,

              Created Partitioned table but unable to copy the data from Non Partitioned table:

              SQL> select * from sales;

              SNO YEAR NAME
              ---------- --------- ----------
              1 01-JAN-11 jan2011
              1 01-FEB-11 feb2011
              1 01-JAN-12 jan2012
              1 01-FEB-12 feb2012
              1 01-JAN-13 jan2013
              1 01-FEB-13 feb2013

              6 rows selected.

              >
              SQL>
              SQL> create table sales_part(sno number(3),year date,name varchar2(10))
              2 partition by range(year)
              3 (
              4 partition p11 values less than (TO_DATE('01/JAN/2012','DD/MON/YYYY')),
              5 partition p12 values less than (TO_DATE('01/JAN/2013','DD/MON/YYYY'))
              6 );

              Table created.

              SQL> SELECT table_name,partition_name, num_rows FROM user_tab_partitions;

              TABLE_NAME PARTITION_NAME NUM_ROWS
              ------------------------------ ------------------------------ ----------
              SALES_PART P11
              SALES_PART P12
              UNPAR_TABLE UNPAR_TABLE_12 776000
              UNPAR_TABLE UNPAR_TABLE_15 5000
              UNPAR_TABLE UNPAR_TABLE_MX 220000

              SQL>
              SQL> insert into sales_part select * from sales;
              insert into sales_part select * from sales
              *
              ERROR at line 1:
              ORA-14400: inserted partition key does not map to any partition


              Regards,
              DB
              • 4. Re: Move data from Non Partitioned Table to Partitioned Table
                Arun Natarajan-OC
                Hi Friends,

                i could load data after removing the unmatches values (2013) from sales table.

                So i would like to know how to create a partition without specifying the range so that the data which are not coming under the range of other partitions will automatically goes to this partition.

                Regards,
                DB
                • 5. Re: Move data from Non Partitioned Table to Partitioned Table
                  Step_Into_Oracle_DBA
                  Please let me know how can i copy /move the data from Non -Partitioned Oracle table to the currently created Partiotioned table.
                  Check the below document.
                  131983.1 (Moving Data from Non-Partitioned Tables to Partitioned Tables - Oracle 8.x)
                  Please keep forum clean by Marking your Post as Answered or Helpful if Your question is answered.

                  Thanks & Regards,

                  StepIntoOracleDBA (SID)
                  • 6. Re: Move data from Non Partitioned Table to Partitioned Table
                    Chanchal Wankhade
                    839396 wrote:
                    Hi All,

                    Created Partitioned table but unable to copy the data from Non Partitioned table:

                    SQL> select * from sales;

                    SNO YEAR NAME
                    ---------- --------- ----------
                    1 01-JAN-11 jan2011
                    1 01-FEB-11 feb2011
                    1 01-JAN-12 jan2012
                    1 01-FEB-12 feb2012
                    1 01-JAN-13 jan2013
                    1 01-FEB-13 feb2013

                    6 rows selected.
                    Because you have specified only two partition and value less than to_date('01-JAN-2013')....But in your output there is value which is more than 1st jan 2013.
                    So you need to specify one more partition and say value less than to_date('31-JAN-2013')... So you would not have any issue to insert data.

                    Please specify third partition as per your requirement Or you can specify maxvalue.
                    • 7. Re: Move data from Non Partitioned Table to Partitioned Table
                      rp0428
                      >
                      Created Partitioned table but unable to copy the data from Non Partitioned table:
                      >
                      Yep! You have to have a place for Oracle to put the data or it will tell you it can't.

                      Create a MAXVALUE partition.
                       partition ALL_OTHER_DATA values less than (MAXVALUE)
                      See the VLDB and Partitioning Guide
                      http://docs.oracle.com/cd/B28359_01/server.111/b32024/partition.htm
                      >
                      Range Partitioning
                      Range partitioning maps data to partitions based on ranges of values of the partitioning key that you establish for each partition. It is the most common type of partitioning and is often used with dates. For a table with a date column as the partitioning key, the January-2005 partition would contain rows with partitioning key values from 01-Jan-2005 to 31-Jan-2005.

                      Each partition has a VALUES LESS THAN clause, which specifies a non-inclusive upper bound for the partitions. Any values of the partitioning key equal to or higher than this literal are added to the next higher partition. All partitions, except the first, have an implicit lower bound specified by the VALUES LESS THAN clause of the previous partition.

                      A MAXVALUE literal can be defined for the highest partition. MAXVALUE represents a virtual infinite value that sorts higher than any other possible value for the partitioning key, including the NULL value.
                      >
                      Simple table with one partition
                      CREATE TABLE my_table_2 (
                        id           NUMBER,
                        description  VARCHAR2(50)
                      )
                      PARTITION BY RANGE (id)
                      (PARTITION my_table_part VALUES LESS THAN (MAXVALUE));
                      • 8. Re: Move data from Non Partitioned Table to Partitioned Table
                        sb92075
                        839396 wrote:
                        Hi All,

                        Created Partitioned table but unable to copy the data from Non Partitioned table:

                        SQL> select * from sales;

                        SNO YEAR NAME
                        ---------- --------- ----------
                        1 01-JAN-11 jan2011
                        1 01-FEB-11 feb2011
                        1 01-JAN-12 jan2012
                        1 01-FEB-12 feb2012
                        1 01-JAN-13 jan2013
                        1 01-FEB-13 feb2013
                        into which partition should row immediately above ("01-FEB-13") be deposited?
                        [oracle@localhost ~]$ oerr  ora 14400
                        14400, 00000, "inserted partition key does not map to any partition"
                        // *Cause:  An attempt was made to insert a record into, a Range or Composite
                        //          Range object, with a concatenated partition key that is beyond 
                        //          the concatenated partition bound list of the last partition -OR-
                        //          An attempt was made to insert a record into a List object with
                        //          a partition key that did not match the literal values specified
                        //          for any of the partitions. 
                        // *Action: Do not insert the key. Or, add a partition capable of accepting
                        //          the key, Or add values matching the key to a partition specification
                        >
                        6 rows selected.

                        >
                        SQL>
                        SQL> create table sales_part(sno number(3),year date,name varchar2(10))
                        2 partition by range(year)
                        3 (
                        4 partition p11 values less than (TO_DATE('01/JAN/2012','DD/MON/YYYY')),
                        5 partition p12 values less than (TO_DATE('01/JAN/2013','DD/MON/YYYY'))
                        6 );

                        Table created.

                        SQL> SELECT table_name,partition_name, num_rows FROM user_tab_partitions;

                        TABLE_NAME PARTITION_NAME NUM_ROWS
                        ------------------------------ ------------------------------ ----------
                        SALES_PART P11
                        SALES_PART P12
                        UNPAR_TABLE UNPAR_TABLE_12 776000
                        UNPAR_TABLE UNPAR_TABLE_15 5000
                        UNPAR_TABLE UNPAR_TABLE_MX 220000

                        SQL>
                        SQL> insert into sales_part select * from sales;
                        insert into sales_part select * from sales
                        *
                        ERROR at line 1:
                        ORA-14400: inserted partition key does not map to any partition


                        Regards,
                        DB
                        • 9. Re: Move data from Non Partitioned Table to Partitioned Table
                          Step_Into_Oracle_DBA
                          Use Like below

                          create table sales_part(sno number(3),year date,name varchar2(10))
                          partition by range(year)
                          (
                          partition p11 values less than (TO_DATE('01/JAN/2012','DD/MON/YYYY')),
                          partition p12 values less than (TO_DATE('01/JAN/2013','DD/MON/YYYY')),
                          partition p13 values less than (maxvalue)
                          );


                          Please keep forum clean by Marking your Post as Answered or Helpful if Your question is answered.

                          Thanks & Regards,

                          StepIntoOracleDBA (SID)