Re: Temporary tablespace near 100% full, smon is not freeing up, Oracle 7.3.4.5 ----> URGENT

From: Anton Buijs <aammbuijs_at_xs4all.nl>
Date: Thu, 9 Jan 2003 20:07:05 +0100
Message-ID: <3e1dc868$0$49101$e4fe514c@news.xs4all.nl>

Oracle does not free up the segments in a temporary tablespace because it is a waste of resources. The next sort needs to allocate it again. In fact there is no harm in having this tablespace 100% full except when there are monitoring tools (like OEM, Patrol, etc.) that keeps reporting that a tablespace is full.

I can't find the metalink note you mentioned but I recently found note 132913.1 that explains how to free the temporary segment in a temporary tablespace without restarting the database. It does not mention any version. I have used it succesfully in V8.1.7.3.
When you change the default storage of the temporary tablespace the sort segment will be recreated. Oracle implemented this because you may really need to change the default storage and can't affort to restart the db. In this case you use a statement that actually does not change the storage like:

     alter tablespace TEMP default storage (pctincrease 0); and you will see the sort segment disappears when every transaction currently using it is finished.

Casi <scsi44_at_bluewin.ch> schreef in berichtnieuws efe5a36b.0301091011.328dfae6_at_posting.google.com...
| 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
| ____________________________________________________
| Swisscom IT Services AG Switzerland
|
| -----------------------------------------------------------------------
|
| 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

Received on Thu Jan 09 2003 - 13:07:05 CST