Solved

TEMPORARY TABLESPACE FRAGMENTATION

Posted on 2003-11-18
9
1,985 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
9 Comments
 
LVL 23

Accepted Solution

by:
seazodiac earned 180 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
 

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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 34

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

762 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now