| 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 sqlquerys 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
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#
Database Usage by user and Tablespace Page: 1
Owner Tablespace Size K
------------------------ ------------------------------ ------------
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: 2
Owner Tablespace Size KPage: 3
------------------------ ------------------------------ ------------
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
Owner Tablespace Size KPage: 4
------------------------ ------------------------------ ------------
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
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
 |
 |