One of the tasks a DBA undertakes is to monitor the amount of space consumed by the database. If a tablespace becomes spacebound than the database will freeze up as data cannot be written down to disk.
Check Tablespace Free Space
The below script will identify the available free space for each tablespace.
SELECT a.tablespace_name
, a.fileCount
, a.MaxTSBytes
, b.TSDataBytes
, a.MaxTSBytes - b.TSDataBytes FreeSpace
, ROUND((a.TotalFilesizeMB/a.MaxTSBytes)*100,2) DFAlloc
, ROUND((b.TSDataBytes/a.MaxTSBytes)*100,2) AS DATAAlloc
FROM
(SELECT tablespace_name
, COUNT(*) filecount
, SUM(CASE WHEN maxbytes > bytes THEN maxbytes/1048576 ELSE bytes/1048576 end) AS MaxTSBytes
, SUM(bytes)/1048576 TotalFilesizeMB
FROM dba_data_files GROUP BY tablespace_name) a,
(SELECT tablespace_name
, SUM(bytes)/(1048576) AS TSDataBytes
FROM dba_extents GROUP BY tablespace_name) b
WHERE a.tablespace_name = b.tablespace_name(+)
ORDER BY 1
/
TABLESPACE_NAME FILECOUNT MAXTSBYTES TSDATABYTES FREESPACE DFALLOC DATAALLOC
--------------- --------- ---------- ----------- --------- ------- ---------
INTERFACE 13 22250 21906.4375 343.5625 100 98.46
LOAD 6 8550 7629.125 920.875 100 89.23
MLOG 1 1250 617.5625 632.4375 100 49.41
NOTES 3 4200 3728.6875 471.3125 100 88.78
SYSTEM 1 500 406.625 93.375 100 81.33
UNDO 3 4500 323.085938 4176.91406 100 7.18
XDB 1 100 44.9375 55.0625 100 44.94
6 rows selected.
Query Datafile Sizes
The below script queries how much free space is available per datafile. In this example only the system tablespace has been queried:
SELECT a.file_id,
SUBSTR(a.tablespace_name,1,10) tablespace,
a.autoextensible,
(CASE WHEN a.maxbytes > a.bytes THEN a.maxbytes/1048576 ELSE bytes/1048576 END) MaxFileSize,
a.bytes/1048576 filesize,
(CASE WHEN a.maxbytes > a.bytes THEN a.maxbytes/1048576 ELSE bytes/1048576 END) - nvl(b.usedbytes,0) free_Mb,
round(100*(1-(nvl(b.Usedbytes,0))/(CASE WHEN a.maxbytes > a.bytes THEN a.maxbytes/1048576 else bytes/1048576 END)),2) "%_FREE",
a.file_name
FROM dba_data_files a,
(SELECT file_id
, sum(bytes)/1048576 Usedbytes
FROM dba_extents GROUP BY file_id) b
WHERE a.file_id = b.file_id(+)
AND a.tablespace_name = '&tablespace_name'
ORDER BY
a.tablespace_name,
a.file_id
/
FILE_ID TABLESPACE AUT MAXFILESIZE FILESIZE FREE_MB %_FREE FILE_NAME
------- ---------- --- ----------- -------- ------- ------ ---------
1 SYSTEM NO 500 500 93.375 18.68 /u05/oracle/oradata/SYSTM_01.dbf
Once you have found the file_id for the datafile in the tablespace that needs expanding then you can increase the size of the tablespace.
