Oracle: List Datafiles, Tempfiles, and Logfiles with Sizes

calendar Mar 26, 2024 / · 8 min read · oracle storage v$datafile v$tempfile v$logfile v$log v$controlfile dba_free_space  ·
Share on: linkedin copy

Oracle: List Datafiles, Tempfiles, and Logfiles with Sizes

Purpose

Capacity planning starts with one number: how much disk the database files use. Oracle spreads that answer over several dynamic performance views. V$DATAFILE lists the datafiles, V$TEMPFILE the tempfiles, V$LOGFILE the redo log members and V$CONTROLFILE the control files. No single view holds all four, and the redo log sizes sit in a fifth view, V$LOG.

The script below joins these views into one list of files with a size for each, and a second query adds up the space by file type. A DBA runs it before a storage request, before a copy or restore to a new host, and when a mount point fills up and someone must say which files sit on it.

The size columns show how big each file is, not how full it is. For fullness, the script ends with a free-space query on DBA_FREE_SPACE.

Code

 1set lines 140 pages 999
 2col file_type format a10
 3col name format a80
 4col mb format 999,999,990.9
 5
 6select file_type
 7, name
 8, round(bytes / 1024 / 1024, 1) mb
 9from (select 'DATAFILE' file_type, name, bytes
10      from v$datafile
11      union all
12      select 'TEMPFILE', name, bytes
13      from v$tempfile
14      union all
15      select 'REDO LOG', lf.member, l.bytes
16      from v$logfile lf
17      , v$log l
18      where lf.group# = l.group#
19      union all
20      select 'CONTROL', name, block_size * file_size_blks
21      from v$controlfile)
22order by file_type, name
23/

A second query totals the space by file type and adds a grand total.

 1select nvl(file_type, 'TOTAL') file_type
 2, count(*) files
 3, round(sum(bytes) / 1024 / 1024, 1) mb
 4from (select 'DATAFILE' file_type, bytes from v$datafile
 5      union all
 6      select 'TEMPFILE', bytes from v$tempfile
 7      union all
 8      select 'REDO LOG', l.bytes
 9      from v$logfile lf
10      , v$log l
11      where lf.group# = l.group#
12      union all
13      select 'CONTROL', block_size * file_size_blks from v$controlfile)
14group by rollup(file_type)
15/

A third query shows free space per tablespace.

1select tablespace_name
2, round(sum(bytes) / 1024 / 1024, 1) free_mb
3from dba_free_space
4group by tablespace_name
5order by tablespace_name
6/

Code Breakdown

SQL*Plus settings

  • set lines 140 pages 999 sets two limits. lines is the number of characters on one line before SQL*Plus wraps. pages is the number of lines on a page before the headings print again, so 999 keeps the report in one block.
  • col name format a80 sets the display width of the name column to 80 characters, enough for most file paths. col mb format 999,999,990.9 prints the size with thousands separators and one decimal place.

The first query

  • The inline view stacks four selects with UNION ALL. Each select returns a type label, a file name and a size in bytes, so the columns line up. UNION ALL skips the duplicate check that UNION does, which is right here because no file appears twice.
  • V$DATAFILE.BYTES is the current file size in bytes. V$TEMPFILE.BYTES is the size taken from the file header.
  • A redo log group has one or more members, and every member is an identical copy. V$LOGFILE has the member names but no size, and V$LOG has the size (BYTES) but one row per group. The join on GROUP# gives each member the size of its group, so a group with two members shows two lines and counts twice. That is close to what the files use on disk; for a datafile, the exact length is BYTES + BLOCK1_OFFSET, a small header offset more.
  • V$CONTROLFILE has no byte column. It has BLOCK_SIZE and FILE_SIZE_BLKS, so the size in bytes is the product of the two.
  • round(bytes / 1024 / 1024, 1) converts bytes to megabytes with one decimal.

The second query

  • It repeats the same union without the file names. GROUP BY ROLLUP(file_type) adds one extra row where file_type is null, and nvl renames that row TOTAL.
  • count(*) shows how many files each type holds. The REDO LOG count is the number of members, not the number of groups.

The third query

  • DBA_FREE_SPACE has one row per free extent in a tablespace. Summing BYTES by TABLESPACE_NAME gives the free space in each tablespace.

Key Points

  • In a multitenant database, the views carry a CON_ID column that names the container each row belongs to. Add con_id to the select lists when you need to split the totals by container.
  • V$DATAFILE.BYTES is 0 for a file the database cannot access, so a zero in the report is a signal and not a true size.
  • For temporary tablespaces, use DBA_TEMP_FREE_SPACE, which shows the total allocated and free space in each temporary tablespace (available since Oracle 11g).
  • If a datafile or the whole tablespace is offline in a locally managed tablespace, DBA_FREE_SPACE shows no extent information for it. A file with no free space has no row at all, so a tablespace can be missing from the third query when it is full.
  • The total counts every redo log member and every control file copy. This is roughly the disk space the files use, not the amount of data stored.
  • Size in the view is the file size. A datafile with AUTOEXTEND on can grow, and its limit is in DBA_DATA_FILES.MAXBYTES, not in these views.

Insights and Best Practices

Know what each number is

The datafile total is often the largest part, but it is not the data volume. It includes free space inside the files, and it includes the undo and SYSTEM and SYSAUX tablespaces. To see how much of it holds objects, compare it with the sum of DBA_SEGMENTS.BYTES or with the free-space query.

The redo log total changes when someone adds or drops a group or a member. A change here is a change to the redo configuration, so find out who made it.

Check that redo and control files are multiplexed

Every database should have at least two control files, each on a different physical disk. Redo log groups should have members on different disks too, so one failed disk does not take away the only copy. The first query shows this at once. If the CONTROL section lists one file, or the paths of two copies share a mount point, the protection is weaker than it looks.

Keep a baseline

Spool the output to a dated file once a month. Two reports show which file grew and when a new file appeared.

Use the SQL*Plus spool command with a file name that holds the date, run the first query, then spool off.

Convert to gigabytes for reports

For large databases, replace / 1024 / 1024 with / 1024 / 1024 / 1024 and widen the mb column format. Megabytes are good for a list of files. Gigabytes read better in the total row.

When to Run This

  • Before a storage request, to state the current footprint with a number
  • Before cloning or moving the database, to size the target host
  • When a mount point fills up, to list the files on it
  • After adding datafiles or resizing groups, to confirm the change
  • During an audit of redo log and control file multiplexing
  • After a restore, to confirm that every file came back at the expected size

Troubleshooting Common Issues

If the third query fails with ORA-00942: table or view does not exist, the account cannot read DBA_FREE_SPACE. Connect as a DBA or ask for read access to the data dictionary views. The same applies if one of the V$ views is not visible to the account.

If V$DATAFILE shows a size of 0 for a file, the file is not accessible. Check the operating system path and permissions, then the status in V$DATAFILE. The STATUS column of V$DATAFILE lists OFFLINE, ONLINE, SYSTEM, RECOVER and SYSOFF; a value other than ONLINE or SYSTEM needs attention.

If the redo log total looks too high, remember that each member is counted. Three groups with two members of 200 MB each give 1,200 MB, not 600 MB.

If the totals do not match the disk usage reported by the operating system, look for files the views do not list: archived redo logs, backup pieces, trace files and the alert log. They live outside these views.

References

Posts in this series