i hv temp tablespace in my db. the name of
tablespace : temp (file name temp01.dbf) by default it was in autoextend datafile mode when full)
due to autoextend my developer has done some mistake in writing query statement (in loop). my temp size increase upto maximum extend & full(defined space on OS).
Now i have remove this query & restart database now used temp space is free (only zero). but now
i will try to reduce the size of temp01.dbf file but it giving error
(we can not reduce the size ..)
how to reduce the size of temp01.dbf ? please advice
What are the storage parameters of your temp tablespace?? Is contents of your tablespace permanent or temporary. If it permanent change it to temporary.
The query you refer to addresses dba_data_files. He wants to reduce the TEMP file. Even so, resizing TEMP files is often pointless for their HWM is usually hitting the top.
If you have a locally managed temporary tablespace you are stucked. I particularly went thru that problem where I find myself adding space to the LMTT because
1) I was unable to resize it
2) will not deallocate even when there is no sorting going and I hate
to see 8 gig allocated to temporary when I could help with load balancing.
So I drop and recreated a DMTT(Dictionary Managed Temporary Tablespace)where I can take control of manually deallocate the temp tablespce as follows:
1) FIND USERS WHO ARE SORTING IN THE TEMP TABLESPACE:
**************************
SELECT t1. username, t2.tablespace, t2.contents, t2.extents, t2.blocks
FROM v$session t1, V$sort_usage t2
WHERE t1.saddr = t2.session_addr ;
If no is returned proceed withn the 2nd step.
2) SECOND RUN THIS TO COALESCE FREE EXTENTS*
********************************
****Alter tablespace temp coalesce ;
3) THIRD RUN THIS TO RECLAIM THE SPACE
***************************
alter tablespace temp
default storage (pctincrease 1);
FOLLOWED BY
alter tablespace temp
default storage (pctincrease 0);
On more thing about autoextend on. after reading all docs I personnally have my system tablespace autoextend on that was it.
HTH
Last edited by Ablakios; 04-29-2003 at 11:10 AM.
Arsene Lupain
The lie takes the elevator, the truth takes the staircase but ends up catching up with the lie.
Step:2 ,3 are not runing . it display error
"SQL> Alter tablespace temp coalesce ;
Alter tablespace temp coalesce
*
ERROR at line 1:
ORA-03217: invalid option for alter of TEMPORARY TABLESPACE
SQL> alter tablespace temp
2 default storage (pctincrease 1);
alter tablespace temp
*
ERROR at line 1:
ORA-03217: invalid option for alter of TEMPORARY TABLESPACE
--------------------------------------------------------"
Regs
ramji
Originally posted by julian The query you refer to addresses dba_data_files. He wants to reduce the TEMP file. Even so, resizing TEMP files is often pointless for their HWM is usually hitting the top.
If you have a locally managed temporary tablespace you are stucked. I particularly went thru that problem where I find myself adding space to the LMTT because
1) I was unable to resize it
2) will not deallocate even when there is no sorting going and I hate
to see 8 gig allocated to temporary when I could help with load balancing.
So I drop and recreated a DMTT(Dictionary Managed Temporary Tablespace)where I can take control of manually deallocate the temp tablespce as follows:
1) FIND USERS WHO ARE SORTING IN THE TEMP TABLESPACE:
**************************
SELECT t1. username, t2.tablespace, t2.contents, t2.extents, t2.blocks
FROM v$session t1, V$sort_usage t2
WHERE t1.saddr = t2.session_addr ;
If no is returned proceed withn the 2nd step.
2) SECOND RUN THIS TO COALESCE FREE EXTENTS*
********************************
****Alter tablespace temp coalesce ;
3) THIRD RUN THIS TO RECLAIM THE SPACE
***************************
alter tablespace temp
default storage (pctincrease 1);
FOLLOWED BY
alter tablespace temp
default storage (pctincrease 0);
On more thing about autoextend on. after reading all docs I personnally have my system tablespace autoextend on that was it.
HTH
Sounds like you've got yourself a job for life there.
How often do you have to resize TEMP? Pretty rarely, i would think.
Why do you need to coalesce free space?
Because you are using a dictionary managed tablespace.
Why do you hate it that the temp space is allocated in an LMT, even when it's not actually in use?
Because it's not what you're used to.
You know how to load balance TEMP? Use a LMT, uniform extent size equal to sort_area_size, and multiple datafiles spread across all your devices. Very easy.
So for interfering with progress and artificially inflating your own workload at the expense of good DB management techniques, you're fired.
Originally posted by julian CREATE TEMPORARY TABLESPACE temp TEMPFILE '/u03/oradata1/.../temp01.dbf' SIZE 1024M reuse
AUTOEXTEND ON NEXT 256K MAXSIZE 6144M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;
Julian, is there a reason for you suggesting the autoextend value being 256K when (presumably) the extra space will be needed in units of 1M? Or did you mean 256M? Tx.
"The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman
Reply With Quote
Bookmarks