Hi,
Need your suggestion on tablespace issue.
Last week we got an exception on tablespace issue :
java.sql.SQLException: ORA-01653: unable to extend table SYSTEM.DEF$_AQCALL by 92169 in tablespace SYSTEM
We have resize the SYSTEM tablespace by 500 MB to resolve the problem and later we add datafile to it which will
carry for several years.
But,
Is there a better way to monitor the tablespace, through which we can prevent this error in future ?
Is there any way to check the current and next sizes for tables against the free space available ?
Currently we monitoring the DB Space in the bellow way.
select a.tablespace_name name, b.tablespace_name dummy, sum_alloc_bytes bytes,
sum_alloc_bytes - sum_free_bytes used, sum_free_bytes free,
100*(sum_alloc_bytes - sum_free_bytes)/sum_alloc_bytes pct_used
from
(select tablespace_name, sum(bytes) sum_alloc_bytes
from dba_data_files
group by tablespace_name) b,
(select tablespace_name,
max(bytes) as biggest_free_bytes,
count(blocks) as num_free_extents,
sum(bytes) as sum_free_bytes
from dba_free_space
group by tablespace_name) a
where a.tablespace_name = b.tablespace_name;
FREE - Free space by Tablespace
Tablespace Name Total Bytes Used Free Used
---------------- ------------------ --------------- ---------------- ------
CSMIP1_DATA1 22,385,065,984 20,149,501,952 2,235,564,032 90.0
CSMIP1_DATA2 2,147,549,184 1,906,376,704 241,172,480 88.8
CSMIP1_IDX1 6,442,647,552 5,525,143,552 917,504,000 85.8
CSMIP1_IDX2 4,295,098,368 3,012,689,920 1,282,408,448 70.1
CSMIP1_RBS 2,147,549,184 738,263,040 1,409,286,144 34.4
CSMIP1_TOOLS 2,147,549,184 122,748,928 2,024,800,256 5.7
CSMIP_DATA3 2,147,549,184 65,536 2,147,483,648 .0
DBOR_MV 1,073,741,824 275,251,200 798,490,624 25.6
SYSTEM 2,097,152,000 1,544,093,696 553,058,304 73.6
------------------ --------------- ----------------
sum 44,883,902,464 33,274,134,528 11,609,767,936