How to find datafile in which a table is stored in oracle

To find the datafile in which a table is stored in Oracle, you need to trace the storage of the table down to its datafile(s). The storage hierarchy involves the following Oracle components:

1. Table: The actual data container.
2. Segment: A segment is a set of extents allocated for a table, index, or other database objects.
3. Extent: A specific number of contiguous data blocks.
4. Datafile: Physical files on disk that store the database data.

Steps to Find Datafile for a Table

1. Identify the Table’s Segment: The table segment contains the data.
2. Find Extents of the Segment: Extents of the segment show where the data is stored.
3. Locate Datafiles for the Extents: Datafiles contain these extents.

SQL Queries to Find Datafile

Step 1: Identify the Table Segment

First, find the segment name and owner from the table name. Typically, the segment name is the same as the table name.

SELECT owner, segment_name, segment_type
FROM dba_segments
WHERE segment_name = 'YOUR_TABLE_NAME' AND segment_type = 'TABLE';

Replace YOUR_TABLE_NAME with the name of your table.

Step 2: Find Extents of the Segment

Next, find the extents of the identified segment.

SELECT tablespace_name, extent_id, file_id, block_id, blocks
FROM dba_extents
WHERE segment_name = 'YOUR_TABLE_NAME' AND owner = 'YOUR_TABLE_OWNER';

Replace YOUR_TABLE_NAME and YOUR_TABLE_OWNER with the actual table name and owner from the previous step.

Step 3: Locate Datafiles for the Extents

Finally, find the datafile name for each file_id identified in the previous step.

SELECT file_id, file_name
FROM dba_data_files
WHERE file_id IN (
  SELECT file_id
  FROM dba_extents
  WHERE segment_name = 'YOUR_TABLE_NAME' AND owner = 'YOUR_TABLE_OWNER'
);

Replace YOUR_TABLE_NAME and YOUR_TABLE_OWNER with the actual values.

Putting It All Together

Below is a comprehensive script to find the datafiles associated with a given table.

-- Step 1: Identify the Table Segment

SELECT owner, segment_name, segment_type
FROM dba_segments
WHERE segment_name = 'YOUR_TABLE_NAME' AND segment_type = 'TABLE';

-- Use the output to get the owner and segment name

-- Step 2: Find Extents of the Segment

SELECT tablespace_name, extent_id, file_id, block_id, blocks
FROM dba_extents
WHERE segment_name = 'YOUR_TABLE_NAME' AND owner = 'YOUR_TABLE_OWNER';

-- Step 3: Locate Datafiles for the Extents

SELECT file_id, file_name
FROM dba_data_files
WHERE file_id IN (
  SELECT file_id
  FROM dba_extents
  WHERE segment_name = 'YOUR_TABLE_NAME' AND owner = 'YOUR_TABLE_OWNER'
);

Example

If you have a table named EMPLOYEES owned by HR, here is how you can find the datafile:

-- Step 1: Identify the Table Segment

SELECT owner, segment_name, segment_type
FROM dba_segments
WHERE segment_name = 'EMPLOYEES' AND segment_type = 'TABLE';

-- Assume the output is: owner = 'HR', segment_name = 'EMPLOYEES'

-- Step 2: Find Extents of the Segment

SELECT tablespace_name, extent_id, file_id, block_id, blocks
FROM dba_extents
WHERE segment_name = 'EMPLOYEES' AND owner = 'HR';

-- Assume the output includes several file_ids

-- Step 3: Locate Datafiles for the Extents

SELECT file_id, file_name
FROM dba_data_files
WHERE file_id IN (
  SELECT file_id
  FROM dba_extents
  WHERE segment_name = 'EMPLOYEES' AND owner = 'HR'
);

OR

SELECT a.segment_name tablename,

         b.file_name,

         a.tablespace_name,

         a.extent_id,

         a.file_id,

         a.block_id from_block_id,

         a.blocks no_of_blocks,

         a.blocks + a.block_id - 1 to_block_id

    FROM dba_extents a, dba_data_files b

   WHERE     a.segment_name = 'EMPLOYEES'

         AND a.owner = 'HR'

         AND a.file_id = b.file_id

ORDER BY extent_id

This script will list all datafiles that contain the data for the EMPLOYEES table.