recreate table within different name and tablespace

[Advocate]

could anyone help me with sample syntax, or point me to some examples, to recreate table from template using a different tablespace ?

eg

create table new_table_name
as (select * from template_table)
tablespace target_tablespace

is the tablespace syntax correct/recommended ?


regards, david - no such thing as problems - only solutions.

 

[dbtoo2001]

http://download-west.oracle.com/docs/cd/A87860_01/doc/server.817/a85397/statem3e.htm#2062864

 

[Advocate]

thank you for response

unfortunately, no specific example there for me ...

CREATE TABLE NW.STG_DAX AS (SELECT * FROM NW.STG_DAX_TEMPLATE WHERE 1=0) TABLESPACE NW.SCRATCH

getting following error message when run in toad sql editor

ORA-00933: SQL command not properly ended

and word TABLESPACE highlighted

am i doing something silly as usual ... ?


regards, david - no such thing as problems - only solutions.

 

[gunjan14]

Hi
the command is
create table <TABLE_NAME> tablespace <TABLESPACE NAME>
as select * from <ANOTHER TABLE>;

Regards,
Gunjan

 

[Advocate]

thank you gunjan14,

that works ok but cannot improve to include extents ie ...

create table <TABLE_NAME> tablespace <TABLESPACE NAME>
STORAGE (
INITIAL_EXTENT <v_INITIAL_EXTENT>
NEXT_EXTENT <v_NEXT_EXTENT>
MAX_EXTENTS <v_MAX_EXTENTS> )

as select * from <ANOTHER TABLE>;

anybody please ?


regards, david - no such thing as problems - only solutions.

 

[SantaMufasa]

Advocate,

Here is an actual code sample on my 9.2.0.4 (but the same syntax should work on any version, provided you are not using a LMT):

create table s_emp3 tablespace system
storage (initial 16k next 16k maxextents unlimited)
as select * from s_emp;

Table created.

Let us know of either success or problems.

Mufasa
(aka Dave of Sandy, Utah, USA @ 06:49 (26Jul04) UTC (aka "GMT" and "Zulu"), 23:49 (25Jul04) Mountain Time)