Oracle Database Discussions

Hi,

we have a particular problem and no clue how to solve it. It seems that one of our temporary tablespaces is 99,98% full all the time. I queried some of the v$ tables, but they're pretty puzzling for me (not that experience in such matters as you can see ;). I post some of the results here and 'll be happy if someone has a clue what's going on or what else to query. Tx in advance!


V$TEMPSTAT
FILE#","PHYRDS","PHYWRTS","PHYBLKRD","PHYBLKWRT","READTIM","WRITETIM","AVGIOTIM","LSTIOTIM","MINIOTIM","MAXIORTM","MAXIOWTM"
1,10222373,1989721,195766981,31482109,243,60965,1,1,0,14310851,144375
2,1544561,1662604,22003367,26591704,126,58635,1,1,0,1665868,661582

V$SORT_SEGMENT
"TABLESPACE_NAME","SEGMENT_FILE","SEGMENT_BLOCK","EXTENT_SIZE","CURRENT_USERS","TOTAL_EXTENTS","TOTAL_BLOCKS","USED_EXTENTS","USED_BLOCKS","FREE_EXTENTS","FREE_BLOCKS","ADDED_EXTENTS","EXTENT_HITS","FREED_EXTENTS","FREE_REQUESTS","MAX_SIZE","MAX_BLOCKS","MAX_USED_SIZE","MAX_USED_BLOCKS","MAX_SORT_SIZE","MAX_SORT_BLOCKS","RELATIVE_FNO"
TEMP01,0,0,64,0,9998,639872,0,0,9998,639872,0,485284,0,0,9998,639872,9998,639872,9998,639872,0

V$SORT_USAGE
"USER","SESSION_ADDR","SESSION_NUM","SQLADDR","SQLHASH","TABLESPACE","CONTENTS","SEGTYPE","SEGFILE#","SEGBLK#","EXTENTS","BLOCKS","SEGRFNO#"
<empty>

V$TEMPFILE
"FILE#","CREATION_CHANGE#","CREATION_TIME","TS#","RFILE#","STATUS","ENABLED","BYTES","BLOCKS","CREATE_BYTES","BLOCK_SIZE","NAME"
1,0,,2,1,ONLINE,READ WRITE,5242880000,320000,5242880000,16384,/ORACLE/PK24OLTP_temp01_01/TEMP01.dbf
2,0,,2,2,ONLINE,READ WRITE,5242880000,320000,5242880000,16384,/ORACLE/PK24OLTP_temp01_01/TEMP02.dbf

V$TEMP_EXTENT_POOL
"TABLESPACE_NAME","FILE_ID","EXTENTS_CACHED","EXTENTS_USED","BLOCKS_CACHED","BLOCKS_USED","BYTES_CACHED","BYTES_USED","RELATIVE_FNO"
TEMP01,1,4999,0,319936,0,5241831424,0,1
TEMP01,2,4999,0,319936,0,5241831424,0,2

V$TEMP_SPACE_HEADER
"TABLESPACE_NAME","FILE_ID","BYTES_USED","BLOCKS_USED","BYTES_FREE","BLOCKS_FREE","RELATIVE_FNO"
TEMP01,2,5242880000,320000,0,0,2
TEMP01,1,5242880000,320000,0,0,1

Status,Name,Type,ExtManagement,Space(M),Used(M),UsedPct
ONLINE,ADVANTAGE_COMMUNICATION_INDEX,PERMANENT,LOCAL, 6,000.000,3592/6000, 59.87
ONLINE,ADVANTAGE_COMMUNICATION_TABLE,PERMANENT,LOCAL, 12,540.000,9950/12540, 79.35
ONLINE,ADVANTAGE_DYNAMIC_INDEX,PERMANENT,LOCAL, 4,500.000,3362,875/4500, 74.73
ONLINE,ADVANTAGE_DYNAMIC_TABLE,PERMANENT,LOCAL, 11,000.000,9670,0625/11000, 87.91
ONLINE,ADVANTAGE_STATIC_INDEX,PERMANENT,LOCAL, 200.000,147,5/200, 73.75
ONLINE,ADVANTAGE_STATIC_TABLE,PERMANENT,LOCAL, 600.000,239/600, 39.83
ONLINE,DATA01,PERMANENT,LOCAL, 7,500.000,6809,6875/7500, 90.80
ONLINE,INDEX01,PERMANENT,LOCAL, 500.000,433,25/500, 86.65
ONLINE,POWERMAR,PERMANENT,LOCAL, 300.000,208,125/300, 69.38
ONLINE,RBSEG01,PERMANENT,DICTIONARY, 4,870.000,17,96875/4870, 0.37
ONLINE,SYSTEM,PERMANENT,DICTIONARY, 308.000,99,5625/308, 32.33
ONLINE,TEMP01,TEMPORARY,LOCAL, 10,000.000,9998/10000, 99.98