Re: Storage option for CREATE TABLE AS..

JSalomon_at_bn.com (Jacob Salomon) wrote in message news:<38b6a68e.0207251443.6dbefa2f_at_posting.google.com>...
> 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 ----+

"AS subquery" goes at the end.

CREATE TABLE product3
TABLESPACE prod_space
STORAGE (INITIAL 40M NEXT 20M PCTINCREASE 0) AS
SELECT * FROM product2;

HTH
//Rauf Sarwar Received on Fri Jul 26 2002 - 05:57:51 CEST