Re: Error at moving tables from tablespace to another

From: Mostafa Eletriby <m_etrib_at_yahoo.com>
Date: Fri, 7 Nov 2014 23:25:32 -0800
Message-ID: <1415431532.66711.YahooMailNeo_at_web162406.mail.bf1.yahoo.com>

Hello ,
Thanks for this clarification.
Actually when I try to user alter statement , it gives me error.

ex:
alter table driver move tablespace users overflow tablespace users;

ORA-01735: invalid ALTER TABLE option
when I use same tablespace for the destination one.

Thanks

On Wednesday, November 5, 2014 4:51 PM, David Fitzjarrell <dmarc-noreply_at_freelists.org> wrote:  

My post was not complete, to correct that I'll repost with a modified script:

SQL> create table driver(

  2          driver_name     varchar2(40),
  3          other_stuff     varchar2(100),
  4          recnum          number,
  5          constraint driver_pk
  6          primary key(recnum)

  7 )
  8 organization index
  9 tablespace indx
 10 overflow tablespace indx;

Table created.

Elapsed: 00:00:00.01
SQL>
SQL> begin

  2          for i in 1..1000 loop
  3                  insert into driver
  4                  values (TO_CHAR ( TO_DATE ( TO_CHAR ( i, '99999999999') , 'J'),  'JSP'), 'blather and junk '||i, i);
  5          end  loop;

  6  

 7 commit;
  8
  9 end;
 10 /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.04

SQL>
SQL> column table_name new_value ot_name
SQL>
SQL> select table_name, iot_name, iot_type, tablespace_name

  2 from user_tables
  3 where iot_type = 'IOT_OVERFLOW';

TABLE_NAME                     IOT_NAME                      
 IOT_TYPE     TABLESPACE_NAME


------------------------------ ------------------------------ ------------ ------------------------------

SYS_IOT_OVER_16792             DRIVER                         IOT_OVERFLOW INDX

1 row selected.

Elapsed: 00:00:00.05
SQL>
SQL> alter table &ot_name move tablespace users; alter table SYS_IOT_OVER_16792 move tablespace users

            *
ERROR at line 1:
ORA-25191: cannot reference overflow table of an  index-organized table

Elapsed: 00:00:00.00
SQL>
SQL> alter table driver move tablespace users overflow tablespace users;

Table altered.

Elapsed: 00:00:00.05
SQL>
SQL> select table_name, iot_name, iot_type, tablespace_name   2 from user_tables
  3 where iot_type = 'IOT_OVERFLOW';

TABLE_NAME                     IOT_NAME                       IOT_TYPE     TABLESPACE_NAME


------------------------------ ------------------------------ ------------ ------------------------------

SYS_IOT_OVER_16792             DRIVER                         IOT_OVERFLOW USERS

1 row selected.

Elapsed: 00:00:00.00
SQL> If you use the correct syntax you can move both at the same time. You can't move the overflow segment without moving the IOT, though.  

David Fitzjarrell

Principal author, "Oracle Exadata Survival Guide"

On Wednesday, November 5, 2014 7:40 AM, David Fitzjarrell <dmarc-noreply_at_freelists.org> wrote:  

It is not a bug. The overflow table is defined for a specific tablespace and that won't change even if you move the IOT:

SQL> create table driver(

  2          driver_name     varchar2(40),
  3          other_stuff     varchar2(100),
  4          recnum          number,
  5          constraint driver_pk
  6          primary key(recnum)

  7 )
  8 organization index
  9 tablespace users
 10
 overflow tablespace indx;

Table created.

Elapsed: 00:00:00.02
SQL>
SQL> begin

  2          for i in 1..1000 loop
  3                  insert into driver
  4                  values (TO_CHAR ( TO_DATE ( TO_CHAR ( i, '99999999999') , 'J'),  'JSP'), 'blather and junk '||i, i);
  5          end  loop;
  6
  7          commit;

  8
  9 end;
 10 /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.04

SQL>
SQL> column table_name new_value ot_name
SQL>
SQL> select table_name, iot_name, iot_type, tablespace_name

  2 from user_tables
  3 where iot_type = 'IOT_OVERFLOW';

TABLE_NAME                     IOT_NAME                       IOT_TYPE     TABLESPACE_NAME


------------------------------ ------------------------------ ------------ ------------------------------

SYS_IOT_OVER_16788             DRIVER                         IOT_OVERFLOW INDX

1 row selected.

Elapsed: 00:00:00.05
SQL>
SQL> alter table &ot_name move tablespace users; alter table SYS_IOT_OVER_16788 move tablespace users

            *
ERROR at line 1:
ORA-25191: cannot reference overflow table of an index-organized table

Elapsed: 00:00:00.01
SQL>
SQL> alter table driver move tablespace indx;

Table altered.

Elapsed: 00:00:00.10
SQL>
SQL>
 select table_name, iot_name, iot_type, tablespace_name   2 from user_tables
  3 where iot_type = 'IOT_OVERFLOW';

TABLE_NAME                     IOT_NAME                       IOT_TYPE     TABLESPACE_NAME


------------------------------ ------------------------------ ------------

 ------------------------------
SYS_IOT_OVER_16788             DRIVER                         IOT_OVERFLOW INDX

1 row selected.

Elapsed: 00:00:00.00
SQL>   David Fitzjarrell

Principal author, "Oracle Exadata Survival Guide"

On Wednesday, November 5, 2014 5:51 AM, Mostafa Eletriby <dmarc-noreply_at_freelists.org> wrote:  

Hello All,
I have a problem regarding moving tables to another tablespace or same tablespace From sysaux tablespace and from another tablespaces , show same error. DB version: 11.2.0.2
O.s : Windows server 2008 R2 Enterprise Edition

ALTER TABLE<table name> MOVE TABLESPACE <tablespace name> ; ALTER TABLE<table name> MOVE TABLESPACE ;

ORA-25191: cannot reference overflow table of an index-organized table

I searched a lot at MOS and googled the error but I am not sure of it is a bug & I may have to upgrade DB.

(Doc ID 49763.1)

Please advice.
Regards,
--

http://www.freelists.org/webpage/oracle-l Received on Sat Nov 08 2014 - 08:25:32 CET