| How to Partition existing Oracle 11g Table [message #620047] |
Mon, 28 July 2014 06:44  |
 |
amit.sonu38
Messages: 3
Registered: July 2014
|
Junior Member |
|
|
Hello All,
I need help from all of you. I need to partition few existing Oracle tables by Quarterly date range. I am doing this in our Dev environment which is not being used right now so please let me know the steps to partition an existing table in Oracle.
For Example - I am attempting to partition a Table T1 with existing data.
Table T1 is as follows:
COLUMN DATATYPE
-----------------
COLUMN1 NUMBER PK
COLUMN2 NUMBER
COLUMN3 NUMBER
CHANGED_DT DATE
I am using this approach:
1- ALTER TABLE T1 RENAME TO T1_TEMP
2- CREATE TABLE T1 (
COLUMN1 NUMBER,
COLUMN2 NUMBER,
COLUMN3 NUMBER,
CHANGED_DT DATE,
CONSTRAINT "PK1_PK" PRIMARY KEY ("COLUMN1") )
PARTITION BY RANGE (changed_dt)
( PARTITION "Q1_2013" VALUES LESS THAN (TO_DATE(' 2013-04-01 00:00:00', 'SYYYY-MM-DD HH24:MIS', 'NLS_CALENDAR=GREGORIAN')) TABLESPACE "USERS" ,
PARTITION "Q2_2013" VALUES LESS THAN (TO_DATE(' 2013-07-01 00:00:00', 'SYYYY-MM-DD HH24:MIS', 'NLS_CALENDAR=GREGORIAN')) TABLESPACE "USERS" ,
PARTITION "Q3_2013" VALUES LESS THAN (TO_DATE(' 2013-10-01 00:00:00', 'SYYYY-MM-DD HH24:MIS', 'NLS_CALENDAR=GREGORIAN')) TABLESPACE "USERS" ,
PARTITION "Q4_2013" VALUES LESS THAN (TO_DATE(' 2014-01-01 00:00:00', 'SYYYY-MM-DD HH24:MIS', 'NLS_CALENDAR=GREGORIAN')) TABLESPACE "USERS" )
ENABLE ROW MOVEMENT;
3- INSERT INTO T1 SELECT * FROM T1_TEMP
Please let me know if you think above approach is wrong approach. I also tried use this approach on one table and was able to create the new partitioned table with data in it. But If I do Select count(*) then number of rows matches with this non partitioned table but when I query the number of rows in each partition and then add them, then total number of rows are greater than Select Count(*) from same table. Please let me know if this is correct behavior.
Thanks a lot in Advance !!!
Regards,
AG
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
