Storage option for CREATE TABLE AS..

Hi Folks.

I was asked to replicate a table. Simple enough, right?

CREATE TABLE product3 as select * from product2;

Not so fast.. It gets created in the default tablespace, not where I wanted it, and quickly exceeds the tablespace's quota on extents. What I need is storage options in this type of create statement.

I have tried:

create table product3 as select * from product2

   using TABLESPACE prod_space
   storage (initial 40m next 20m pctincrease 0)

I have also tried the same statement without the word USING in the tablespace clause. Other variations include putting (parentheses) around the select statement and adding a dummy where clause (where 1 = 1).

All variations earned me the same error message:

    ERROR at line 2:
    ORA-00933: SQL command not properly ended

Duh?

I know I can create the table explicitly, naming every blessed (NOT!) column and explicitly naming my tablespace and storage parameters, followed by:

    insert into product3 select * from product2 but then I would feel I'm not finessing it the way I'd like to.

I have seen posts that ALMOST hit this and then I see they refer to something else. The Oracle docs manage to skip this variation of the command. Does anyone know the syntax for what I'm trying to do?

Thanks much.

+---------- Jacob Salomon JSalomon_at_bn.com -- -------------- ------+
| Conservative (n): A statesman who is enamored of existing evils,|
| as distinguished from the Liberal, who wishes to replace them |
| with others. |

+--------------------- Ambrose Bierce, The Devil's Dictionary ----+