Library Cache Object loaded into SGA

alert日志频繁报错

引用

Mon Jun 29 16:00:06 2009
Memory Notification: Library Cache Object loaded into SGA
Heap size 4365K exceeds notification threshold (2048K)
KGL object name :SYS.ALERT_QUE

其相关trc文件显示

引用

Dump file g:\oracle\product\10.2.0\admin\dota\udump\dota_ora_65148.trc
Mon Jun 29 15:59:36 2009
ORACLE V10.2.0.1.0 - Production vsnsta=0
vsnsql=14 vsnxtr=3
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Windows XP Version V5.1 Service Pack 3, v.5657
CPU                 : 2 - type 586, 2 Physical Cores
Process Affinity    : 0x00000000
Memory (Avail/Total): Ph:271M/2046M, Ph+PgF:1598M/3932M, VA:1270M/2047M
Instance name: dota

Redo thread mounted by this instance: 1

Oracle process number: 19

Windows thread id: 65148, image: ORACLE.EXE (SHAD)


*** SERVICE NAME:(SYS$USERS) 2009-06-29 15:59:36.000
*** SESSION ID:(138.6167) 2009-06-29 15:59:36.000
Memory Notification: Library Cache Object loaded into SGA
Heap size 4358K exceeds notification threshold (2048K)
LIBRARY OBJECT HANDLE: handle=33ccb7cc mutex=33CCB880(0)
name=SYS.ALERT_QUE
hash=ac8b6e1e4ca94b6f517f04aaaa810f64 timestamp=08-30-2005 14:11:40
namespace=QUEU flags=KGHP/TIM/SML/[02000000]
kkkk-dddd-llll=0000-0000-0000 lock=N pin=S latch#=1 hpc=0a98 hlc=0a98
lwt=33CCB828[33CCB828,33CCB828] ltm=33CCB830[33CCB830,33CCB830]
pwt=33CCB80C[33CCB80C,33CCB80C] ptm=33CCB814[33CCB814,33CCB814]
ref=33CCB848[33CCB848,33CCB848] lnd=33CCB854[33CCB854,33CCB854]
  LIBRARY OBJECT: object=2f75a8a4
  type=QUEU flags=EXS/LOC[0005] pflags=[0000] status=VALD load=X
  DEPENDENCIES: count=4 size=16
  dependency#    table reference   handle position flags
  ----------- -------- --------- -------- -------- -------------------
            0 2f75ac60  2f6505f4 33d584fc        0 DEP[01]
            1 2f75ac60  2f650628 33d583a4        0 DEP[01]
            2 2f75ac60  2f65065c 33d5824c        0 DEP[01]
            3 2f75ac60  2f650690 33e5d438        0 DEP[01]
  DATA BLOCKS:
  data#     heap  pointer    status pins change whr alloc(K)  size(K)
  ----- -------- -------- --------- ---- ------ --- -------- --------
      0 28227d50 2f75a93c I/P/A/-/-    0 NONE   00   4358.61     0.00

ALERT_QUE表格主要用于dbconsole或者gird control监视系统警告信息所用，根据alert日志提示，其堆栈大小超过了系统设定值，解决办法如下

引用

SQL> connect / as sysdba

SQL>alter system enable restricted session;

To drop server alert schema.

SQL>@$ORACLE_HOME/rdbms/admin/catnoalr.sql

To re-create tables, sequence, type and queue for server alert

SQL>@$ORACLE_HOME/rdbms/admin/dbmsslrt.sql
SQL>@$ORACLE_HOME/rdbms/admin/catalrt.sql

To recompile the invalid objects

SQL>@$ORACLE_HOME/rdbms/admin/utlrp.sql

SQL> alter system disable restricted session;

或者

引用

SQL> connect / as sysdba

SQL>alter system enable restricted session;

To drop server alert schema.

SQL>@$ORACLE_HOME/rdbms/admin/catnoalr.sql

Rerun catproc.sql

SQL>@$ORACLE_HOME/rdbms/admin/catproc.sql

SQL> alter system disable restricted session;