Home » RDBMS Server » Server Administration » How to Partition existing Oracle 11g Table (Oracle 11g)
How to Partition existing Oracle 11g Table [message #620047] Mon, 28 July 2014 06:44 Go to next message
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
Re: How to Partition existing Oracle 11g Table [message #620048 is a reply to message #620047] Mon, 28 July 2014 07:07 Go to previous messageGo to next message
John Watson
Messages: 4610
Registered: January 2010
Location: Global Village
Senior Member
Welcome to the forum. Please read our OraFAQ Forum Guide and please read How to use [code] tags and make your code easier to read

Your code and technique look fine to me, except that you have a mistake in your date format string, HH24:MIS is not going to work.
I do not believe that the sum of rows in each partition was less than the rows in the table! You need to prove this assertion: demonstrate it, using copy/paste (and don't forget the [code] tags).
Re: How to Partition existing Oracle 11g Table [message #620050 is a reply to message #620048] Mon, 28 July 2014 07:19 Go to previous messageGo to next message
amit.sonu38
Messages: 3
Registered: July 2014
Junior Member
Thanks for Reply John. I appreciate it.
Actually date format string was as mentioned below.

TO_DATE(' 2013-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')



Do you think it looks good now?

Also what I meant by second question is that. When I count the number of rows in my non partitioned and partitioned tables(select count(*) from table_name) it matches but when I run the following query and add the num_rows column values, then sum of num_rows column comes greater than total number of rows in my non partitioned or partitioned tables.

SELECT partition_name, num_rows
FROM user_tab_partitions where tablespace_name = 'USERS'

Any idea why this is happening?

Thanks,
Amit Garg
Re: How to Partition existing Oracle 11g Table [message #620051 is a reply to message #620047] Mon, 28 July 2014 07:24 Go to previous messageGo to next message
Lalit Kumar B
Messages: 2407
Registered: May 2013
Location: World Wide on the Web
Senior Member
> I also tried use this approach on one table

Your script should error out due to unrecognozed date picture format as John pointed out. Can you copy paste your SQL*Plus session.

Edit : OP posted before me with his corrections

[Updated on: Mon, 28 July 2014 07:27]

Report message to a moderator

Re: How to Partition existing Oracle 11g Table [message #620053 is a reply to message #620050] Mon, 28 July 2014 07:29 Go to previous messageGo to next message
John Watson
Messages: 4610
Registered: January 2010
Location: Global Village
Senior Member
The figures in user_tab_partitions are estimates, and are static until you analyze the table again. They are not necessarily correct.
Re: How to Partition existing Oracle 11g Table [message #620056 is a reply to message #620047] Mon, 28 July 2014 07:49 Go to previous messageGo to next message
Michel Cadot
Messages: 59289
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

For cross-ref, same question at:

http://www.dba-village.com/village/dvp_forum.OpenThread?ThreadIdA=72741&SkipA=0

Re: How to Partition existing Oracle 11g Table [message #620059 is a reply to message #620053] Mon, 28 July 2014 07:56 Go to previous messageGo to next message
amit.sonu38
Messages: 3
Registered: July 2014
Junior Member
Here are the values,

select count(*) from t1 partition (q1_2013);
1000

select count(*) from t1 partition (q2_2013);
3000

select count(*) from t1 partition (q3_2013);
10000

select count(*) from t1 partition (q4_2013);
8000


select count(*) from t1 where changed_dt between '01-JAN-13' and '31-MAR13';
1000

select count(*) from t1 where changed_dt between '01-APR-13' and '30-JUN-13';
3000

select count(*) from t1 where changed_dt between '01-JUL-13' and '30-SEP-13';
10000

select count(*) from t1 where changed_dt between '01-OCT-13' and '31-DEC-13';
8000

if you see above queries you will find that number of rows is matching
but when I run the following query, I see different result.

SELECT partition_name, num_rows
FROM user_tab_partitions where tablespace_name = 'USERS'

Partition_Name Num_Rows
Q1_2013 1000
Q2_2013 2750
Q3_2013 9500
Q4_2013 10000


Thanks,
Amit
Re: How to Partition existing Oracle 11g Table [message #620062 is a reply to message #620059] Mon, 28 July 2014 08:41 Go to previous messageGo to next message
BlackSwan
Messages: 22839
Registered: January 2009
Senior Member
DITTO

http://www.dbforums.com/oracle/1702740-how-partition-existing-oracle-11g-table.html

How will you or I recognize when correct answer is posted here?
Re: How to Partition existing Oracle 11g Table [message #620077 is a reply to message #620047] Mon, 28 July 2014 11:11 Go to previous messageGo to next message
LKBrwn_DBA
Messages: 439
Registered: July 2003
Location: WPB, FL
Senior Member
Use DBMS_REDEFINITION() Package.

[Updated on: Mon, 28 July 2014 11:13] by Moderator

Report message to a moderator

Re: How to Partition existing Oracle 11g Table [message #620079 is a reply to message #620077] Mon, 28 July 2014 11:15 Go to previous messageGo to next message
Lalit Kumar B
Messages: 2407
Registered: May 2013
Location: World Wide on the Web
Senior Member
LKBrwn_DBA wrote on Mon, 28 July 2014 21:41
Use DBMS_REDEFINITION() Package.


Why? Can you explain please.
Re: How to Partition existing Oracle 11g Table [message #620086 is a reply to message #620059] Mon, 28 July 2014 12:34 Go to previous messageGo to next message
joy_division
Messages: 4520
Registered: February 2005
Location: East Coast USA
Senior Member
amit.sonu38 wrote on Mon, 28 July 2014 08:56
Here are the values,

select count(*) from t1 partition (q1_2013);
1000

select count(*) from t1 partition (q2_2013);
3000

select count(*) from t1 partition (q3_2013);
10000

select count(*) from t1 partition (q4_2013);
8000


select count(*) from t1 where changed_dt between '01-JAN-13' and '31-MAR13';
1000

select count(*) from t1 where changed_dt between '01-APR-13' and '30-JUN-13';
3000

select count(*) from t1 where changed_dt between '01-JUL-13' and '30-SEP-13';
10000

select count(*) from t1 where changed_dt between '01-OCT-13' and '31-DEC-13';
8000

I see many things wrong.

You are comparing DATE columns to text strings. Oracle will "probably" properly implicitly convert, but then...

Did you really use two different "text" formats for dates as in '30-JUN-13' and '31-MAR13'?

Do your tables really have perfectly rounded number of rows in each of the partitions? I think all output was faked.

Why can't you just cut and paste actual queries?
Re: How to Partition existing Oracle 11g Table [message #620098 is a reply to message #620079] Mon, 28 July 2014 13:23 Go to previous message
LKBrwn_DBA
Messages: 439
Registered: July 2003
Location: WPB, FL
Senior Member
Lalit Kumar B wrote on Mon, 28 July 2014 12:15
LKBrwn_DBA wrote on Mon, 28 July 2014 21:41
Use DBMS_REDEFINITION() Package.


Why? Can you explain please.

No down time.

[Updated on: Mon, 28 July 2014 13:48] by Moderator

Report message to a moderator

Previous Topic: ORA-01882: timezone region %s not found
Next Topic: Kill inactive session
Goto Forum:
  


Current Time: Wed Oct 01 06:37:40 CDT 2014

Total time taken to generate the page: 0.06208 seconds