Re: Index only table -- how to determine datafile

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Mon, 01 Jun 1998 12:28:54 GMT
Message-ID: <35729e67.783476@192.86.155.100>

A copy of this was sent to johnvue_at_gte.net (if that email address didn't require changing) On Mon, 01 Jun 1998 04:15:54 GMT, you wrote:

>Since there are no ROWIDs in an index-only table, how would one find
>out what datafiles an index-only table spans? Can't use the "select
>distinct substr(rowid, ..., ...)" trick.

SQL> desc sys.dba_extents;

 Name                            Null?    Type
 ------------------------------- -------- ----
 OWNER                                    VARCHAR2(30)
 SEGMENT_NAME                             VARCHAR2(81)
 PARTITION_NAME                           VARCHAR2(30)
 SEGMENT_TYPE                             VARCHAR2(17)
 TABLESPACE_NAME                          VARCHAR2(30)
 EXTENT_ID                       NOT NULL NUMBER
 FILE_ID                         NOT NULL NUMBER
 BLOCK_ID                        NOT NULL NUMBER
 BYTES                                    NUMBER
 BLOCKS                          NOT NULL NUMBER
 RELATIVE_FNO                    NOT NULL NUMBER

select * from there where segment_name = '<IOT Name>' will tell you.

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA

http://govt.us.oracle.com/ -- downloadable utilities

Opinions are mine and do not necessarily reflect those of Oracle Corporation

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Mon Jun 01 1998 - 07:28:54 CDT