Skip to content
Home » Oracle » How to List Tables in Datafile

How to List Tables in Datafile

Normally, we would like to list tables in a tablespace for better managing segment allocation. In fact, a tablespace is just a logical collection of data files, which does not physically store tables and indexes, it's data files do the physical storing.

To know what segments in a data file, we need to drill down into details.

SQL> column owner format a10;
SQL> column "SIZE(MB)" format 999.99;
SQL> select e.owner, e.segment_name, sum(e.bytes)/1024/1024 "SIZE(MB)" from dba_extents e, dba_data_files f where e.file_id = f.file_id and f.file_name = '/u01/app/oracle/oradata/ORCL/example01.dbf' group by e.owner, e.segment_name order by 3 desc;

OWNER      SEGMENT_NAME                   SIZE(MB)
---------- ------------------------------ --------
SH         SALES                            128.00
SH         CUSTOMERS                         13.00
SH         SALES_CUST_BIX                     4.56

As you can see, we specify the absolute path of the data file to retrieve the information.

Leave a Reply

Your email address will not be published. Required fields are marked *