SQL> l
1 select s.event, count(s.event)
2 from v$session s, v$process p
3 where s.PADDR = p.ADDR
4 group by s.event
5* order by 2 desc
SQL>
EVENT COUNT(S.EVENT)
---------------------------------------------------------------- --------------
enq: HW - contention 1300
PL/SQL lock timer 100
rdbms ipc message 26
Space Manager: slave idle wait 7
DIAG idle wait 2
Streams AQ: qmn slave idle wait 2
Streams AQ: qmn coordinator idle wait 1
Streams AQ: waiting for time management or cleanup tasks 1
VKTM Logical Idle Wait 1
SQL*Net message to client 1
smon timer 1
pmon timer 1
SQL*Net message from client 1
log file parallel write 1
14 rows selected.
SQL>
由以上可见enq: HW - contention等待事件严重
TABLESPACE_NAME SIZE_G FREE_G USED_PCT
------------------------------ --------------- --------------- ----------
SYSTEM 0.75 0.01 98.81%
SOE 91.19 3.72 95.92%
SYSAUX 0.74 0.03 95.90%
EXAMPLE 0.34 0.04 89.58%
USERS 0.00 0.00 81.25%
UNDOTBS1 30.77 13.56 55.91%
6 rows selected.
查询表空间发现表空间使用率很高
扩展表空间
SQL> alter database datafile '/oradata/ccictdb/ccictdb/soe.dbf' resize 150g;
Database altered.
SQL>