Skip to Main Content

Breadcrumb

Dev Live Dev Intro

This month we are celebrating Developers at AskTOM. We welcome Developers of all levels of experience to join us at our FREE Developer Live events coming in August. Just click on the left to register today! If you are brand new to Database Technology, then we also have got you covered. Just click on the right for your comprehensive FREE training program to kick start your Oracle Database Development journey!

Question and Answer

Chris Saxon

Thanks for the question.

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

Answered by: Chris Saxon - Last updated: June 25, 2020 - 1:03 pm UTC

Category: Database Administration - Version: 9.2.0.6.0

Viewed 100+ 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 we 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...

and you rated our response

  (1 rating)

Reviews

June 29, 2020 - 1:30 pm UTC

Reviewer: Osama

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