Re: Tablespaces

Except where your tables are very large - i.e. summary tables in a datawarehouse. A parallel 'select table as' will massively outperform an import. And you add the constraints and indexes afterwards, again in parallel.

Steve Phelan

(Oracle 7 & 8 OCP)

Martin Haltmayer wrote:

> Export and import is a must in my opinion.
>
> Create table as select does not exactly the thing it should. Consider the
> following example:
>
> Oracle7 Server Release 7.3.4.3.0 - Production
> With the parallel query and Parallel Server options
> PL/SQL Release 2.3.4.3.0 - Production
>
> SQL> create table test1 (n number default 555 not null);
>
> Table created.
>
> SQL> desc test1
> Name Null? Type
> ------------------------------- -------- ----
> N NOT NULL NUMBER
>
> SQL> create table test2 as select * from test1;
>
> Table created.
>
> SQL> desc test2
> Name Null? Type
> ------------------------------- -------- ----
> N NOT NULL NUMBER
>
> SQL> select table_name, data_default from user_tab_columns where table_name in
> ('TEST1', 'TEST2');
>
> TABLE_NAME
> ------------------------------
> DATA_DEFAULT
> --------------------------------------------------------------------------------
> TEST1
> 555
>
> TEST2
>
>
>
> 2 rows selected.
>
> SQL>
>
> That means, that default values are not copied but the not null constraints.
> This behaviour took us some time to detect in a 4GL application where all the
> columns had not null constraints and should have default values but did not
> receive by copying.
>
> Martin Haltmayer
>
> Huwski schrieb:
> >
> > Could someone tell me the best way of moving tables from one
> > tablespace to another.
> >
> > TIA
> >
> > Huwski.
Received on Tue Feb 23 1999 - 13:56:35 CST