Re: Error at moving tables from tablespace to another
- From: "David Fitzjarrell" <dmarc-noreply@xxxxxxxxxxxxx> (Redacted sender "oratune@xxxxxxxxx" for DMARC)
- To: "dmarc-noreply@xxxxxxxxxxxxx" <dmarc-noreply@xxxxxxxxxxxxx>, Oracle-l Digest Users <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 5 Nov 2014 06:40:47 -0800
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@xxxxxxxxxxxxx> 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,
Other related posts:
