Home » SQL & PL/SQL » SQL & PL/SQL » How to create partition on an existing table with range on a timestamp
How to create partition on an existing table with range on a timestamp [message #287706] Thu, 13 December 2007 03:57 Go to next message
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
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 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
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
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 Go to previous messageGo to next message
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
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 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
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
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 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
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

Re: How to create partition on an existing table with range on a timestamp [message #287716 is a reply to message #287706] Thu, 13 December 2007 04:26 Go to previous messageGo to next message
novalex
Messages: 19
Registered: November 2007
Junior Member
Merci beaucoup! Wink
icon10.gif  Re: How to create partition on an existing table with range on a timestamp [message #298608 is a reply to message #287706] Wed, 06 February 2008 14:21 Go to previous message
atl2008
Messages: 2
Registered: February 2008
Junior Member
Thanks.
Previous Topic: How to add multiple partitions to a table
Next Topic: Need help about ORA-01427: single-row subquery returns more than one row
Goto Forum:
  


Current Time: Sat Dec 10 12:46:46 CST 2016

Total time taken to generate the page: 0.09073 seconds