| 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 -> Re: Temporary tablespace near 100% full, smon is not freeing up, Oracle 7.3.4.5 ----> URGENT
Temporary tablespace is *supposed* to be 100% full.
When permanent tablespace is used for user sorts, then PGA-sized extents are allocated within it, only to be dropped when that user's report finishes. The next sort to disk then has to go to the bother of re-allocating the extents.
When proper temporary tablespace is used for sorts, the extents are allocated as before by the first person doing a sort. At the end of his or her report, the extents he caused to be allocated are *not* dropped, but are merely marked for re-use by other users. This makes the second and subsequent sorts run faster than they would have done in permanent tablespace.
But it also means that the space in the temporary tablespace is not released. Eventually, the entire temporary tablespace will have extents marked out within it, and it will look 100% full. But that doesn't stop other people from re-using those extents, and it isn't a problem. In fact, it's a design feature. Indeed, if your temporary tablespace is only (say) 94% full, then it means you are wasting 6% of disk space.
Rather than worrying about how full the temporary tablespace is, you should only be concerned if users report that their sorts are failing for lack of space. And if that's the case, the issue is merely that your temporary tablespace is too small.
With all that said, you can prod SMON to do its stuff if you are a bit of a masochist by merely altering the tablespace to permanent, and then making it temporary again:
alter tablespace TEMP permanent;
alter tablespace TEMP temporary;
Regards
HJR
"Casi Schmid" <casimir.schmid_at_swisscom.com> wrote in message
news:1042137733.228879_at_exnews...
> 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
> ____________________________________________________
> Swisscom IT Services AG Department: IT-DCS-OCC-OCL-OPD
>
> Post office address:
> Swisscom IT Services AG, Casimir Schmid, Neuhusstrasse 3, CH-6045 Meggen
>
>
> --------------------------------------------------------------------------
--Received on Thu Jan 09 2003 - 13:09:53 CST
> ---------------------------------------
>
>
> 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#
> group by us.name,ts.name
> /
> prompt End of Report
> spool off
> ttitle off
> clear breaks
> clear columns
> clear computes
> set verify on SQL> SQL> > SQL> SQL> SQL> SQL> SQL> SQL> SQL> unable to
open
> file "/usr/bin/time.sql"
> SQL> SQL> 2 3 4 5 6 7 8 9 10
>
> 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 K
> ------------------------ ------------------------------ ------------
> 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:
> 3
>
> Owner Tablespace Size K
> ------------------------ ------------------------------ ------------
> 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:
> 4
>
> 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
> ------------------------------
> --------------------------------------------------------------------------
--
> ----
> - ----------------- ------------------------------ ----------- -----------
-
> ---------- ---------- ---------- -------------- ----------- -----------
> ----------- ------------ ---------- ---------------
> SYS 34.252202
> TEMPORARY TEMPSORT01
> 34 252202 1.4948E+10 3649295 7086 2109440
> 2109440 1 2147483645 0 1 1
>
>
>
>
 |
 |