Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2106
  • Last Modified:

TEMPORARY TABLESPACE FRAGMENTATION

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
algotube
Asked:
algotube
1 Solution
 
seazodiacCommented:
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
 
algotubeAuthor Commented:
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
 
seazodiacCommented:
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
algotubeAuthor Commented:
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
 
seazodiacCommented:
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
 
Mark GeerlingsDatabase AdministratorCommented:
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
 
riazpkCommented:
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
 
algotubeAuthor Commented:
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
 
seazodiacCommented:
of course, in fact, if you don't specify MAXEXTENTS parameter, oracle will default to 2147483645.
0

Featured Post

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now