| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Temporary tablespace near 100% full, smon is not freeing up, Oracle 7.3.4.5 ----> URGENT
Dear all
on one of your most important oracle instance (with many surrounding instances), the temporary tablespace TEMPSORT01 got 100% full every month. SMON is not freeing it, the only solution was to restart the DB.
Today, the same situation, but it's the end of the day and we have time to do some analysis to get the cause of the problem. also, the users are aible to work at the moment at the usual performance. the aim is to free the temporary tablespace without restarting the whole instance.
In Metalink note 40758.1 there is a script to free the temp-tablespaces described, but this note is not accessible for me. Maybe someone can send it to me via mail or has a similar script for forcing smon in Oracle 7.3.4.5?
Please study the sqlquery and their output below - please contact me when you would like more scripts to be run.
Thanks a lot for your help!
Casimir Schmid
Oracle DBA
Post office address:
Swisscom IT Services AG, Casimir Schmid, Neuhusstrasse 3, CH-6045 Meggen
SQL> SET ECHO OFF
set newpage0
ttitle center 'Database Usage by user and Tablespace'-
right 'Page:' format 999 sql.pno skip skip
break on owner skip 2
col K format 999,999,999 heading 'Size K'
col ow format a24 heading 'Owner'
col ta format a30 heading 'Tablespace'
spool tfsdbspa.lst
set feedback off
start time
set feedback 6
select us.name ow,
ts.name ta,
sum(seg.blocks*ts.blocksize)/1024 K
from sys.ts$ ts,
sys.user$ us,
sys.seg$ seg
where seg.user# = us.user#
and ts.ts# = seg.ts#
Owner Tablespace Size K2
------------------------ ------------------------------ ------------
APP TEMPDAT 1,189,228 OPS$TMEPROD ADMDAT 15,272 OPS$TMEPROD ADMIND 11,560 OPS$TMEPROD CKTODAT 1,732,980 OPS$TMEPROD CKTOIND 3,207,920 OPS$TMEPROD CKTVDAT 12,520 OPS$TMEPROD CKTVIND 15,140 OPS$TMEPROD CLRDAT 415,140 OPS$TMEPROD CLRIND 712,160 OPS$TMEPROD CNDAT 300,708 Database Usage by user and Tablespace Page:
Owner Tablespace Size K3
------------------------ ------------------------------ ------------
OPS$TMEPROD CNIND 609,144 OPS$TMEPROD CUDAT 468,940 OPS$TMEPROD CUIND 1,241,720 OPS$TMEPROD DIVDAT 181,260 OPS$TMEPROD DIVIND 295,940 OPS$TMEPROD ECDAT 266,704 OPS$TMEPROD ECIND 72,420 OPS$TMEPROD INVDAT 257,640 OPS$TMEPROD INVIND 493,120 OPS$TMEPROD NETDAT 8,300 Database Usage by user and Tablespace Page:
Owner Tablespace Size K4
------------------------ ------------------------------ ------------
OPS$TMEPROD NETIND 32,180 OPS$TMEPROD NONDAT 516,720 OPS$TMEPROD NONIND 651,440 OPS$TMEPROD ORDDAT 2,230,420 OPS$TMEPROD ORDIND 683,404 OPS$TMEPROD TEMPDAT 173,920 OPS$TMEPROD TRBDAT 80 OPS$TMEPROD TRBIND 160 OPS$TMEPROD UDFDAT 1,392,300 OPS$TMEPROD UDFIND 3,236,544 Database Usage by user and Tablespace Page:
Owner Tablespace Size K
------------------------ ------------------------------ ------------
OPS$TMEPROD UDLDAT 3,020 OPS$TMEPROD UDLIND 2,340 OPS$TMEPROD WKGDAT 8,640 OPS$TMEPROD WKGIND 5,900 SMILE TEMPDAT 408 SYS RBLGDAT 20,540 SYS ROLLDAT 247,440 SYS SYSTEM 36,156 SYS TEMPSORT01 14,597,180 SYSTEM SYSTEM 488,140
----------------------------------------------------------------------------
----------------
SQL> SELECT * FROM dba_segments where segment_type='TEMPORARY';
OWNER SEGMENT_NAME
SEGMENT_TYPE TABLESPACE_NAME
HEADER_FILE HEADER_BLOCK BYTES BLOCKS EXTENTS INITIAL_EXTENT
NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE FREELISTS FREELIST_GROUPS
 |
 |