Partitioning a Non-partitioned table --oracle dba

                                               
we can partition a non-partitioned table in one of four ways:
A) Export/import method

B) Insert with a subquery method (CTS)

C) Partition exchange method

D) DBMS_REDEFINITION

Either of these four methods will create a partitioned table from an existing non-partitioned table.


A. Export/import method
 --------------------
1) Export your table:
 exp pp/pp tables=numbers file=exp.dmp


2) Drop the table:
drop table numbers;



3) Recreate the table with partitions:
 create table numbers (qty number(3), name varchar2(15))
 partition by range (qty)
(partition p1 values less than (501),
 partition p2 values less than (maxvalue));



4) Import the table with ignore=y:
imp pp/pp file=exp.dmp ignore=y

 The ignore=y causes the import to skip the table creation and
 continues to load all rows.

-----------------------------------------------------------------------------------------------------------------------
B. Insert with a subquery method(CTS)

1) Create a partitioned table:
 create table partbl (qty number(3), name varchar2(15))
 partition by range (qty)
 (partition p1 values less than (501),
 partition p2 values less than (maxvalue));



2) Insert into the partitioned table with a subquery from the
 non-partitioned table:
 insert into partbl (qty, name) select * from origtbl;


3) If you want the partitioned table to have the same name as the
 original table, then drop the original table and rename the
 new table:
drop table origtbl;
 alter table partbl rename to origtbl;
-------------------------------------------------------------------------------------------------------------------
C. Partition Exchange method

ALTER TABLE EXCHANGE PARTITION can be used to convert a partition (or
subpartition) into a non-partitioned table and a non-partitioned table into a
partition (or subpartition) of a partitioned table by exchanging their data
and index segments.


1) Create table dummy_t as select with the required partitions


2) Alter table EXCHANGE partition partition_name
 with table non-partition_table;


Example
-------


SQL> CREATE TABLE p_emp(sal NUMBER(7,2))
  PARTITION BY RANGE(sal)
 (partition emp_p1 VALUES LESS THAN (2000),
  partition emp_p2 VALUES LESS THAN (4000));



SQL> SELECT * FROM emp;
 EMPNO ENAME JOB MGR HIREDATE SAL
 --------- ---------- --------- --------- --------- ---------
 7369 SMITH CLERK 7902 17-DEC-80 800
 7499 ALLEN SALESMAN 7698 20-FEB-81 1600
 7521 WARD SALESMAN 7698 22-FEB-81 1250
 7566 JONES MANAGER 7839 02-APR-81 2975
 7654 MARTIN SALESMAN 7698 28-SEP-81 1250
 7698 BLAKE MANAGER 7839 01-MAY-81 2850
 7782 CLARK MANAGER 7839 09-JUN-81 2450
 7788 SCOTT ANALYST 7566 19-APR-87 3000
 7839 KING PRESIDENT 17-NOV-81 5000
 7844 TURNER SALESMAN 7698 08-SEP-81 1500
 7876 ADAMS CLERK 7788 23-MAY-87 1100
 7900 JAMES CLERK 7698 03-DEC-81 950
 7902 FORD ANALYST 7566 03-DEC-81 3000
 7934 MILLER CLERK 7782 23-JAN-82 1300
 14 rows selected.


SQL> CREATE TABLE dummy_y as SELECT sal FROM emp WHERE sal<2000;
 Table created.


SQL> CREATE TABLE dummy_z as SELECT sal FROM emp WHERE sal BETWEEN 2000 AND 3999;
 Table created.


SQL> alter table p_emp exchange partition emp_p1 with table dummy_y;
 Table altered.


SQL> alter table p_emp exchange partition emp_p2 with table dummy_z;
 Table altered.
 
                                      (OR)
                   FOR REFERENCE TABLE
ref table :- lookup          
unpartition table :- big_table  
duplicate table :- big_table2


CREATE TABLE lookup ( id NUMBER(10), description VARCHAR2(50));
ALTER TABLE lookup ADD (CONSTRAINT lookup_pk PRIMARY KEY (id));

INSERT INTO lookup (id, description) VALUES (1, 'ONE');
INSERT INTO lookup (id, description) VALUES (2, 'TWO');
INSERT INTO lookup (id, description) VALUES (3, 'THREE');
COMMIT;



-- Create and populate a larger table that we will later partition.
CREATE TABLE big_table (id NUMBER(10),created_date DATE,lookup_id NUMBER(10),data VARCHAR2(50));

DECLARE
  l_lookup_id    lookup.id%TYPE;
  l_create_date  DATE;
BEGIN
  FOR i IN 1 .. 1000000 LOOP
    IF MOD(i, 3) = 0 THEN
      l_create_date := ADD_MONTHS(SYSDATE, -24);
      l_lookup_id   := 2;
    ELSIF MOD(i, 2) = 0 THEN
      l_create_date := ADD_MONTHS(SYSDATE, -12);
      l_lookup_id   := 1;
    ELSE
      l_create_date := SYSDATE;
      l_lookup_id   := 3;
    END IF;
 
 
INSERT INTO big_table (id, created_date, lookup_id, data) VALUES (i, l_create_date, l_lookup_id, 'This is some data for ' || i);
  END LOOP;
  COMMIT;
END;
/



-- Apply some constraints to the table.
ALTER TABLE big_table ADD (CONSTRAINT big_table_pk PRIMARY KEY (id));
CREATE INDEX bita_created_date_i ON big_table(created_date);
CREATE INDEX bita_look_fk_i ON big_table(lookup_id);
ALTER TABLE big_table ADD (CONSTRAINT bita_look_fk FOREIGN KEY (lookup_id) REFERENCES lookup(id));


-- Gather statistics on the schema objects
EXEC DBMS_STATS.gather_table_stats(USER, 'LOOKUP', cascade => TRUE);
EXEC DBMS_STATS.gather_table_stats(USER, 'BIG_TABLE', cascade => TRUE);


Create a Partitioned Interim Table
Next we create a new table with the appropriate partition structure to act as an interim table.


-- Create partitioned table.
CREATE TABLE big_table2 (id NUMBER(10),created_date  DATE,lookup_id NUMBER(10),data VARCHAR2(50))
PARTITION BY RANGE (created_date)
(PARTITION big_table_2014 VALUES LESS THAN (TO_DATE('01/01/2014', 'DD/MM/YYYY')),
 PARTITION big_table_2015 VALUES LESS THAN (TO_DATE('01/01/2015', 'DD/MM/YYYY')),
 PARTITION big_table_2016 VALUES LESS THAN (MAXVALUE));


With this interim table in place we can start the online redefinition.Start the Redefinition Process
First we check the redefinition is possible using the following command.
EXEC DBMS_REDEFINITION.can_redef_table(USER, 'BIG_TABLE');


If no errors are reported it is safe to start the redefinition using the following command.
-- Alter parallelism to desired level for large tables.


--ALTER SESSION FORCE PARALLEL DML PARALLEL 8;
DML: DML statements are executed in parallel mode if a parallel hint or a parallel clause is specified



--ALTER SESSION FORCE PARALLEL QUERY PARALLEL 8;
QUERY: Queries are executed in parallel mode if a parallel hint or a parallel clause is specified
QUERY: Subsequent queries are executed with the default degree of parallelism, unless a degree is specified in this clause
FORCE DML and QUERY, the degree overrides the degree currently stored for the table in the data dictionary

ALTER SESSION FORCE PARALLEL DDL PARALLEL 8;
DDL statements are executed in parallel mode if a parallel clause is specified.
ensure my all queries will be running in parallel without making any modification in sql queries.
                 or

ALTER SESSION DISABLE PARALLEL QUERY;
ALTER TABLE ORDERS PARALLEL (DEGREE 4); for table execution
SELECT /*+ PARALLEL (ORDERS_RP, 4) */STATUS, COUNT(*) FROM ORDERS_RP GROUP BY STATUS; for select
INSERT /*+ PARALLEL (TEMP_ORDERS, 4) */INTO TEMP_ORDERS SELECT /*+ PARALLEL (ORDERS, 4) */ * FROM ORDERS; for insert


BEGIN
  DBMS_REDEFINITION.start_redef_table(
    uname      => USER,      
    orig_table => 'BIG_TABLE',
    int_table  => 'BIG_TABLE2');
END;
/


Depending on the size of the table, this operation can take quite some time to complete.
Create Constraints and Indexes (Dependencies)

If there is delay between the completion of the previous operation and moving on to finish the redefinition, it may be sensible to resynchronize the interim table before building any constraints and indexes. The resynchronization of the interim table is initiated using the following command.
-- Optionally synchronize new table with interim data before index creation


BEGIN
  dbms_redefinition.sync_interim_table(
    uname      => USER,      
    orig_table => 'BIG_TABLE',
    int_table  => 'BIG_TABLE2');
END;
/

The dependent objects will need to be created against the new table. This is done using the COPY_TABLE_DEPENDENTS procedure. You can decide which dependencies should be copied.

SET SERVEROUTPUT ON
DECLARE
  l_errors  NUMBER;
BEGIN
  DBMS_REDEFINITION.copy_table_dependents(
    uname            => USER,
    orig_table       => 'BIG_TABLE',
    int_table        => 'BIG_TABLE2',
    copy_indexes     => DBMS_REDEFINITION.cons_orig_params,
    copy_triggers    => TRUE,
    copy_constraints => TRUE,
    copy_privileges  => TRUE,
    ignore_errors    => FALSE,
    num_errors       => l_errors,
    copy_statistics  => FALSE,
    copy_mvlog       => FALSE);
 
  DBMS_OUTPUT.put_line('Errors=' || l_errors);
END;
/

The fact you are partitioning the table means you should probably consider the way you are indexing the table. You may want to manually create the constraints and indexes against the interim table using alternate names to prevent errors. The indexes should be created with the appropriate partitioning scheme to suit their purpose.

-- Add new keys, FKs and triggers.
ALTER TABLE big_table2 ADD (
  CONSTRAINT big_table_pk2 PRIMARY KEY (id)
);



CREATE INDEX bita_created_date_i2 ON big_table2(created_date) LOCAL;

CREATE INDEX bita_look_fk_i2 ON big_table2(lookup_id) LOCAL;

ALTER TABLE big_table2 ADD (
  CONSTRAINT bita_look_fk2
  FOREIGN KEY (lookup_id)
  REFERENCES lookup(id)
);


-- Gather statistics on the new table.
EXEC DBMS_STATS.gather_table_stats(USER, 'BIG_TABLE2', cascade => TRUE);


Complete the Redefinition Process
Once the constraints and indexes have been created the redefinition can be completed using the following command.
BEGIN
  dbms_redefinition.finish_redef_table(
    uname      => USER,      
    orig_table => 'BIG_TABLE',
    int_table  => 'BIG_TABLE2');
END;
/

At this point the interim table has become the "real" table and their names have been switched in the data dictionary.
All that remains is to perform some cleanup operations.


-- Remove original table which now has the name of the interim table.
DROP TABLE big_table2;


-- Rename all the constraints and indexes to match the original names.
ALTER TABLE big_table RENAME CONSTRAINT big_table_pk2 TO big_table_pk;
ALTER TABLE big_table RENAME CONSTRAINT bita_look_fk2 TO bita_look_fk;
ALTER INDEX big_table_pk2 RENAME TO big_table_pk;
ALTER INDEX bita_look_fk_i2 RENAME TO bita_look_fk_i;
ALTER INDEX bita_created_date_i2 RENAME TO bita_created_date_i;


The following queries show that the partitioning was successful.
SELECT partitioned FROM   user_tables WHERE  table_name = 'BIG_TABLE';

PAR
---
YES

1 row selected.



SELECT partition_name FROM   user_tab_partitions WHERE  table_name = 'BIG_TABLE';

PARTITION_NAME
------------------------------
BIG_TABLE_2003
BIG_TABLE_2004
BIG_TABLE_2005




-------------------------------------------------------------------------------------------------------------------------------------------

D. DBMS_REDEFINITION
 ---------------------------------

Step by step instructions on how to convert unpartitioned table to partitioned one using dbms_redefinition package.

1) Create unpartitioned table with the name unpar_table
SQL> CREATE TABLE unpar_table (id NUMBER(10), create_date DATE,name VARCHAR2(100));


2) Apply some constraints to the table:
SQL> ALTER TABLE unpar_table ADD (CONSTRAINT unpar_table_pk PRIMARY KEY (id));
SQL> CREATE INDEX create_date_ind ON unpar_table(create_date);


3) Gather statistics on the table:
SQL> EXEC DBMS_STATS.gather_table_stats(USER, 'unpar_table', cascade => TRUE);


4) Create a Partitioned Interim Table:
SQL> CREATE TABLE par_table (id NUMBER(10),create_date DATE,name VARCHAR2(100))
 PARTITION BY RANGE (create_date)
(PARTITION unpar_table_2014 VALUES LESS THAN (TO_DATE('01/01/2014', 'DD/MM/YYYY')),
PARTITION unpar_table_2015 VALUES LESS THAN (TO_DATE('01/01/2015', 'DD/MM/YYYY')),
PARTITION unpar_table_2016 VALUES LESS THAN (MAXVALUE));



5) Start the Redefinition Process:

           a) Check the redefinition is possible using the following command:
SQL> EXEC Dbms_Redefinition.can_redef_table(USER, 'unpar_table');

 
           b)If no errors are reported, start the redefintion using the following command:

SQL> BEGIN
DBMS_REDEFINITION.start_redef_table( uname => USER,orig_table => 'unpar_table',int_table => 'par_table');
END;
/

Note: This operation can take quite some time to complete.

 
           c) Optionally synchronize new table with interim name before index creation:
SQL> BEGIN
dbms_redefinition.sync_interim_table( uname => USER,orig_table => 'unpar_table',int_table => 'par_table');
END;
/
   
           d) Create Constraints and Indexes:
SQL> ALTER TABLE par_table ADD ( CONSTRAINT unpar_table_pk2 PRIMARY KEY (id));

SQL> CREATE INDEX create_date_ind2 ON par_table(create_date);

   
           e) Gather statistics on the new table:-
SQL> EXEC DBMS_STATS.gather_table_stats(USER, 'par_table', cascade => TRUE);

   
          f) Complete the Redefintion Process:
SQL> BEGIN
dbms_redefinition.finish_redef_table(uname => USER,orig_table => 'unpar_table',int_table => 'par_table');
END;
/
At this point the interim table has become the "real" table and their names have been switched in the name dictionary.

   
           g) Remove original table which now has the name of the interim table:
SQL> DROP TABLE par_table;

   
          h)Rename all the constraints and indexes to match the original names.
ALTER TABLE unpar_table RENAME CONSTRAINT unpar_table_pk2 TO unpar_table_pk;
ALTER INDEX create_date_ind2 RENAME TO create_date_ind;

   
        i) Check whether partitioning is successful or not:
SQL> SELECT partitioned FROM user_tables WHERE table_name = 'unpar_table';

PAR
---
YES

1 row selected.


SQL> SELECT partition_name FROM user_tab_partitions WHERE table_name = 'unpar_table';

PARTITION_NAME
------------------------------
unpar_table_2005
unpar_table_2006
unpar_table_2007


                                 (OR)

Table P is the parent table.
Table T1 is the existing non-partitioned table.
Table T2 is the new partitioned table.

At the end, we are left with P and T1 - T1 being partitioned.



10GR2> create table p ( x primary key ) as select user_id from all_users;
Table created.


10GR2> create table t1 as select * from all_users;
 Table created.
10GR2>EXEC DBMS_STATS.gather_table_stats(USER, 'T1', cascade => TRUE);


10GR2> alter table t1 add constraint t1_pk primary key(user_id);
Table altered.


10GR2> alter table t1 add constraint t1_fk foreign key(user_id) references p(x);
Table altered.


10GR2> create table t2 ( username varchar2(30),user_id  number,created  date ) partition by hash(user_id) partitions 8;
Table created.


10GR2> exec dbms_redefinition.can_redef_table( user, 'T1' );
PL/SQL procedure successfully completed.


10GR2> exec dbms_redefinition.start_redef_table( user, 'T1', 'T2' );
PL/SQL procedure successfully completed.


10GR2>
dbms_redefinition.copy_table_dependents( user, 'T1', 'T2',copy_indexes => dbms_redefinition.cons_orig_params, num_errors => :nerrors );
PL/SQL procedure successfully completed.


10GR2>EXEC DBMS_STATS.gather_table_stats(USER, 'T2', cascade => TRUE);


10GR2> exec dbms_redefinition.finish_redef_table( user, 'T1', 'T2' );
PL/SQL procedure successfully completed.


10GR2> select dbms_metadata.get_ddl( 'TABLE', 'T1' ) from dual;

DBMS_METADATA.GET_DDL('TABLE','T1')
-------------------------------------------------------------------------------

  CREATE TABLE "OPS$TKYTE"."T1"
   (    "USERNAME" VARCHAR2(30) CONSTRAINT "SYS_C0026838" NOT NULL ENABLE NOVALIDA
TE,
        "USER_ID" NUMBER CONSTRAINT "SYS_C0026839" NOT NULL ENABLE NOVALIDATE,
        "CREATED" DATE CONSTRAINT "SYS_C0026840" NOT NULL ENABLE NOVALIDATE,
         CONSTRAINT "T1_PK" PRIMARY KEY ("USER_ID")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS"  ENABLE NOVALIDATE,
         CONSTRAINT "T1_FK" FOREIGN KEY ("USER_ID")
          REFERENCES "OPS$TKYTE"."P" ("X") ENABLE NOVALIDATE
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
  STORAGE(
  BUFFER_POOL DEFAULT)
  TABLESPACE "USERS"
  PARTITION BY HASH ("USER_ID")
 (PARTITION "SYS_P1017"
   TABLESPACE "USERS",
 PARTITION "SYS_P1018"
   TABLESPACE "USERS",
 PARTITION "SYS_P1019"
   TABLESPACE "USERS",
 PARTITION "SYS_P1020"
   TABLESPACE "USERS",
 PARTITION "SYS_P1021"
   TABLESPACE "USERS",
 PARTITION "SYS_P1022"
   TABLESPACE "USERS",
 PARTITION "SYS_P1023"
   TABLESPACE "USERS",
 PARTITION "SYS_P1024"
   TABLESPACE "USERS")




10GR2> select constraint_name, constraint_type from user_constraints where table_name = 'T1';

CONSTRAINT_NAME       C
------------------------------ -
SYS_C0026838                   C
SYS_C0026839                   C
SYS_C0026840                   C
T1_PK                                P
T1_FK                                R