Re: Error at moving tables from tablespace to another
Date: Mon, 10 Nov 2014 06:28:19 -0800
Message-ID: <1415629699.63792.YahooMailNeo_at_web124705.mail.ne1.yahoo.com>
I'm using 11.2.0.4; apparently you're using an older release since it works for me:
SQL> alter table driver move tablespace users overflow tablespace users;
Table altered.
Elapsed: 00:00:00.48
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_16964 DRIVER IOT_OVERFLOW USERS
Elapsed: 00:00:00.00
SQL>
Which release are you using?
David Fitzjarrell
Principal author, "Oracle Exadata Survival Guide"
On Saturday, November 8, 2014 12:25 AM, Mostafa Eletriby <dmarc-noreply_at_freelists.org> wrote:
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_name2 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_name2 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 Mon Nov 10 2014 - 15:28:19 CET