Exploring Datafiles within Oracle Tablespaces

Show the Datafiles That Make Up an Oracle Tablespace

Capacity planning for an Oracle database starts with knowing which files hold each tablespace and how large they are. A tablespace is a logical container. Its data lives in one or more datafiles on disk, and dba_data_files lists them. The query below takes a tablespace name and returns the path and size in MB of every datafile in it.

Run it before you add space, move files, or plan a backup. It shows how many files a tablespace has and where they sit on the file system or in ASM.

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'
7/

Sample Oracle Output

This is example output for the SYSAUX tablespace on an Oracle Database Free instance. SQL*Plus prompts for the value of &TSNAME and prints the old and new versions of the line it replaces.

1Enter value for tsname: SYSAUX
2old   4: where  tablespace_name like '&TSNAME'
3new   4: where  tablespace_name like 'SYSAUX'
4
5FILE_NAME                                                                 size MB
6---------------------------------------------------------------------- ----------
7/opt/oracle/oradata/FREE/sysaux01.dbf                                         730

The file name column is 70 characters wide because of col file_name format a70. The size is in MB, rounded up to a whole number.

Code Breakdown

  • set lines 100: Sets the line width to 100 characters so a 70-character file name column and the size column fit on one line.
  • col file_name format a70: Sets the file_name column to a width of 70 characters. In SQL*Plus, FORMAT A n sets the width of a character column to n.
  • select file_name, ceil(bytes / 1024 / 1024) "size MB":
    • Selects the file name.
    • Divides bytes by 1024 twice to get MB, and ceil rounds up to the next whole number.
    • Aliases the result as "size MB". The quotes keep the space and the lowercase letters in the heading.
  • from dba_data_files: Reads the data dictionary view that describes the database files.
  • where tablespace_name like '&TSNAME': SQL*Plus asks for a value and substitutes it for &TSNAME. With no wildcard in the value, like behaves like =. Enter the name in upper case, because tablespace names are stored in upper case unless they were created with quotes.

Key Points

  • Replace &TSNAME with the name of the tablespace you want to inspect. Add % to the value to match several tablespaces, for example UNDO%.
  • bytes is the current size of the file. It is not the space used by data. Use dba_free_space for free space inside the tablespace.
  • dba_data_files has more columns than this query selects. status shows AVAILABLE or INVALID, and online_status shows values such as ONLINE, OFFLINE and RECOVER. autoextensible and maxbytes show whether a file can grow and how far.
  • Tempfiles of temporary tablespaces do not appear in dba_data_files. Use dba_temp_files for those.
  • user_bytes is the part of the file that can hold user data. The difference between bytes and user_bytes holds file metadata, so a file never offers its full bytes value to segments.
  • file_id is the absolute file number, unique in the whole database. relative_fno is unique only inside one tablespace. The two match on small databases, but they can differ once the database has more than about 1,023 files.
  • When the file details are not already in the dictionary, Oracle must read the tablespaces and their files to answer the query. If those tablespaces are encrypted, open the keystore (wallet) before you run it.
  • Rounding up with ceil means a file of 100.2 MB shows as 101 MB. Treat the figure as a close estimate, and use bytes itself when you need the exact size.
  • The query needs access to the DBA views, for example the SELECT_CATALOG_ROLE role or the DBA role.

Insights and Best Practices

Look at file count and size together

A tablespace with many small files is harder to manage than one with a few larger files. The output shows both at a glance, so you can spot a tablespace that was grown by adding file after file.

Check autoextend before you add space

Before you add a datafile, check whether the existing ones can still grow. Add autoextensible and maxbytes to the select list. A file that is set to autoextend with a high maxbytes may need no new file at all.

1select file_name
2,      autoextensible
3,      ceil(bytes / 1024 / 1024)    "size MB"
4,      ceil(maxbytes / 1024 / 1024) "max MB"
5from   dba_data_files
6where  tablespace_name like '&TSNAME'
7/

Resize a file before you add one

A smallfile tablespace can grow in two ways: add a new datafile, or make an existing one larger. Resizing keeps the file count down. Use the path from the query output:

1alter database datafile '/u02/oracle/rbdb1/stuff01.dbf' resize 100M;

The same statement can make a file smaller, but not always to the size you ask for. If the file holds data beyond the new size, the database returns an error and the file keeps its size.

Know when the tablespace is a bigfile tablespace

A bigfile tablespace has a single, very large datafile. You cannot add a datafile to it, and you cannot drop its only file. To grow it, resize the tablespace with ALTER TABLESPACE. Its relative file number is always 1024, except on OS/390, where it is 4096. Join to dba_tablespaces to see the type, the file numbers and the space left for user data in one result:

 1select d.file_name
 2,      d.file_id
 3,      d.relative_fno
 4,      t.bigfile
 5,      ceil(d.bytes / 1024 / 1024)      "size MB"
 6,      ceil(d.user_bytes / 1024 / 1024) "user MB"
 7from   dba_data_files d
 8join   dba_tablespaces t
 9on     t.tablespace_name = d.tablespace_name
10where  d.tablespace_name like '&TSNAME'
11/

A bigfile value of YES means the fix for a full tablespace is a resize, not a new file. A value of NO means a smallfile tablespace, which can take more files.

Check lost write protection in 19c

In Oracle Database 19c, dba_data_files also has a lost_write_protect column with the values ENABLED, OFF and SUSPEND. Protection can be set for one datafile without the others in the same tablespace. When it is set for a whole tablespace, it covers every file in it, including files added later. Read the column per file when you need to know which files are covered.

Total the size per tablespace

When you only need the total, group the same view by tablespace. The result is the allocated size of every tablespace in the database, largest first.

1select tablespace_name
2,      count(*)                            "files"
3,      ceil(sum(bytes) / 1024 / 1024)      "total MB"
4from   dba_data_files
5group  by tablespace_name
6order  by sum(bytes) desc
7/

Compare the list with the per-file output. A tablespace that holds most of the space in a single file is a different risk from one spread over many files: the first depends on one disk path, the second has more files to track in backups and moves.

Record the output with your change tickets

Save the per-file output before and after any change to a tablespace. If a resize or a file move goes wrong, the saved list shows the original paths and sizes, which makes the rollback a copy job instead of a search.

Use V$DATAFILE when the database is only mounted

v$datafile shows datafile information read from the control file, not from the data dictionary. Use it as a cross-check against dba_data_files, or when the data dictionary is not available.

Container databases

In a multitenant database, dba_data_files shows the files of the container you are connected to. Connect to the pluggable database to see its files, and connect to the root to see the root's own files. Check which container your session uses before you read the output, so you do not mistake one container's list for another's.

When to Use This Check

  • Before you add a datafile or resize one.
  • When you plan to move datafiles to new storage.
  • Before a backup or restore, to confirm which files belong to a tablespace.
  • After a space alert on a tablespace, to see how its files are laid out.
  • When you document a database for a migration.

Troubleshooting Common Issues

  • No rows returned: The tablespace name is wrong, or it is a temporary tablespace. Check dba_tablespaces for the exact name and type, and use dba_temp_files for temporary tablespaces.
  • ORA-00942: table or view does not exist: Your account cannot see the dba_ views. Ask for the SELECT_CATALOG_ROLE role, or use the user_ and all_ views your account can access.
  • File names are cut off: Raise the format a70 width, and raise set lines to match.
  • A file shows status INVALID: The file number is not in use, for example when the tablespace it belonged to was dropped.
  • A resize to a smaller size fails: The file holds data past the size you asked for. Pick a larger target size.
  • ALTER TABLESPACE ADD DATAFILE fails: Check bigfile in dba_tablespaces. A bigfile tablespace cannot take a second file, so resize it instead.

References

  • DBA_DATA_FILES - Oracle Database 19c Reference, with every column of the view, including file_name, bytes, status and online_status.
  • DBA_TABLESPACES - Oracle Database 19c Reference, with the bigfile column that shows whether a tablespace has a single datafile.
  • Managing Data Files and Temp Files - Oracle Database 19c Administrator's Guide, on file numbers, resizing data files and bigfile tablespace limits.
  • DBA_TEMP_FILES - Oracle Database 19c Reference, the matching view for tempfiles.
  • V$DATAFILE - Oracle Database 19c Reference, datafile information read from the control file.
  • COLUMN command - SQL*Plus User's Guide, how FORMAT A n sets the width of a character column.
  • Automatic Storage Management (ASM) in Oracle Database 10g - ORACLE-BASE walkthrough of ASM, useful when datafile paths start with a disk group name.

Posts in this series