Home » SQL & PL/SQL » SQL & PL/SQL » Adding Range Partitions to existing non-partition tables (Oracle ,10.2.0.3,Solaris)
Adding Range Partitions to existing non-partition tables [message #282080] Tue, 20 November 2007 16:31 Go to next message
sheker2007
Messages: 19
Registered: November 2007
Junior Member
Hi All,
Initially we created some tables without any partitioning,Now we decided for range partitioning.
Iam trying to add partioning to existing tables but getting error like
"Invalid data type"
My code looks like
alter table xyz add partition by range(eff_date)
( partition px1_nov_2007
values less than (to_date ('2007-11-30',yyyy-mm-dd))
partition px1_dec_2007
table space st1
values less than (to_date('2007-12-31',yyyy-mm-dd))
table space st1
)
Thanks
Re: Adding Range Partitions to existing non-partition tables [message #282081 is a reply to message #282080] Tue, 20 November 2007 16:36 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & FOLLOW posting guidelines as stated in URL above

You need to research how to properly use TO_DATE function

Please use CUT & PASTE plus documented <code tags> in the future.
Re: Adding Range Partitions to existing non-partition tables [message #282088 is a reply to message #282080] Tue, 20 November 2007 19:04 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
Quote:

Initially we created some tables without any partitioning,Now we decided for range partitioning.
Iam trying to add partioning to existing tables

alter_table_partitioning clause of ALTER TABLE Statement:
Quote:

The clauses in this section apply only to partitioned tables.

It is not possible. Create new partitioned tables. Should not be difficult after renaming the old tables first.
Re: Adding Range Partitions to existing non-partition tables [message #282093 is a reply to message #282088] Tue, 20 November 2007 19:54 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Alternatively, take a look at DBMS_REDEFINITION

Ross Leishman
Previous Topic: Triggers
Next Topic: Function calling
Goto Forum:
  


Current Time: Fri Dec 09 21:43:31 CST 2016

Total time taken to generate the page: 0.16528 seconds