Re: ALter table move on IOT - TOP

Robert wrote:
>
> Hi all,
>
> I have an 8.1.7.4 DB.
> The database holds a schema for a specific application.
> I recently inherited the administration off the instance.
> The guy who used to do it quit the job.
>
> I saw he used some IOT table's (i'm not sure why, but that's another
> question).

An IOT is a great way of storing data, especially when the bulk of the info is in the 'key' and the supporting data is just a few pieces of info over the access. (Think phone directory - the index is the name, the data is phone number - why force a table when there's really only a small bit of additional info.)

>
> After looking in the database it looks like he isn't the great DBA after
> all. He did create tablespaces for data and for indexes (even on seperate
> disks). But all schemas have SYSTEM as default and all tables ended up in
> the SYSTEM tablespace because the create statement for all tables didn't
> specify another tablespace.

Yup - not a brilliant thing to do. Move it out soon.

>
> I don;t hav any experience with IOT's
> After looking up some docs i understand that it used a normal index
> organisation and a "overflow" tablespace. Seems OK.
>
> But i couldn't find anything about moving IOT's. I would like to move the
> IOT out of SYSTEM.
>
> Can i safely execute
> alter table <table_name> move tablespace <tablespace_name>
> on the table's ??
>

http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96521/tables.htm and click on "Managing Index-Organized Tables"

> What does oracle do with the overflow data ?

The overflow is for the data section - if it starts overwhelming the key/index, you don't want it inline with the index. Received on Tue Dec 02 2003 - 17:14:03 CST