OraFAQ Forum: SQL & PL/SQL » How to create partition on an existing table with range on a timestamp

Home » SQL & PL/SQL » SQL & PL/SQL » How to create partition on an existing table with range on a timestamp

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

How to create partition on an existing table with range on a timestamp [message #287706]

Thu, 13 December 2007 03:57

novalex
Messages: 19
Registered: November 2007

Junior Member

Hello!

I've red many tutorials about partitioning but in all the tutorials they create partitions directly after creating the table.
I have already created a table with a timestamp and now I want to create a range partition on the timestamp (Year,Month), so that I have partitions on the months combined with the year.
"Alter table name add partition..." doesn't work because I get an error that the table doesn't have any partition.

Hope you can help me.

Thanks.

Alex

Report message to a moderator

Re: How to create partition on an existing table with range on a timestamp [message #287708 is a reply to message #287706]

Thu, 13 December 2007 04:00

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

Senior Member
Account Moderator

You can't partition a table that is not.
Partitioning is defined at table creation time.
You can either recreate the table or use dbms_redefinition package.

Regards
Michel

Report message to a moderator

Re: How to create partition on an existing table with range on a timestamp [message #287710 is a reply to message #287706]

Thu, 13 December 2007 04:05

novalex
Messages: 19
Registered: November 2007

Junior Member

Thank you very much. It was never mentioned in the tutorials I've read.

All right, but can you tell me how to create a partition on a simple timestamp (month combined with year)?
Is it possible or do I need two columns (month, year)?

regards
Alex

Report message to a moderator

Re: How to create partition on an existing table with range on a timestamp [message #287713 is a reply to message #287710]

Thu, 13 December 2007 04:18

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

Senior Member
Account Moderator

partition on (trunc(mytimestamp,'MONTH'))

This truncate the timestamp to month/year:

SQL> select to_char(systimestamp,'DD/MM/YYYY HH24:MI:SS') timestamp, 
  2         trunc(systimestamp,'MONTH') month
  3  from dual;
TIMESTAMP           MONTH
------------------- -------------------
13/12/2007 11:18:09 01/12/2007 00:00:00

1 row selected.

Regards
Michel

Report message to a moderator

Re: How to create partition on an existing table with range on a timestamp [message #287715 is a reply to message #287713]

Thu, 13 December 2007 04:22

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

Senior Member
Account Moderator

This was a silly answer.
Yes, you can range partition on a timestamp field and specify the first of month on each value.

partition by range (mytimestamp) (
partition p200701 values less than (to_date('01/02/2007','DD/MM/YYYY')),
partition p200702  values less than (to_date('01/03/2007','DD/MM/YYYY')),
...

Regards
Michel

[Updated on: Thu, 13 December 2007 04:23]

Report message to a moderator