Moving IOT Tables

[THoey]

All,

We are having an issue now with a tablespace that grew too large and needs rebuilding. Part of the process required that we move all objects from the tablespace and then resize it. The issue we are running into now is that there are several Index Organized Tables (IOT's) that are in this tablespace and they are being a pain to move.

Anyone else seen anything like this and have a solution? I can post more details if needed. Also researching Metalink.

Terry
**************************
* General Disclaimer - Please read *
**************************
Please make sure your post is in the CORRECT forum, has a descriptive title, gives as much detail to the problem as possible, and has examples of expected results. This will enable me and others to help you faster...

 

[THoey]

The following is one of the many errors we have seen since we have been trying this:

SQL> ALTER TABLE SYS_IOT_OVER_194009 move tablespace SMFRPT_DATA;
ALTER TABLE SYS_IOT_OVER_194009 move tablespace SMFRPT_DATA
*
ERROR at line 1:
ORA-25191: cannot reference overflow table of an index-organized table


Terry
**************************
* General Disclaimer - Please read *
**************************
Please make sure your post is in the CORRECT forum, has a descriptive title, gives as much detail to the problem as possible, and has examples of expected results. This will enable me and others to help you faster...

 

[SantaMufasa]

What happens when you export, then import the table? That's what I do when I encounter a table that will not "MOVE" due to its characteristices (e.g., LONG/LOB) columns.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[THoey]

Sorry, I am helping another member of my team with this, so it takes a bit to get the info from her. She says she gets the following if she tries to export the IOT Overflow tables:

About to export specified tables via Conventional Path ... EXP-00011: SMFRPT_PROD.SYS_IOT_OVER_194009; does not exist Export terminated successfully with warnings.

Terry
**************************
* General Disclaimer - Please read *
**************************
Please make sure your post is in the CORRECT forum, has a descriptive title, gives as much detail to the problem as possible, and has examples of expected results. This will enable me and others to help you faster...

 

[SantaMufasa]

Terry,

Since I do not have appropriate experience in exporting tables with binary indexes, I propose that (in the interest of time) you log a Service Request with Oracle Metalink (...unless someone else here can provide an answer for you).

If you do obtain an answer from Oracle Tech Support, please do post the resolution here.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[THoey]

Yeah, that is kinda the answer we are getting from every where. No one has really got a lot of experience with these. Love it when developers use something new for the sake of using something new.

She has just got access to Metalink and is going to file a request. I'll let you know what we find.

Thanks for trying Dave.

Terry
**************************
* General Disclaimer - Please read *
**************************
Please make sure your post is in the CORRECT forum, has a descriptive title, gives as much detail to the problem as possible, and has examples of expected results. This will enable me and others to help you faster...

 

[karluk]

Your problem appears to be that you are treating the overflow segment of an IOT as if it were an actual table. You have to find the name of the underlying IOT and do your alter table statement directly on it. To find the name of the IOT, try the following:

select owner, object_name, object_type from dba_objects where object_id=194009;

Take the result of this query and do the alter. For example, suppose your IOT is named THOEY.MY_IOT, then the alter should be

alter table THOEY.MY_IOT move overflow tablespace SMFRPT_DATA;

 

[THoey]

Thanks karl. I'll pass that along to the other DBA and see what she says.

Terry
**************************
* General Disclaimer - Please read *
**************************
Please make sure your post is in the CORRECT forum, has a descriptive title, gives as much detail to the problem as possible, and has examples of expected results. This will enable me and others to help you faster...

 

[THoey]

Her response:

I got that far but it turns out that the IOT_OVERFLOW is an nested IOT_OVERFLOW table and moving nested IOT_OVERFLOW tables to other tablespaces is "not supported".

However, the tables are related to the XML tables.

I copied one XML table to the other tablespace and it is valid. I want the developers to test this table and it's indexes, constraints, etc.

But the XML tables are HUGE, and the undo is running out before I can finish a copy. Even if I do it without logging. On the otherhand, I am competing with users who also are using the undo so I am trying to get in during off-peak times.

I love my job...

Terry
**************************
* General Disclaimer - Please read *
**************************
Please make sure your post is in the CORRECT forum, has a descriptive title, gives as much detail to the problem as possible, and has examples of expected results. This will enable me and others to help you faster...