Re: 2 Object Placement Questions...

From: Frank Hubeny <fhubeny_at_ntsource.com>
Date: Thu, 17 Aug 2000 19:38:27 -0500
Message-ID: <399C8583.AE25DB46@ntsource.com>

If the table contains fields with simple datatypes, you can directly move the table using "alter table" xyz "move tablespace" newone.

For example, (NT, 8.1.6):

SQL> create table userstable (a number); Table created.
SQL> select tablespace_name from user_tables where table_name = 'USERSTABLE';

TABLESPACE_NAME

---

USERS SQL> alter table userstable move tablespace system; Table altered.
SQL> select tablespace_name from user_tables where table_name = 'USERSTABLE';

TABLESPACE_NAME

---

SYSTEM However, if the table were defined with a long datatype, the above move would not work:

SQL> create table userstable (a long);
Table created.
SQL> alter table userstable move tablespace system; alter table userstable move tablespace system *
ERROR at line 1:
ORA-00997: illegal use of LONG datatype

Additional syntax is available in Oracle 8i SQL Reference (a76989.pdf 7-127).

Frank Hubeny

Chris England wrote:

> 1. Can you move tables/objects from one tablespace to another? (Not
> referring to selecting the data out of the existing table, dropping the
> table, recreating it on another tablespace, then sqlloading it) I'd like to
> be able to do this using SQL if possible.
>
> 2. Can you/how do you move redo logs. I would like to move the redo logs
> from their current locations in order to reduce possible i/o contention.
>
> Thanks!
Received on Thu Aug 17 2000 - 19:38:27 CDT