Library Cache Object loaded into SGA

alert日志频繁报错
[quote]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 [/quote]
其相关trc文件显示
[quote]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[/quote]

ALERT_QUE表格主要用于dbconsole或者gird control监视系统警告信息所用，根据alert日志提示，其堆栈大小超过了系统设定值，解决办法如下
[quote]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; [/quote]
或者
[quote]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; [/quote]