adding partition to existing non-partitioned table

pts.
Tags:
Database
IBM DB2
Oracle
SQL
Hello all! I have a tables without partitions and I now want to partition it. How do I go about this? I tired alter table ... add partition... but it errors with "ORA-14501: object is not partitioned " So I tried to re-create the table with partitions: create table quote_header_test as select * from quote_header partition by range (ordered_date) partition less_jul2004 values less than (to_date('01-JUL-2004','MON-DD-YYYY')); This gives me "ORA-00933: SQL command not properly ended" Any suggestions? Thanks! Sharon

Answer Wiki

Thanks. We'll let you know when a new response is added.

Hello:
The ‘AS SELECT…’ clause should be the last part of the CREATE TABLE … PARTITION. Try this:
create table quote_header_test
partition by range (ordered_date)
partition less_jul2004 values less than (to_date(’01-JUL-2004′,’MON-DD-YYYY’))
as select * from quote_header;

Good Luck

Discuss This Question: 3  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Ppaniagua
    Hello: The 'AS SELECT...' clause should be the last part of the CREATE TABLE ... PARTITION. Try this: create table quote_header_test partition by range (ordered_date) partition less_jul2004 values less than (to_date('01-JUL-2004','MON-DD-YYYY')) as select * from quote_header; Good Luck
    0 pointsBadges:
    report
  • DaveInAZ
    Yes, as ppaniagua said, your SQL syntax is out of order. Here's a good articel on Partitioning; http://www.dbazine.com/oracle/or-articles/nanda6 Also, your to_date syntax is wrong. 'MON-DD-YYYY' should be 'DD-MON-YYYY', to match the value '01-JUL-2004'.
    0 pointsBadges:
    report
  • Skovac
    [...] Address: http://itknowledgeexchange.techtarget.com/itanswers/adding-partition-to-existing-non-partitioned-tab... (0) Comments Read [...]
    0 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following