Re: Move user to different tablespace

In article <178d2795.0205150516.3d2363f_at_posting.google.com>, mark.powell_at_eds.com says...
>

snip
>
>Linda and Terry, the "alter table owner.table_name tablespace x;"
>command is also available in 8.1. With version 9 the 'online' option
>becomes available allowing update DML to continue to run while the
>rebuild is in process. In 8i no updates are allowed during the
>relocation/rebuild.
>
>This command saves the grants, constraints, and synonyms on the table.
> The indexes need to be rebuilt after the table is moved to update the
>target row ids.
>
>HTH -- Mark D Powell --

I could be wrong on this, but I think the ONLINE option is only available for IOT's. Here's a test case (if anyone sees anything wrong with this let me know):

SQL> REM first create a normal table.  No tablespace specified, default to
SQL> REM SYSTEM
SQL> create table x (y char (1));

Table created.

SQL> REM Try to move it online to the USERS tablespace SQL> alter table x move online tablespace users; alter table x move online tablespace users *
ERROR at line 1:
ORA-01735: invalid ALTER TABLE option

SQL> REM Try to move it without the ONLINE keyword SQL> alter table x move tablespace users;

Table altered.

SQL> REM Create an IOT, tablespace SYSTEM SQL> create table y (x char (1) primary key) organization index;

Table created.

SQL> REM Move it using the ONLINE keyword SQL> alter table y move online tablespace users;

Table altered.

HTH. Additions and corrections welcome.

Pete

SELECT standard_disclaimer, witty_remark FROM company_requirements; Received on Wed May 15 2002 - 10:36:23 CDT