asked on

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.

ASKER CERTIFIED SOLUTION

seazodiac

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

algotube

ASKER

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.

seazodiac

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.

algotube

ASKER

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

seazodiac

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 Geerlings

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.

Ora_Techie

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.

algotube

ASKER

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

seazodiac

of course, in fact, if you don't specify MAXEXTENTS parameter, oracle will default to 2147483645.