描述:数据库有大量的查询,很多查询通过临时表进行,速度很慢,而且归档日期产生的特别快,一天会产生20G,一般正常情况一天只会产生5G左右.
下面贴部分日志..alertDEPT.log(ORA-00600前后的一段)Sat Jun 24 23:08:02 2006
ARCH shutting down
Sat Jun 24 23:08:02 2006
ARCH shutting down
ARC1: Archival stopped
Sat Jun 24 23:08:02 2006
ARC0: Archival stopped
Sat Jun 24 23:08:02 2006
Thread 1 closed at log sequence 10263
Successful close of redo thread 1.
Sat Jun 24 23:08:02 2006
Completed: alter database close normal
Sat Jun 24 23:08:02 2006
alter database dismount
Completed: alter database dismount
ARCH: Archiving is disabled
Shutting down archive processes
Archiving is disabled
Archive process shutdown avoided: 0 active
ARCH: Archiving is disabled
Shutting down archive processes
Archiving is disabled
Archive process shutdown avoided: 0 active
Sat Jun 24 23:08:07 2006
Errors in file d:\oracle\admin\dept\udump\DEPT_ora_2964.trc:
ORA-00600: internal error code, arguments: [723], [10332], [10332], [memory leak], [], [], [], []Dump file d:\oracle\admin\dept\bdump\alert_dept.log
Sun Jun 25 00:13:23 2006
ORACLE V9.2.0.1.0 - Production vsnsta=0
vsnsql=12 vsnxtr=3
Windows 2000 Version 5.2 , CPU type 586
Sun Jun 25 00:13:23 2006
Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
SCN scheme 2
Using log_archive_dest parameter default value
LICENSE_MAX_USERS = 0
SYS auditing is disabled
Starting up ORACLE RDBMS Version: 9.2.0.1.0.
System parameters with non-default values:
processes = 150
timed_statistics = TRUE
shared_pool_size = 369098752
sga_max_size = 697900916
large_pool_size = 33554432
java_pool_size = 33554432
control_files = d:\oracle\oradata\dept\CONTROL01.CTL, d:\oracle\oradata\dept\CONTROL02.CTL, d:\oracle\oradata\dept\CONTROL03.CTL
db_block_size = 8192
db_cache_size = 201326592
compatible = 9.2.0.0.0
log_archive_start = TRUE
log_archive_dest_1 = LOCATION=E:\oracle\archivelog\dept MANDATORY REOPEN
db_file_multiblock_read_count= 16
fast_start_mttr_target = 300
undo_management = AUTO
undo_tablespace = UNDOTBS2
undo_retention = 10800
remote_login_passwordfile= EXCLUSIVE
db_domain =
instance_name = dept
dispatchers = (PROTOCOL=TCP) (SERVICE=deptXDB)
job_queue_processes = 10
hash_join_enabled = TRUE
background_dump_dest = d:\oracle\admin\dept\bdump
user_dump_dest = d:\oracle\admin\dept\udump
core_dump_dest = d:\oracle\admin\dept\cdump
sort_area_size = 524288
db_name = dept
open_cursors = 300
star_transformation_enabled= FALSE
query_rewrite_enabled = FALSE
pga_aggregate_target = 209715200
aq_tm_processes = 1DEPT_ora_2964.trc(内容)
******************************************************
*** 2006-06-24 23:08:07.000
ksedmp: internal or fatal error
ORA-00600: internal error code, arguments: [723], [10332], [10332], [memory leak], [], [], [], []
Current SQL information unavailable - no SGA.
----- Call Stack Trace -----
calling call entry argument values in hex
location type point (? means dubious value)
-------------------- -------- -------------------- ----------------------------
_ksedmp+147 CALLrel _ksedst+0
_ksfdmp.108+e CALLrel _ksedmp+0 3
_kgeriv+89 CALLreg 00000000 1B4148 3
_kgesiv+4e CALLrel _kgeriv+0 1B4148 0 2D3 3 3D6FC34
_ksesic3+3b CALLrel _kgesiv+0 1B4148 0 2D3 3 3D6FC34 2D3 3
3D6FC34
__VInfreq__ksmdpg+e CALLrel _ksesic3+0 2D3 0 285C 0 285C 1 B 25D84BC
f
_opidcl+1ea CALLrel _ksmdpg+0
_opidrv+3bf CALLrel _opidcl+0 1BA298 0
_sou2o+19 CALLrel _opidrv+0
_opimai+150 CALLrel _sou2o+0 3D6FE24 32 0 0
_BackgroundThreadSt CALLrel _opimai+0
art@4+11a
77E1A98D CALLreg 00000000
--------------------- Binary Stack Dump ---------------------
========== FRAME [1] (_ksedmp+147 -> _ksedst+0) ==========
Dump of memory from 0x03D6FB24 to 0x03D6FB9C
3D6FB20 03D6FB9C 00691AB4 00000000 [......i.....]
3D6FB30 00000000 00000000 00000000 001B422C [............,B..]
3D6FB40 00000000 00000000 03D6FB64 00000001 [........d.......]
3D6FB50 00000003 00000000 00000000 00000003 [................]
3D6FB60 03D6FC34 00333237 001B4148 0048E6C8 [4...723.HA....H.]
3D6FB70 018FD140 001B4148 00000000 018FD140 [@...HA......@...]
3D6FB80 001B3FC0 03D6FB30 03D6FBC8 03D6FCF0 [.?..0...........]
3D6FB90 014122B0 025AFB94 FFFFFFFF [."A...Z.....]
========== FRAME [2] (_ksfdmp.108+e -> _ksedmp+0) ==========
Dump of memory from 0x03D6FB9C to 0x03D6FBA8
3D6FB90 03D6FBA8 [....]
3D6FBA0 007C7883 00000003 [.x|.....]
========== FRAME [3] (_kgeriv+89 -> 00000000) ==========
Dump of memory from 0x03D6FBA8 to 0x03D6FBC8
3D6FBA0 03D6FBC8 02652007 [..... e.]
3D6FBB0 001B4148 00000003 00000012 00000000 [HA..............]
3D6FBC0 000002D3 001B4148 [....HA..]
========== FRAME [4] (_kgesiv+4e -> _kgeriv+0) ==========
Dump of memory from 0x03D6FBC8 to 0x03D6FBF4
3D6FBC0 03D6FBF4 026105DB [......a.]
3D6FBD0 001B4148 00000000 000002D3 00000003 [HA..............]
3D6FBE0 03D6FC34 00000000 001B3FC0 03D6FC34 [4........?..4...]
3D6FBF0 001B3FC0 [.?..]
========== FRAME [5] (_ksesic3+3b -> _kgesiv+0) ==========
Dump of memory from 0x03D6FBF4 to 0x03D6FC28
3D6FBF0 03D6FC28 00698B6C 001B4148 [(...l.i.HA..]
3D6FC00 00000000 000002D3 00000003 03D6FC34 [............4...]
3D6FC10 000002D3 00000003 03D6FC34 007CABDC [........4.....|.]
3D6FC20 001B4008 025D84BC [.@....].]
========== FRAME [6] (__VInfreq__ksmdpg+ef -> _ksesic3+0) ==========
Dump of memory from 0x03D6FC28 to 0x03D6FCA4
3D6FC20 03D6FCA4 01889CA4 [........]
3D6FC30 000002D3 00000000 0000285C 00000000 [........\(......]
3D6FC40 0000285C 00000001 0000000B 025D84BC [\(............].]
3D6FC50 03DF6D68 00000000 00000000 03DF6D68 [hm..........hm..]
3D6FC60 03D6FC88 02653C6A 03DF6FA0 001B4148 [....j<e..o..HA..]
3D6FC70 00000000 00000000 001B4224 001B3FC0 [........$B...?..]
3D6FC80 001B4200 001B66F4 001B4010 001B66E4 [[email protected]..]
下面贴部分日志..alertDEPT.log(ORA-00600前后的一段)Sat Jun 24 23:08:02 2006
ARCH shutting down
Sat Jun 24 23:08:02 2006
ARCH shutting down
ARC1: Archival stopped
Sat Jun 24 23:08:02 2006
ARC0: Archival stopped
Sat Jun 24 23:08:02 2006
Thread 1 closed at log sequence 10263
Successful close of redo thread 1.
Sat Jun 24 23:08:02 2006
Completed: alter database close normal
Sat Jun 24 23:08:02 2006
alter database dismount
Completed: alter database dismount
ARCH: Archiving is disabled
Shutting down archive processes
Archiving is disabled
Archive process shutdown avoided: 0 active
ARCH: Archiving is disabled
Shutting down archive processes
Archiving is disabled
Archive process shutdown avoided: 0 active
Sat Jun 24 23:08:07 2006
Errors in file d:\oracle\admin\dept\udump\DEPT_ora_2964.trc:
ORA-00600: internal error code, arguments: [723], [10332], [10332], [memory leak], [], [], [], []Dump file d:\oracle\admin\dept\bdump\alert_dept.log
Sun Jun 25 00:13:23 2006
ORACLE V9.2.0.1.0 - Production vsnsta=0
vsnsql=12 vsnxtr=3
Windows 2000 Version 5.2 , CPU type 586
Sun Jun 25 00:13:23 2006
Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
SCN scheme 2
Using log_archive_dest parameter default value
LICENSE_MAX_USERS = 0
SYS auditing is disabled
Starting up ORACLE RDBMS Version: 9.2.0.1.0.
System parameters with non-default values:
processes = 150
timed_statistics = TRUE
shared_pool_size = 369098752
sga_max_size = 697900916
large_pool_size = 33554432
java_pool_size = 33554432
control_files = d:\oracle\oradata\dept\CONTROL01.CTL, d:\oracle\oradata\dept\CONTROL02.CTL, d:\oracle\oradata\dept\CONTROL03.CTL
db_block_size = 8192
db_cache_size = 201326592
compatible = 9.2.0.0.0
log_archive_start = TRUE
log_archive_dest_1 = LOCATION=E:\oracle\archivelog\dept MANDATORY REOPEN
db_file_multiblock_read_count= 16
fast_start_mttr_target = 300
undo_management = AUTO
undo_tablespace = UNDOTBS2
undo_retention = 10800
remote_login_passwordfile= EXCLUSIVE
db_domain =
instance_name = dept
dispatchers = (PROTOCOL=TCP) (SERVICE=deptXDB)
job_queue_processes = 10
hash_join_enabled = TRUE
background_dump_dest = d:\oracle\admin\dept\bdump
user_dump_dest = d:\oracle\admin\dept\udump
core_dump_dest = d:\oracle\admin\dept\cdump
sort_area_size = 524288
db_name = dept
open_cursors = 300
star_transformation_enabled= FALSE
query_rewrite_enabled = FALSE
pga_aggregate_target = 209715200
aq_tm_processes = 1DEPT_ora_2964.trc(内容)
******************************************************
*** 2006-06-24 23:08:07.000
ksedmp: internal or fatal error
ORA-00600: internal error code, arguments: [723], [10332], [10332], [memory leak], [], [], [], []
Current SQL information unavailable - no SGA.
----- Call Stack Trace -----
calling call entry argument values in hex
location type point (? means dubious value)
-------------------- -------- -------------------- ----------------------------
_ksedmp+147 CALLrel _ksedst+0
_ksfdmp.108+e CALLrel _ksedmp+0 3
_kgeriv+89 CALLreg 00000000 1B4148 3
_kgesiv+4e CALLrel _kgeriv+0 1B4148 0 2D3 3 3D6FC34
_ksesic3+3b CALLrel _kgesiv+0 1B4148 0 2D3 3 3D6FC34 2D3 3
3D6FC34
__VInfreq__ksmdpg+e CALLrel _ksesic3+0 2D3 0 285C 0 285C 1 B 25D84BC
f
_opidcl+1ea CALLrel _ksmdpg+0
_opidrv+3bf CALLrel _opidcl+0 1BA298 0
_sou2o+19 CALLrel _opidrv+0
_opimai+150 CALLrel _sou2o+0 3D6FE24 32 0 0
_BackgroundThreadSt CALLrel _opimai+0
art@4+11a
77E1A98D CALLreg 00000000
--------------------- Binary Stack Dump ---------------------
========== FRAME [1] (_ksedmp+147 -> _ksedst+0) ==========
Dump of memory from 0x03D6FB24 to 0x03D6FB9C
3D6FB20 03D6FB9C 00691AB4 00000000 [......i.....]
3D6FB30 00000000 00000000 00000000 001B422C [............,B..]
3D6FB40 00000000 00000000 03D6FB64 00000001 [........d.......]
3D6FB50 00000003 00000000 00000000 00000003 [................]
3D6FB60 03D6FC34 00333237 001B4148 0048E6C8 [4...723.HA....H.]
3D6FB70 018FD140 001B4148 00000000 018FD140 [@...HA......@...]
3D6FB80 001B3FC0 03D6FB30 03D6FBC8 03D6FCF0 [.?..0...........]
3D6FB90 014122B0 025AFB94 FFFFFFFF [."A...Z.....]
========== FRAME [2] (_ksfdmp.108+e -> _ksedmp+0) ==========
Dump of memory from 0x03D6FB9C to 0x03D6FBA8
3D6FB90 03D6FBA8 [....]
3D6FBA0 007C7883 00000003 [.x|.....]
========== FRAME [3] (_kgeriv+89 -> 00000000) ==========
Dump of memory from 0x03D6FBA8 to 0x03D6FBC8
3D6FBA0 03D6FBC8 02652007 [..... e.]
3D6FBB0 001B4148 00000003 00000012 00000000 [HA..............]
3D6FBC0 000002D3 001B4148 [....HA..]
========== FRAME [4] (_kgesiv+4e -> _kgeriv+0) ==========
Dump of memory from 0x03D6FBC8 to 0x03D6FBF4
3D6FBC0 03D6FBF4 026105DB [......a.]
3D6FBD0 001B4148 00000000 000002D3 00000003 [HA..............]
3D6FBE0 03D6FC34 00000000 001B3FC0 03D6FC34 [4........?..4...]
3D6FBF0 001B3FC0 [.?..]
========== FRAME [5] (_ksesic3+3b -> _kgesiv+0) ==========
Dump of memory from 0x03D6FBF4 to 0x03D6FC28
3D6FBF0 03D6FC28 00698B6C 001B4148 [(...l.i.HA..]
3D6FC00 00000000 000002D3 00000003 03D6FC34 [............4...]
3D6FC10 000002D3 00000003 03D6FC34 007CABDC [........4.....|.]
3D6FC20 001B4008 025D84BC [.@....].]
========== FRAME [6] (__VInfreq__ksmdpg+ef -> _ksesic3+0) ==========
Dump of memory from 0x03D6FC28 to 0x03D6FCA4
3D6FC20 03D6FCA4 01889CA4 [........]
3D6FC30 000002D3 00000000 0000285C 00000000 [........\(......]
3D6FC40 0000285C 00000001 0000000B 025D84BC [\(............].]
3D6FC50 03DF6D68 00000000 00000000 03DF6D68 [hm..........hm..]
3D6FC60 03D6FC88 02653C6A 03DF6FA0 001B4148 [....j<e..o..HA..]
3D6FC70 00000000 00000000 001B4224 001B3FC0 [........$B...?..]
3D6FC80 001B4200 001B66F4 001B4010 001B66E4 [[email protected]..]
event = "10262 trace name context forever, level 4000"即可阻止小于4K的内存泄露报错，也就是说如果泄露不超过4K就不提示错误添加完毕之后，需要重启数据库如果如此操作之后，还是报这个错，那么说明你的内存泄露比较严重，需要进一步查找原因