OraFAQ Forum: Server Administration » How to Partition existing Oracle 11g Table

Home » RDBMS Server » Server Administration » How to Partition existing Oracle 11g Table (Oracle 11g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

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

Report message to a moderator

Re: How to Partition existing Oracle 11g Table [message #620048 is a reply to message #620047]

Mon, 28 July 2014 07:07

John Watson
Messages: 8962
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).

Report message to a moderator

Re: How to Partition existing Oracle 11g Table [message #620050 is a reply to message #620048]

Mon, 28 July 2014 07:19

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

Report message to a moderator

Re: How to Partition existing Oracle 11g Table [message #620051 is a reply to message #620047]

Mon, 28 July 2014 07:24

Lalit Kumar B
Messages: 3174
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

John Watson
Messages: 8962
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.

Report message to a moderator

Re: How to Partition existing Oracle 11g Table [message #620056 is a reply to message #620047]

Mon, 28 July 2014 07:49

Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

For cross-ref, same question at:

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

Report message to a moderator

Re: How to Partition existing Oracle 11g Table [message #620059 is a reply to message #620053]

Mon, 28 July 2014 07:56

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

Report message to a moderator

Re: How to Partition existing Oracle 11g Table [message #620062 is a reply to message #620059]

Mon, 28 July 2014 08:41

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

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?

Report message to a moderator

Re: How to Partition existing Oracle 11g Table [message #620077 is a reply to message #620047]

Mon, 28 July 2014 11:11

LKBrwn_DBA
Messages: 487
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

Lalit Kumar B
Messages: 3174
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.

Report message to a moderator

Re: How to Partition existing Oracle 11g Table [message #620086 is a reply to message #620059]

Mon, 28 July 2014 12:34

joy_division
Messages: 4963
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?

Report message to a moderator

Re: How to Partition existing Oracle 11g Table [message #620098 is a reply to message #620079]

Mon, 28 July 2014 13:23

LKBrwn_DBA
Messages: 487
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:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Dec 22 01:32:16 CST 2024