CTAS with partitions and constraint statement, Is this possible? Help!
wolfeetJul 13 2009 — edited Jul 13 2009I'm trying to use CTAS to create a copy of one of our large tables to test the use of local indexs and partition pruning. Can anyone help me out here? Is this doable or should I go another route? I'm also rearranging the table to put the first column of my primary key as the first column of the table.
Create table new_table,
constraint pk_new_table primary key (seq_number,ssn,service_code)
using index (create index PK_LI_new_table ON new_table (seq_number,ssn,service_code) LOCAL tablespace TS_LI_new_table)
tablespace TS_NEW_TABLE
PARTITION BY RANGE (SEQ_NUMBER)
(
partition P197203 values less than (2) tablespace ts_new_table_197203,
.
.
.
partition P200906 values less than (245) tablespace ts_new_table_200906
)
AS SELECT (<new order of columns>) from <original_table>
parallel enable row movement;
output from statement:
Create table new_table,
Error at line 1:
ORA-00922: missing or invalid option
The asterisk is below the comma in the above error statement. Would I need to list out the new table columns and datatypes in the create table statement? Any help appreciated.