Re: Tablespaces

From: Martin Haltmayer <Martin_Haltmayer_at_KirchGruppe.de>
Date: Tue, 23 Feb 1999 15:33:28 +0100
Message-ID: <36D2BC38.B05B681E@KirchGruppe.de>

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 - 08:33:28 CST