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.

  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.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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.
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.
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

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.
(3) Check the current status of the tablespace.  
       WHERE TABLESPACE_NAME = '<tablespace>';          
(4) Drop the tablespace.
       DROP TABLESPACE <tablespace> INCLUDING  
(5) Recreate the tablespace with the desired parameters.          
     CREATE TABLESPACE <tablespace>      
            DATAFILE <filespec>        
            DEFAULT STORAGE <storage_clause> ;  

Thank you
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.
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.
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.
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';
-----------         ---------- ---------- ---------- ---------- ----------
TEMPORARY     512000     512000      1          2147483645
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:
DATAFILE 'E:\orant\database\Tmp1pps.ora' SIZE 10M
MAXEXTENTS 2147483645
I believe the DBA who created this tablespace before me used this size.
Feedback please and thank you
of course, in fact, if you don't specify MAXEXTENTS parameter, oracle will default to 2147483645.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.