0

I want to copy a table from one schema into another. Then I want to partition the table. How do I partition a table that I've copied from another schema?

From what I understand about partitioning, this should work, but I keep getting the missing left parenthesis error - which means probably that the syntax is incorrect?

DROP TABLE SS_CUSTOMER;
CREATE TABLE SS_Customer AS SELECT * FROM WL.CUSTOMER
 PARTITION BY HASH(CUST_ID),
   PARTITIONS 4 
   STORE IN (USERS, USERS, CUSTOMER, CUSTOMER);
Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
ShoSom
  • 29
  • 7

1 Answers1

0

I believe your only option is to do this in multiple steps. First, create the partitioned table. Then, run an INSERT statement that selects data from the source table. I don't believe it is possible to do a CREATE TABLE AS SELECT that creates a partitioned table.

As an aside, I can't believe that your partition names are correct. If you're going to name your hash partitions, you'd have to use different names-- creating two partitions named USERS and two named CUSTOMER is not going to work.

Justin Cave
  • 227,342
  • 24
  • 367
  • 384
  • I created the partition and inserted the data. I did this by removing the constraints, which means I will have to add them back in, but it worked, Thank you. – ShoSom Oct 28 '14 at 20:05