Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

TEMPORARY TABLESPACE FRAGMENTATION

Posted on 2003-11-18
9
Medium Priority
?
2,080 Views
Last Modified: 2012-08-13
Oracle Version: Oracle8 Enterprise Edition Release 8.0.5
O/S:                Windows NT 4.0

Hello Experts how are you today?
I have just ran this script and it indicated that my TEMPORARY tablespace has fragmentation.

system@PPS.WORLD>select
  2  total.tablespace_name tsname,
  3  count(free.bytes) nfrags,
  4  nvl(max(free.bytes)/1024,0) mxfrag,
  5  total.bytes/1024 totsiz,
  6  nvl(sum(free.bytes)/1024,0) avasiz,
  7  (1-nvl(sum(free.bytes),0)/total.bytes)*100 pctusd
  8  from
  9  dba_data_files total,
 10  dba_free_space free
 11  where
 12  total.tablespace_name = free.tablespace_name(+)
 13  and total.file_id=free.file_id(+)
 14  group by
 15  total.tablespace_name,
 16  total.bytes;

-                                     Database FreespaceSummary

                   Free     Largest       Total      Available   Pct
   Tablespace     Frags    Frag (KB)       (KB)         (KB)     Used
---------------- -------- ------------ ------------ ------------ ----
INDX                    1      999,208      999,210      999,208    0
INDXDEV                 8      133,058      819,200      321,108   61
INDXPROD               15      161,480    1,081,344      555,962   49
INDXQA                  2      295,708      768,000      301,368   61
PPSDEV                  3      767,658      768,000      981,746  -28
PPSPROD                 8      736,938      768,000      870,576  -13
PPSQA                   4      766,988      768,000      990,326  -29
RBS                     0            0      188,702            0  100
RBS                     1      256,398      768,000      256,398   67
SYSTEM                  7      799,810    1,824,230    1,728,490    5
TEMPORARY          15,000          500    1,662,702    1,604,700    3
                 --------              ------------ ------------
sum                15,049                10,415,388    8,609,882

11 rows selected.

Can you please indicate all the steps needed to get my TEMPORARY TABLESPACE FRAGMENTATION under control for this version of Oracle.

Thank you for your time.
0
Comment
Question by:algotube
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
9 Comments
 
LVL 23

Accepted Solution

by:
seazodiac earned 720 total points
ID: 9772328
The secret is that you never have to de-fragment the temp tablespace.
by nature, temporary tablespace is very easy to get fragmented.

if that bothers you much, you can just drop the old and create a new temp tablespace with ease..

in oracle 8, you can do this:

1. alter database tempfile '<the full path of temp01.dbf>' drop;
2. then delete the OS temp file
3. alter tablespace temp add tempfile '<a new temp01.dbf>';

now, you will have a brand new temp tablespace.
0
 

Author Comment

by:algotube
ID: 9773013
Hi seazodiac, thank you for your response. I thought it was straight forward just like suggested. But when I was talking to Oracle they insisted I do:

 Shutdown and startup the database.

SMON actually de-allocates the sort segment after the instance has been
started and the database has been opened. Thus, after the database has been
opened, SMON may be seen to consume large amounts of CPU as it first
de-allocates the (extents from the) temporary segment, and then when it is
requested to perform free space coalescing of the free extents created by
the temporary segment cleanup.

This should take care of the fragmentation.

Any feedback on this would be welcomed.
0
 
LVL 23

Expert Comment

by:seazodiac
ID: 9773148
I think they are right. So depending on the situations, you can use either of two methods to solve that.
therefore, if your oracle database has to be up 24X7, you create a new , fresh temp tablespace.
otherwise, you can reboot the oracle database and let SMON to do that.
0
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 

Author Comment

by:algotube
ID: 9773295
Ok seazodiac good enough.

Could I have used this method also? Would it have accomplished the same thing?

(1)Go into Server Manager and connect internal.
(2) Offline the tablespace.
     ALTER TABLESPACE <tablespace> OFFLINE;
(3) Check the current status of the tablespace.  
      SELECT STATUS FROM DBA_TABLESPACES
       WHERE TABLESPACE_NAME = '<tablespace>';          
(4) Drop the tablespace.
       DROP TABLESPACE <tablespace> INCLUDING  
       CONTENTS         CASCADE CONSTRAINTS;  
(5) Recreate the tablespace with the desired parameters.          
     CREATE TABLESPACE <tablespace>      
            DATAFILE <filespec>        
            DEFAULT STORAGE <storage_clause> ;  

Thank you
0
 
LVL 23

Expert Comment

by:seazodiac
ID: 9773358
quite honestly, I am not sure about your approach, I am a Oracle8i and 9i DBA.
but my feeling on this is that you can do that except the step 4, you can just use drop tablespace <tablespace> ; and step 5 you have to explicitly specify the "TEMPORARY" keyword to create a temporary tablespace.
0
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 9773592
For me too, it has been a while since I worked with Oracle8.0.  With 8i and 9i you can (and should) use "tempfile" rather than "datafile" for the temporary tablespace(s).  I don't think that Oracle8.0 supports that syntax, but I am sure that it allows something like "contents temporary" when you create the temporary tablespace in 8.0.  This will prevent permananent objects (tables or indexes) from being created in the temp tablespace and also reduce the amount of rollback activity generated.
0
 
LVL 13

Expert Comment

by:riazpk
ID: 9776763
I think there is no need to drop the Temp Tablespace. Just Resize the temp tablespace to minimum level. And create an LMT (Locally-Managed Tablespace) temporary tablespace and forget abt de-frangmentation since LMTs don't have this problem.
0
 

Author Comment

by:algotube
ID: 9797127
If you don't mind I  have one more quetion for you.
-
I just ran this small script on my TEMPORARY tablespace:
-
SQLWKS> SELECT tablespace_name "TABLESPACE",
2> initial_extent "INITIAL_EXT",
3> next_extent "NEXT_EXT",
4> min_extents "MIN_EXT",
5> max_extents "MAX_EXT",
6> pct_increase
7> FROM sys.dba_tablespaces
8> WHERE tablespace_name='TEMPORARY';
-
TABLESPACE   INITIAL_EX NEXT_EXT MIN_EXT MAX_EXT PCT_INCREA
-----------         ---------- ---------- ---------- ---------- ----------
TEMPORARY     512000     512000      1          2147483645
 0
1 row selected.
-
As you can see the MAX_EXTENT is 2147483645. My question is if I decide to create the TEMPORARY tablespace after dropping it can I use 2147483645 as my actual MAX_EXTENT size?
 for example:
-
SVRMGR> CREATE TABLESPACE TEMPORARY
DATAFILE 'E:\orant\database\Tmp1pps.ora' SIZE 10M
DEFAULT STORAGE (
INITIAL 500K
NEXT 500K
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0)
ONLINE
TEMPORARY;
I believe the DBA who created this tablespace before me used this size.
-
Feedback please and thank you
0
 
LVL 23

Expert Comment

by:seazodiac
ID: 9797476
of course, in fact, if you don't specify MAXEXTENTS parameter, oracle will default to 2147483645.
0

Featured Post

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

715 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question