RSS

Tag Archives: How to know data objects “Segments” in a given data file

Data Objects in a data file in Oracle

How to know, find out data objects “Segments” in a given data file??

SELECT <distinct> a.segment_name,
 a.SEGMENT_TYPE,
 a.TABLESPACE_NAME,
 a.file_id,
 b.file_name Datafile_name
 FROM dba_extents a, dba_data_files b
 WHERE a.file_id = b.file_id
 AND b.file_id = <data file id>;

 

For the opposite purpose where you want to know in with data file a data object stored, you can use the following query:

SELECT a.segment_name, a.file_id, b.file_name Datafile_name
 FROM dba_extents a, dba_data_files b
 WHERE a.file_id = b.file_id AND a.segment_name = '<object_name>';
 
 

Tags: , , ,