-
Temporary tablespace size huge
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
Regards
CF
-
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.
Last edited by alapps; 01-07-2003 at 11:49 PM.
alapps
Fast, Cheap, Reliable... Pick Two(2)
-
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.
Agasimani
OCP(10g/9i/8i/8)
-
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?
Regards
CF
-
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.
alapps
Fast, Cheap, Reliable... Pick Two(2)
-
Re: Temporary tablespace size huge
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
/
Oracle Certified Master
Oracle Certified Professional 6i,8i,9i,10g,11g,12c
email: ocp_9i@yahoo.com
-
Also, make it locally managed, and set the extent size to be the same as your db sort_area_size
-
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.
Oracle Certified Master
Oracle Certified Professional 6i,8i,9i,10g,11g,12c
email: ocp_9i@yahoo.com
-
Hi,
identify the reason why Temp increases so much :
Code:
--
-- 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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|