4 Replies Latest reply on May 4, 2011 10:44 AM by orawiss

    How to Create a copy of partition table?

    570355
      hi,


      I have a partition table that has 8 partition.

      I would like to create a copy of this partition table.
      I am use:
      create table copy_partition_table as select * from partition_table where 1=2;

      But when I create this table partitions are not created?

      How can do that?
      Could you please help me?

      Thanks and regards,
        • 1. Re: How to Create a copy of partition table?
          Dbb
          Use DBMS_METADATA.GET_DDL
          • 2. Re: How to Create a copy of partition table?
            Hemant K Chitale
            You have to define the Partitions in you CREATE TABLE. Else, the created table is NOT a partitioned table, it only copies the logical structure (column names, datatypes) from the source table.
            (This is similar to knowing that when you CREATE TABLE x AS SELECT * FROM y the indexes and constraints that are defined on 'y' are not copied to x unless they are explicitly specified )

            Hemant K Chitale
            • 3. Re: How to Create a copy of partition table?
              859237
              hi

              you can use DBMS_REDEFINITION reorganize tables
              or
              dbms exchange.

              thanks.
              • 4. Re: How to Create a copy of partition table?
                orawiss
                Example:
                SQL> create table t1
                (id number primary key,
                created_date date default sysdate
                )
                partition by range (created_date)
                (
                partition t201104 values less than (to_date('01-04-2011', 'DD-MM-YYYY')),
                partition t201105 values less than (to_date('01-05-2011', 'DD-MM-YYYY'))
                  9  )
                 10  ;
                
                Table created.
                
                SQL> insert into t1 values (1, to_date('01/04/2011','DD/mm/YYYY'));
                
                1 row created.
                
                SQL> insert into t1 values (2, to_date('01/03/2011','DD/mm/YYYY'));
                
                1 row created.
                
                SQL> commit;
                
                Commit complete.
                
                SQL> 
                
                create table t2
                partition by range (created_date)
                (
                partition t_201104 values less than (to_date('01-04-2011', 'DD-MM-YYYY')),
                partition t_201105 values less than (to_date('01-05-2011', 'DD-MM-YYYY'))
                )
                as
                  8  select * from t1;
                
                Table created.
                
                SQL> select * from t2;
                
                     ID CREATED_D
                ---------- ---------
                      2 01-MAR-11
                      1 01-APR-11
                
                SQL>