Re: Index Organized Tables | What tablespace?

From: Brian Peasland <peasland_at_edcmail.cr.usgs.gov>
Date: Tue, 3 Oct 2000 14:54:25 GMT
Message-ID: <39D9F321.91D32A86@edcmail.cr.usgs.gov>

Kevin,

As you know, you won't be able to see the tablespace name in dba_tables. But you can see it in dba_segments. Remember that the IOT is really a table that's stored as an index. So when you query dba_segments, you are really looking for an index segment. I created an IOT and then queried user_segments to obtain the following:

   SQL> select SEGMENT_NAME,SEGMENT_TYPE,TABLESPACE_NAME from user_segments

   SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME

• ------------------

  ---

  SYS_IOT_TOP_12668 INDEX MISC

You will even see this index if you query user_indexes (or dba_indexes) complete with the tablespace name that it's in.

To know which table this IOT is for, I queried user_indexes and saw the following:

   SQL> select index_name,table_name,tablespace_name from user_indexes;

   INDEX_NAME                     TABLE_NAME      TABLESPACE_NAME
   ------------------------------ ---------------
------------------------------
   SYS_IOT_TOP_12668              IOT_TABLE       MISC

The table that I created, IOT_TABLE is not really a table so for that reason, you will not find the table stored in a tablespace. Since the table is stored as a B-tree index, you will find it in dba_indexes as an index for that table.

HTH,
Brian

kwagner_at_watchmark.com wrote:
>
> Howdy,
> How can I determine what tablespace an IOT has been created in
> (when no overflow exists)? The tablespace_name column in dba_tables is
> null in every occurance of IOT and these objects to not appear in
> dba_segments or dba_indexes.
>
> ???
> Kevin Wagner
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
 

-- 
========================================
Brian Peasland
Raytheons Systems at
  USGS EROS Data Center
These opinions are my own and do not
necessarily reflect the opinions of my 
company!
========================================

Received on Tue Oct 03 2000 - 09:54:25 CDT