Exploring Datafiles within Oracle Tablespaces

Description of Oracle Database SQL code for Showing Tablespace Files

This code displays information about the datafiles composing a specific tablespace in your Oracle database. Here's a breakdown of its purpose, components, and key points:

Show thea data files that comprise a tablespace

Sample SQL Command

1set lines 100
2col file_name format a70
3select file_name
4,      ceil(bytes / 1024 / 1024) "size MB"
5from   dba_data_files
6where  tablespace_name like '&TSNAME'

Sample Oracle Output:

 1set lines 100
 2col file_name format a70
 3select file_name
 4,      ceil(bytes / 1024 / 10SQL> SQL>   2  24) "size MB"
 5from   dba_data_files
 6where  tablespace_name like '&TSNAME'
 7/  3    4    5
 8Enter value for tsname: SYSAUX
 9old   4: where  tablespace_name like '&TSNAME'
10new   4: where  tablespace_name like 'SYSAUX'
12FILE_NAME                                                  size MB
13------------------------------------------------------- ----------
14/opt/oracle/oradata/FREE/sysaux01.dbf                       730


  • Identify the individual datafiles associated with a particular tablespace.
  • Display the size of each datafile in megabytes (MB) for easy reference.


  • set lines 100: Increases the number of displayed rows to accommodate potentially many datafiles.
  • col file_name format a70: Sets the file_name column to display text up to 70 characters wide.
  • select file_name, ceil(bytes / 1024 / 1024) "size MB":
    • Selects the file_name and calculates the size in MB by dividing bytes by 1024 twice and performing a ceiling function for rounding up.
    • Aliases the calculated size as "size MB" for clarity.
  • from dba_data_files: Retrieves data from the dba_data_files view which contains information about datafiles.
  • where tablespace_name like '&TSNAME': Filters the results to include only datafiles belonging to the tablespace with the name replaced by &TSNAME.

Key points:

  • This code requires replacing &TSNAME with the actual name of the tablespace you want to analyze.
  • The displayed size represents the raw datafile size and might not reflect the actual usable space within the tablespace.
  • Consider using additional dba_data_files columns like file_type or online_status for more insights into the datafiles.

Insights and explanations:

  • Understanding the datafiles that comprise a tablespace is crucial for tasks like managing files, analyzing performance, and performing backups.
  • Knowing the size and location of datafiles helps plan for future storage needs and identify potential bottlenecks.
  • This code provides a basic overview of tablespace files. For more advanced analysis, consider using additional features like Automatic Storage Management (ASM) or Dynamic Performance Views (DPVs).

Additional notes:

  • This code snippet represents a basic implementation. Additional filtering or calculations might be necessary depending on your specific needs.
  • Always exercise caution when manipulating datafiles directly. Consult your database documentation and seek expert advice if unsure about any changes.

Posts in this Series