Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question.

Asked: June 24, 2020 - 2:45 pm UTC

Last updated: June 25, 2020 - 1:03 pm UTC

Version: 9.2.0.6.0

Viewed 1000+ times

You Asked

Hi Tom,

How can I find out which data file the index is stored in?
What SQL statement can I use to do it?

and Chris said...

Each index (partition) belongs to a tablespace.

Each tablespace can have many files.

So an index could be stored over many files.

You can find the tablespace for an index with:
select tablespace_name 
from   dba_indexes
where  index_name = :inx;


If the tablespaces has many files, you can see which the index by joining dba_extents to dba_data_files:

create table t ( c1 constraint pk primary key ) as 
  select level from dual
  connect by level <= 100;

select de.tablespace_name, ddf.file_id, ddf.file_name
from   dba_extents de
join   dba_data_files ddf
on     de.file_id = ddf.file_id
where  owner = 'CHRIS'
and    segment_name = 'PK';

TABLESPACE_NAME   FILE_ID FILE_NAME                                                                              
USERS                  12 +DATA/DBAAS19C...

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Osama, June 29, 2020 - 1:30 pm UTC

Hi Tom,

Many thanks for your response, this is exactly what I need

Regards,

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database