Size of Oracle Data Files can be max 32 GB. If you are not chech data files sizes, The system can give an error when size of data files arrive max extent. You can solve this problem with adding a new data file in tablespace. But this state is not a good. You have to recognize before has an error. For solution, you can check data files sizes with e mail notification or thirt party alert applications. Below query give to us that rate of max extent of tablespaces.
Query of Tablespaces Max Size Rate
SELECT tablespace_name,
ROUND (SUM (NVL (max_mb, 0))) allocated,
ROUND (SUM (NVL (used_mb, 0))) used,
ROUND(100*ROUND (SUM (NVL (max_free_mb, 0))) / DECODE (ROUND (SUM (NVL (max_mb, 0))), 0, 1, ROUND (SUM (NVL (max_mb, 0))))) blank_percent
FROM ( SELECT df.tablespace_name,
df.file_name file_name,
df.BYTES / (1024 * 1024) allocated_mb,
(df.BYTES – SUM (NVL (dfs.BYTES, 0))) / (1024 * 1024) used_mb,
SUM (NVL (dfs.BYTES, 0)) / (1024 * 1024) free_space_mb,
NVL (
DECODE (autoextensible,
‘NO’, DF.BYTES,
GREATEST (DF.BYTES, maxbytes)),
0)
/ (1024 * 1024)
max_mb,
DECODE (
autoextensible,
‘YES’, (df.maxbytes / (1024 * 1024)
– (df.BYTES – SUM (NVL (dfs.BYTES, 0)))
/ (1024 * 1024)),
SUM (NVL (dfs.BYTES, 0)) / (1024 * 1024))
max_free_mb,
df.autoextensible
FROM dba_data_files df, dba_free_space dfs
WHERE df.file_id = dfs.file_id(+) AND DF.BYTES / (1024 * 1024) > 0.2
GROUP BY df.tablespace_name,
df.file_name,
df.BYTES,
df.maxbytes,
df.autoextensible)
GROUP BY tablespace_name
UNION ALL
SELECT A.tablespace_name tablespace_name, round(D.mb_total) allocated,
round(SUM (A.used_blocks * D.block_size) / 1024 / 1024) used,
ROUND(100* (round(D.mb_total – SUM (A.used_blocks * D.block_size) / 1024 / 1024)/ round(D.mb_total))) blank_percent
FROM gv$sort_segment A,
(
SELECT B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total
FROM gv$tablespace B, gv$tempfile C
WHERE B.ts#= C.ts# and b.inst_id=c.inst_id
GROUP BY B.name, C.block_size
) D
WHERE A.tablespace_name = D.name
GROUP by A.tablespace_name, D.mb_total
ORDER BY blank_percent;