srcnblgc

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.