Hello,
experts
Why my oracle db tablespace SYSAUX's used percent has been increased fast for 7 days lately ?
And my check processes below,
Firstly,tablespace used percent currently is as follows,
SYS@orcl29> set linesize 300
SYS@orcl29> set pagesize 300
SYS@orcl29> col "Tablespace" FOR a20
SYS@orcl29> col "Status" FOR a10
SYS@orcl29> col "Total MB" FOR a15
SYS@orcl29> col "Used MB" FOR a15
SYS@orcl29> col "Used (%)" FOR a10
SYS@orcl29>
SYS@orcl29> SELECT
2 tablespace_name "Tablespace",
3 d.STATUS "Status",
4 TO_CHAR((a.bytes / 1048576),'99,999,990.900') "Total MB",
5 TO_CHAR(((a.bytes - DECODE(f.bytes, NULL, 0, f.bytes)) / 1048576),'99,999,990.900') "Used MB",
6 TO_CHAR((((a.bytes - DECODE(f.bytes, NULL, 0, f.bytes)) / 1048576)*100) / (a.bytes / 1048576),'999,999.9') "Used (%)"
7 FROM
8 (sys.dba_tablespaces d JOIN sys.sm$ts_avail a USING (tablespace_name))
9 LEFT OUTER JOIN sys.sm$ts_free f USING (tablespace_name)
10 ORDER BY 5;
Tablespace Status Total MB Used MB Used (%)
-------------------- ---------- --------------- --------------- ----------
MIS_BASE ONLINE 1,000.000 2.000 .2
SZD_BASE_V2 ONLINE 647,200.000 60,496.063 9.3
SYSTEM ONLINE 6,144.000 693.625 11.3
UNDOTBS1 ONLINE 25,325.000 4,441.188 17.5
SZD_BAR_V2 ONLINE 3,072.000 562.000 18.3
SZD_PERFORMANCE_V2 ONLINE 2,048.000 842.000 41.1
USERS ONLINE 2,048.000 1,406.813 68.7
SYSAUX ONLINE 161,633.813 161,487.938 99.9
Secondly,there has warnings info in alert log,
......
Fri Nov 10 16:00:23 2017
ORA-1688: unable to extend table SYS.WRH$_LATCH_CHILDREN partition WRH$_LATCH__3701130036_0 by 128 in tablespace SYSAUX
ORA-1688: unable to extend table SYS.WRH$_LATCH_CHILDREN partition WRH$_LATCH__3701130036_0 by 8192 in tablespace SYSAUX
......
Sat Nov 11 12:04:29 2017
alter tablespace sysaux add datafile '/u01/oradata/orcl29/sysaux04.dbf' size 31g
Sat Nov 11 12:06:23 2017
Completed: alter tablespace sysaux add datafile '/u01/oradata/orcl29/sysaux04.dbf' size 31g
Sat Nov 11 12:06:49 2017
alter tablespace sysaux add datafile '/u01/oradata/orcl29/sysaux05.dbf' size 31g
Sat Nov 11 12:08:47 2017
Completed: alter tablespace sysaux add datafile '/u01/oradata/orcl29/sysaux05.dbf' size 31g
......
In the meantime,I added two datafiles in SYSAUX(total size is 64G)
Inexplicably,7 days later(in 2017.11.17),there has also warnings info about insufficient tablespace in SYSAUX,see my check result as follows,
......
Fri Nov 17 22:00:26 2017
ORA-1688: unable to extend table SYS.WRH$_LATCH_CHILDREN partition WRH$_LATCH__3701130036_0 by 128 in tablespace SYSAUX
ORA-1688: unable to extend table SYS.WRH$_LATCH_CHILDREN partition WRH$_LATCH__3701130036_0 by 8192 in tablespace SYSAUX
MMON Flush encountered SYSAUX out of space error(1688).
MMON (emergency) purge of WR snapshots (12429) and older
......
During 7 days period this time,why size 64G has been used up so fast ?
Please help me to analyze this issue weird,thanks a lot.
Best Regards,
Quanwen Zhao