Re: Storage option for CREATE TABLE AS..

Thanks Rauf and Sybrand for the answer:
  create table ....
  tablespace ...
  storage ...
  as select * from ...

Of course this worked.

Sybrand's response includes a mild warning that:   "using storage settings like above using a next not equal to init    is a bad idea"

Complying with this, I changed the initial extent size to match the next size. However, coming from an Informix world where this is common practice, I am moved to ask why this is a bad idea? How does it adversely affect the database or admin?

Again, thanks.
+---------- 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 ----+

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.
>
Received on Fri Jul 26 2002 - 17:12:35 CEST