Skip to content

How to List Tables and Indexes in a Data File

  • by

Normally, we would like to know what objects in a tablespace for better managing object 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 the usage of a data file, we need to drill down into the detailed.

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.