OracleStorage

Datafiles with size and autoextend limits

Every datafile, its current size, whether it can grow, and how far. Files with AUTOEXTEND NO in a busy tablespace are the ones that page you at night.

Not yet verified. How scripts are tested

1CLEAR COLUMNS2SET LINESIZE 180 PAGESIZE 200 TRIMOUT ON TAB OFF3COLUMN tablespace_name HEADING "TABLESPACE" FORMAT A204COLUMN file_name       HEADING "FILE"       FORMAT A705COLUMN size_gb         HEADING "SIZE GB"    FORMAT 99,990.996COLUMN autoextensible  HEADING "AUTO"       FORMAT A47COLUMN max_gb          HEADING "MAX GB"     FORMAT 99,990.998COLUMN status          FORMAT A99 10SELECT tablespace_name, file_name,11       ROUND(bytes / POWER(1024, 3), 2) AS size_gb,12       autoextensible,13       ROUND(maxbytes / POWER(1024, 3), 2) AS max_gb,14       status15FROM dba_data_files16ORDER BY tablespace_name, file_name;

Save it as ora-datafiles.sql and run it with SQL> @ora-datafiles.

Open in denrepoRelated tool: Tablespace runway and datafiles

Helps with

• ORA-01110: data file %s: '%s'
• ORA-01113: file %s needs media recovery
• ORA-01157: cannot identify/lock data file %s - see DBWR trace file
• ORA-01653: unable to extend table %s.%s by %s in tablespace %s
• ORA-01654: unable to extend index %s.%s by %s in tablespace %s

Part of these runbooks

• Space emergency

More Oracle scripts: Storage

• Datafile usage with a bar graphEvery datafile with its size, used space, max size and a ten-character usage bar (X = used, - = free). Free_MB is the largest free extent in the…
• Database size three ways: allocated, used and totalAllocated datafile size, space actually used by segments, and the overall footprint including temp files, online redo logs and control files. All in…
• Database size summary: total, used and freeOne line with the database name and its total size (datafiles, temp files and redo logs), used space and free space, rounded to whole GB.
• Size of one schemaTotal segment size in GB for the schema you enter.
• Tablespace usageUsed and free space in GB against the maximum each tablespace can autoextend to, with its type (permanent, temporary or undo), fullest first.
• Top 20 largest segmentsThe biggest tables, indexes, LOBs and partitions in the database.