Temporary tablespace size huge

[feng11]

Hi Guys,

My oracle8i's temp tablepsace has been increasing. Now is at 2gb. is there any ay i can shrink it down? Is this temp the same as those datafiles where i can just issue a "resize" command to shrink to to the last data block is that used?

thanks

[alapps]

The TEMP tablespace should not have the autoextend option turned on, that is why your tablespace is growing.

As for "Resizing" the temp tablespace, you have a couple of options:

If you are able to have exclusive control of the DB (i.e. A database maintenance/downtime, then use the following option:

-- Shutdown the DB
Start the DB in restriected mode
Drop the temp tablespace
Recreate the temp tablespace


If unable to have exclusive control (i.e 24x7 ) of the DB then:
-- Create a new tablespace (i.e. TEMP1)
-- Switch all users to use this new tablespace as their default TEMP
-- Drop the "old" TEMP (2GB) tablespace
-- Recreate the TEMP tablespace
-- Switch all users to use TEMP as their default TEMP
-- Drop the "Temporary temp" TEMP1 tablespace.

[agasimani]

To add to alapps comments, dont forget to add TEMPORARY clause to your statement as follows:

CREATE TABLESPACE temp1 datafile '.../temp01.dbf' SIZE 300M
DEFAULT STORAGE ( INITIAL 1M NEXT 1M MINEXTENTS 1 MAXEXTENTS 500)
TEMPORARY;

This assures that nobody can create permanent objects in this ts.

[feng11]

I have tried to do a resize on my devp database for the temp tablespaces and it works. Can this be done on the production systems?

[alapps]

A resize will work if the area at the end of the datafile is not in use. A production database will most likely have that in use, especially in this case since the TEMP tablespace keeps growing (Autoextend should not be on for datafiles within temp tablespaces).

The drop and recreate in restricted mode was recommended since it works.

[julian]

Originally posted by feng11
Hi Guys,

My oracle8i's temp tablepsace has been increasing. Now is at 2gb. is there any ay i can shrink it down? Is this temp the same as those datafiles where i can just issue a "resize" command to shrink to to the last data block is that used?

thanks

Use this query to find out the HWM of the file. That is up to where you can resize (downwards):

col "File Name" for A40
column file_name format a40;
column highwater format 9999999999;
SELECT Substr(df.file_name,1,40) "File Name",
Round(df.bytes/1024/1024,2) "Size (M)",
Round(e.used_bytes/1024/1024,2) "Used (M)",
Round(f.free_bytes/1024/1024,2) "Free (M)",
round((b.maximum+c.blocks-1)*d.db_block_size/(1024*1024)) "HWM (M)"
FROM dba_data_files df,
(SELECT file_id, Sum(Decode(bytes,NULL,0,bytes)) used_bytes FROM dba_extents GROUP by file_id) e,
(SELECT Max(bytes) free_bytes, file_id FROM dba_free_space GROUP BY file_id) f,
(SELECT file_id, max(block_id) maximum from dba_extents group by file_id) b,
dba_extents c,
(SELECT value db_block_size from v$parameter where name='db_block_size') d
WHERE e.file_id (+) = df.file_id
AND df.file_id = f.file_id (+)
AND df.file_id = b.file_id and c.file_id = b.file_id and c.block_id = b.maximum
ORDER BY
df.tablespace_name, df.file_name
/

[slimdave]

Also, make it locally managed, and set the extent size to be the same as your db sort_area_size

[julian]

Originally posted by slimdave
Also, make it locally managed, and set the extent size to be the same as your db sort_area_size

Good advice!

That is if you use SORT_AREA_SIZE and not:

pga_aggregate_target = 512M
workarea_size_policy = auto

Even in this case SORT_AREA_SIZE is set by Oracle (64K) but then the extent size need not be 64K.

[Orca777]

Hi,
identify the reason why Temp increases so much :

--
-- KRE CSC SQL's die TEMP belegen
--
SELECT /*+ORDERED*/su.tablespace,
       se.sid,
       se.user#,
       se.serial#, 
       su.blocks,
       su.segtype,
       sa.sql_text
 FROM  v$sort_usage su
      ,v$sqlarea    sa
      ,v$session    se
WHERE su.session_addr = se.saddr
  AND se.sql_address  = sa.address(+)
/*ORDER
   BY su.tablespace,
      se.sid,
      se.serial#,
      sa.address
*/

Orca