partitions –Oracle 10g practicals

December 1, 2016

Create a table which hold userdata
CREATE TABLE TXN
( TDATE DATE,
START_DATE NUMBER,
END_DATE NUMBER,
PNUMBER NUMBER,
ACODE CHAR(2)
)

Insert data into the table with appx. 10000 recs per month for 1 year months can be increased/decreased using 0..11)
begin
for z in 0..11 loop
for i in 1..323 loop
for j in 1..31 loop
insert into txn1 values(sysdate-433+j+(31*z),1212*i,2323232*i,122323*i,(select
decode(trunc(dbms_random.value(1,5)),1,’CN’,2,’MD’,3,’CO’,4,’TR’,’CN’) from dual));
end loop;
end loop;
end loop;
end;
/

Range_partition

CREATE TABLE TXN_RANGE
( TDATE DATE,
START_DATE NUMBER,
END_DATE NUMBER,
PNUMBER NUMBER,
ACODE CHAR(2))
PARTITION BY RANGE (tdate)
(PARTITION p1 VALUES LESS THAN (TO_DATE(’31/03/2015′, ‘DD/MM/YYYY’)) TABLESPACE TEST,
PARTITION p2 VALUES LESS THAN (TO_DATE(’30/06/2015′, ‘DD/MM/YYYY’)) TABLESPACE TEST,
PARTITION p3 VALUES LESS THAN (TO_DATE(’30/09/2015′, ‘DD/MM/YYYY’)) TABLESPACE TEST,
PARTITION p4 VALUES LESS THAN (TO_DATE(’31/12/2015′, ‘DD/MM/YYYY’)) TABLESPACE TEST);

List_partition
CREATE TABLE TXN_LIST
( TDATE DATE,
START_DATE NUMBER,
END_DATE NUMBER,
PNUMBER NUMBER,
ACODE CHAR(2))
PARTITION BY LIST (ACODE)
(
PARTITION Chennai VALUES (‘CN’),
PARTITION madurai VALUES (‘MD’),
PARTITION Coimb VALUES (‘CO’),
PARTITION trichy VALUES (‘TR’)
);

HASH_partition

CREATE TABLE TXN_HASH
(TDATE DATE,
START_DATE NUMBER,
END_DATE NUMBER,
PNUMBER NUMBER,
ACODE CHAR(2))
PARTITION BY HASH (TDATE)
PARTITIONS 4
STORE IN (TEST, TEST, TEST, TEST);

Composite partition(Range-List)
CREATE TABLE TXN_RANGE_LIST
(TDATE DATE,
START_DATE NUMBER,
END_DATE NUMBER,
PNUMBER NUMBER,
ACODE CHAR(2))
PARTITION BY RANGE (TDATE)
SUBPARTITION BY LIST (ACODE)
SUBPARTITION TEMPLATE
(
SUBPARTITION Chennai VALUES (‘CN’),
SUBPARTITION madurai VALUES (‘MD’),
SUBPARTITION Coimb VALUES (‘CO’),
SUBPARTITION trichy VALUES (‘TR’)
)
(PARTITION p1 VALUES LESS THAN (TO_DATE(’31/03/2015′, ‘DD/MM/YYYY’)) TABLESPACE TEST,
PARTITION p2 VALUES LESS THAN (TO_DATE(’30/06/2015′, ‘DD/MM/YYYY’)) TABLESPACE TEST,
PARTITION p3 VALUES LESS THAN (TO_DATE(’30/09/2015′, ‘DD/MM/YYYY’)) TABLESPACE TEST,
PARTITION p4 VALUES LESS THAN (MAXVALUE) TABLESPACE TEST);

To insert data use the below commad for all partitioned tables
insert into TXN_RANGE_LIST select * from txn1;

views :
dba_tab_partitions
select table_name,partition_name,num_rows,blocks,high_value from user_tab_partitions

dba_tab_subpartitions
select table_name,partition_name,subpartition_name,tablespace_name,num_rows from user_tab_subpartitions

partitions tables and indexes in oracle

November 19, 2016

By postgresdba

Partitioned Tables And Indexes
Maintenance of large tables and indexes can become very time and resource consuming. At the same time, data access performance can reduce drastically for these objects. Partitioning of tables and indexes can benefit the performance and maintenance in several ways.

Partition independance means backup and recovery operations can be performed on individual partitions, whilst leaving the other partitons available.
Query performance can be improved as access can be limited to relevant partitons only.
There is a greater ability for parallelism with more partitions.

All the examples shown here use the users tablespace for all partitions. In a real situation it is likely that these partitions would be assigned to different tablespaces to reduce device contention.

Range Partitioning Tables
Hash Partitioning Table
Composite Partitioning Tables
Partitioning Indexes
Local Prefixed Indexes
Local Non-Prefixed Indexes
Global Prefixed Indexes
Global Non-Prefixed Indexes
Partitioning Existing Tables

Related articles.

Partitioning Enhancements In Oracle9i
Hash Partitioned Global Indexes in Oracle 10g
Partitioning Enhancements in Oracle Database 11g Release 1
Partitioning an Existing Table using DBMS_REDEFINITION
Partitioning an Existing Table using EXCHANGE PARTITION

1.Range Partitioning Tables
Range partitioning is useful when you have distinct ranges of data you want to store together. The classic example of this is the use of dates. Partitioning a table using date ranges allows all data of a similar age to be stored in same partition. Once historical data is no longer needed the whole partition can be removed. If the table is indexed correctly search criteria can limit the search to the partitions that hold data of a correct age.

CREATE TABLE invoices
(invoice_no NUMBER NOT NULL,
invoice_date DATE NOT NULL,
comments VARCHAR2(500))
PARTITION BY RANGE (invoice_date)
(PARTITION invoices_q1 VALUES LESS THAN (TO_DATE(’01/04/2001′, ‘DD/MM/YYYY’)) TABLESPACE users,
PARTITION invoices_q2 VALUES LESS THAN (TO_DATE(’01/07/2001′, ‘DD/MM/YYYY’)) TABLESPACE users,
PARTITION invoices_q3 VALUES LESS THAN (TO_DATE(’01/09/2001′, ‘DD/MM/YYYY’)) TABLESPACE users,
PARTITION invoices_q4 VALUES LESS THAN (TO_DATE(’01/01/2002′, ‘DD/MM/YYYY’)) TABLESPACE users);

2.Hash Partitioning Tables
Hash partitioning is useful when there is no obvious range key, or range partitioning will cause uneven distribution of data. The number of partitions must be a power of 2 (2, 4, 8, 16…) and can be specified by the PARTITIONS…STORE IN clause.

CREATE TABLE invoices
(invoice_no NUMBER NOT NULL,
invoice_date DATE NOT NULL,
comments VARCHAR2(500))
PARTITION BY HASH (invoice_no)
PARTITIONS 4
STORE IN (users, users, users, users);
Or specified individually.

CREATE TABLE invoices
(invoice_no NUMBER NOT NULL,
invoice_date DATE NOT NULL,
comments VARCHAR2(500))
PARTITION BY HASH (invoice_no)
(PARTITION invoices_q1 TABLESPACE users,
PARTITION invoices_q2 TABLESPACE users,
PARTITION invoices_q3 TABLESPACE users,
PARTITION invoices_q4 TABLESPACE users);

3.Composite Partitioning Tables
Composite partitioning allows range partitions to be hash subpartitioned on a different key. The greater number of partitions increases the possiblities for parallelism and reduces the chances of contention. The following example will range partition the table on invoice_date and subpartitioned these on the invoice_no giving a totol of 32 subpartitions.

CREATE TABLE invoices
(invoice_no NUMBER NOT NULL,
invoice_date DATE NOT NULL,
comments VARCHAR2(500))
PARTITION BY RANGE (invoice_date)
SUBPARTITION BY HASH (invoice_no)
SUBPARTITIONS 8
(PARTITION invoices_q1 VALUES LESS THAN (TO_DATE(’01/04/2001′, ‘DD/MM/YYYY’)),
PARTITION invoices_q2 VALUES LESS THAN (TO_DATE(’01/07/2001′, ‘DD/MM/YYYY’)),
PARTITION invoices_q3 VALUES LESS THAN (TO_DATE(’01/09/2001′, ‘DD/MM/YYYY’)),
PARTITION invoices_q4 VALUES LESS THAN (TO_DATE(’01/01/2002′, ‘DD/MM/YYYY’));

4.Partitioning Indexes
There are two basic types of partitioned index.
Local – All index entries in a single partition will correspond to a single table partition (equipartitioned). They are created with the LOCAL keyword and support partition independance. Equipartioning allows oracle to be more efficient whilst devising query plans.

Global – Index in a single partition may correspond to multiple table partitions. They are created with the GLOBAL keyword and do not support partition independance. Global indexes can only be range partitioned and may be partitioned in such a fashion that they look equipartitioned, but Oracle will not take advantage of this structure.

Both types of indexes can be subdivided further.

Prefixed – The partition key is the leftmost column(s) of the index. Probing this type of index is less costly. If a query specifies the partition key in the where clause partition pruning is possible, that is, not all partitions will be searched.

Non-Prefixed – Does not support partition pruning, but is effective in accessing data that spans multiple partitions. Often used for indexing a column that is not the tables partition key, when you would like the index to be partitioned on the same key as the underlying table.
Local Prefixed Indexes

Assuming the INVOICES table is range partitioned on INVOICE_DATE, the followning are examples of

5.local prefixed indexes.
CREATE INDEX invoices_idx ON invoices (invoice_date) LOCAL;

CREATE INDEX invoices_idx ON invoices (invoice_date) LOCAL
(PARTITION invoices_q1 TABLESPACE users,
PARTITION invoices_q2 TABLESPACE users,
PARTITION invoices_q3 TABLESPACE users,
PARTITION invoices_q4 TABLESPACE users);

Oracle will generate the partition names and build the partitions in the default tablespace using the default size unless told otherwise.

6.Local Non-Prefixed Indexes
Assuming the INVOICES table is range partitioned on INVOICE_DATE, the following example is of a local non-prefixed index. The indexed column does not match the partition key.

CREATE INDEX invoices_idx ON invoices (invoice_no) LOCAL
(PARTITION invoices_q1 TABLESPACE users,
PARTITION invoices_q2 TABLESPACE users,
PARTITION invoices_q3 TABLESPACE users,
PARTITION invoices_q4 TABLESPACE users);

7.Global Prefixed Indexes
Assuming the INVOICES table is range partitioned on INVOICE_DATE, the followning examples is of a global prefixed index.

CREATE INDEX invoices_idx ON invoices (invoice_date)
GLOBAL PARTITION BY RANGE (invoice_date)
(PARTITION invoices_q1 VALUES LESS THAN (TO_DATE(’01/04/2001′, ‘DD/MM/YYYY’)) TABLESPACE users,
PARTITION invoices_q2 VALUES LESS THAN (TO_DATE(’01/07/2001′, ‘DD/MM/YYYY’)) TABLESPACE users,
PARTITION invoices_q3 VALUES LESS THAN (TO_DATE(’01/09/2001′, ‘DD/MM/YYYY’)) TABLESPACE users,
PARTITION invoices_q4 VALUES LESS THAN (MAXVALUE) TABLESPACE users);

Note that the partition range values must be specified. The GLOBAL keyword means that Oracle can not assume the partition key is the same as the underlying table.

8.Global Non-Prefixed Indexes

Oracle does not support Global Non Prefixed indexes.

9.Partitioning Existing Tables
The ALTER TABLE … EXCHANGE PARTITION … syntax can be used to partition an existing table, as shown by the following example. First we must create a non-partitioned table to act as our starting point.

CREATE TABLE my_table (
id NUMBER,
description VARCHAR2(50)
);

INSERT INTO my_table (id, description) VALUES (1, ‘One’);
INSERT INTO my_table (id, description) VALUES (2, ‘Two’);
INSERT INTO my_table (id, description) VALUES (3, ‘Three’);
INSERT INTO my_table (id, description) VALUES (4, ‘Four’);
COMMIT;

Next we create a new partitioned table with a single partition to act as our destination table.

CREATE TABLE my_table_2 (
id NUMBER,
description VARCHAR2(50)
)
PARTITION BY RANGE (id)
(PARTITION my_table_part VALUES LESS THAN (MAXVALUE));

Next we switch the original table segment with the partition segment.

ALTER TABLE my_table_2
EXCHANGE PARTITION my_table_part
WITH TABLE my_table
WITHOUT VALIDATION;

We can now drop the original table and rename the partitioned table.

DROP TABLE my_table;
RENAME my_table_2 TO my_table;

Finally we can split the partitioned table into multiple partitions as required and gather new statistics.

ALTER TABLE my_table SPLIT PARTITION my_table_part AT (3)
INTO (PARTITION my_table_part_1,
PARTITION my_table_part_2);

EXEC DBMS_STATS.gather_table_stats(USER, ‘MY_TABLE’, cascade => TRUE);

The following query shows that the partitioning process is complete.

COLUMN high_value FORMAT A20
SELECT table_name,
partition_name,
high_value,
num_rows
FROM user_tab_partitions
ORDER BY table_name, partition_name;

TABLE_NAME PARTITION_NAME HIGH_VALUE NUM_ROWS
—————————— —————————— ——————– ———-
MY_TABLE MY_TABLE_PART_1 3 2
MY_TABLE MY_TABLE_PART_2 MAXVALUE 2

2 rows selected.
1 row created.

how to Partitioning a Non-partitioned table –oracle dba

November 19, 2016

By postgresdba

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