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 thefile_namecolumn to a width of 70 characters. In SQL*Plus,FORMAT A nsets the width of a character column to n.select file_name, ceil(bytes / 1024 / 1024) "size MB":- Selects the file name.
- Divides
bytesby 1024 twice to get MB, andceilrounds 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,likebehaves like=. Enter the name in upper case, because tablespace names are stored in upper case unless they were created with quotes.
Key Points
- Replace
&TSNAMEwith the name of the tablespace you want to inspect. Add%to the value to match several tablespaces, for exampleUNDO%. bytesis the current size of the file. It is not the space used by data. Usedba_free_spacefor free space inside the tablespace.dba_data_fileshas more columns than this query selects.statusshowsAVAILABLEorINVALID, andonline_statusshows values such asONLINE,OFFLINEandRECOVER.autoextensibleandmaxbytesshow whether a file can grow and how far.- Tempfiles of temporary tablespaces do not appear in
dba_data_files. Usedba_temp_filesfor those. user_bytesis the part of the file that can hold user data. The difference betweenbytesanduser_bytesholds file metadata, so a file never offers its fullbytesvalue to segments.file_idis the absolute file number, unique in the whole database.relative_fnois 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
ceilmeans a file of 100.2 MB shows as 101 MB. Treat the figure as a close estimate, and usebytesitself when you need the exact size. - The query needs access to the DBA views, for example the
SELECT_CATALOG_ROLErole or theDBArole.
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_tablespacesfor the exact name and type, and usedba_temp_filesfor temporary tablespaces. - ORA-00942: table or view does not exist: Your account cannot see the
dba_views. Ask for theSELECT_CATALOG_ROLErole, or use theuser_andall_views your account can access. - File names are cut off: Raise the
format a70width, and raiseset linesto 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
bigfileindba_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,statusandonline_status. - DBA_TABLESPACES - Oracle Database 19c Reference, with the
bigfilecolumn 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 nsets 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
- Monitoring Temporary Tablespace Usage in Oracle Database
- Adjusting Segment Growth with MAXEXTENTS in Oracle Database
- Oracle Segments Nearing Max Extents via dba_segments
- Setting a Default Temporary Tablespace in Oracle Database
- Identifying User Tablespaces and Space Usage in Oracle
- Listing Objects in an Oracle Tablespace
- Oracle Tablespaces Needing More Space: SQL Script
- Exploring Datafiles within Oracle Tablespaces
- Understanding Tablespace Usage in Oracle Database
- Understanding User Quotas in Oracle Database
- Creating a Temporary Tablespace in Oracle Database