Re: Create table as ...

In article <69ovpt$qdj$1_at_scapa.cs.ualberta.ca>, Wei Tang <wtang_at_cs.ualberta.ca> wrote:
>Hi, there:
>
>I wonder if anyone of you ever used "create table as subquery" statement
>to create a table.
>
>I have a table called test:
>create table test(
> name varchar2(10),
> ID number);
>
>But I cannot use the following statement:
>create table test_bak as select * from test order by ID;
>
>It said: ORA-00933: SQL command not properly ended.
>
>When I remove the "order by" clause, everything is fine. I couldn't find
>any documentation saying "order by" is not supported in "create table
>as..." statement.
>
>What's wrong with that?

Because the ordering is supported by the table definition, not by the order of insertion.

There are a number of things that are implicitly not supported because they would not make sense (if one knows all the implications).

>
>I need your expertise in a rush. Please help me.

You might investigate the unrecoverable option if you are in a hurry.

>
>Thanks.
>

-- 
These opinions are my own and not necessarily those of Information Quest
jgarry@eiq.com                           http://www.informationquest.com
http://ourworld.compuserve.com/homepages/joel_garry
"See your DBA?"  I AM the @#%*& DBA!