-
TEMP tablespace - URGENT...
Dear Gurus,
My Temp tablespace shows it has 3GB filled. How can i reduced it??
Coalescing doesn't make any diff. Please let me know how i get back
the space??
My Temp tablespace is Locally Managed. PCT_INCREASE value is 0.
Can i change change PCTINCREASE to 1 and then back to 0. It should coalesce itself?
I use this command but i got the error
SQL> alter tablespace temp
2 default storage (
3 pctincrease 1);
alter tablespace temp
*
ERROR at line 1:
ORA-03217: invalid option for alter of TEMPORARY TABLESPACE
How to use this command? can u please advise me,
Thanks in advance,
Iyyappan.M
-
Hi,
there is no need to coalesce the Temp-Ts.
What you see is the 'Highwatermark' of the Temp-Segment.
The segment can be allocated for a transaction in the past
and now be empty.
Try to identify the SQL's which needs teh temp-Ts,
and tune them or replace the logic of expensive code.
Orca
--
-- KRE CSC SQL's die TEMP belegen
--
SELECT su.tablespace,
se.sid,
se.serial#,
su.blocks,
sa.sql_text
FROM v$sort_usage su
,v$session se
,v$sqlarea sa
WHERE su.session_addr = se.saddr
AND se.sql_address = sa.address
ORDER
BY su.tablespace,
se.sid,
se.serial#,
sa.address
Last edited by Orca777; 11-12-2002 at 04:20 AM.
-
Seems you get in this situation quite often ... http://forums.dbasupport.com/forums/...threadid=29705
* ALTER TABLESPACE TEMP ADD TEMPFILE 'new_temp_file'
* ALTER DATABASE TEMPFILE 'old_temp_file' OFFLINE;
* ALTER DATABASE TEMPFILE 'old_temp_file' DROP;
this is often not possible, as users may have space allocated in the tempfile. Be patient, kill the sessions or restart the database
* go to the OS and remove the old_temp_file
Ales The whole difference between a little boy and an adult man is the price of toys
-
Originally posted by ales
Seems you get in this situation quite often ... http://forums.dbasupport.com/forums/...threadid=29705
* ALTER TABLESPACE TEMP ADD TEMPFILE 'new_temp_file'
* ALTER DATABASE TEMPFILE 'old_temp_file' OFFLINE;
* ALTER DATABASE TEMPFILE 'old_temp_file' DROP;
this is often not possible, as users may have space allocated in the tempfile. Be patient, kill the sessions or restart the database
* go to the OS and remove the old_temp_file
Hi is there a need to drop the datafile?
And ... is it possible to drop a datafile without complication?
I learned that to have fewer datafiles in a Tablespace you have to recreate the whole Tablespace ?
The Actual file deallocates the storage when you alter the storage ( same value )
alter tablespace temp
2 default storage (
3 pctincrease 0);
Orca
-
I believe his TEMP tablespace is the true temporary tablespace - created with the "CREATE TEMPORARY TABLESPACE" command. It has tempfiles instead of datafiles and it's possible to offline and drop tempfile without any problems - if there aren't active transactions.
In a "normal" tablespace with datafiles you cannot drop a datafile without dropping the tablespace but this is another issue.
The technique I posted is the least painful I've found for "shrinking" true temporary tablespace.
Ales The whole difference between a little boy and an adult man is the price of toys
-
There is a simpler way to 'coalesce' a temporary type tablespace
First switch the tablespace back to permenant
Code:
alter tablespace xxx permanent;
Then coalesce the tablespace
Code:
alter tablespace xxx coalesce;
Lastly switch the tablespace back to temporary
Code:
alter tablespace xxx temporary;
Using this method you do not have to create any additional datafiles or drop any.
Regards
Jim
Oracle Certified Professional
"Build your reputation by helping other people build theirs."
"Sarcasm may be the lowest form of wit but its still funny"
Click HERE to vist my website!
-
Refer to Managing Tablespaces chapter of Oracle 9i Administrator Guide for technical information about LOCALLY MANAGED TEMPORARY TABLESPACE.
Oracle Certified Master - September, 2003, the Second OCM in China
*** LOOKING for PART TIME JOB***
Data Warehouse & Business Intelligence Expert
MCSE, CCNA, SCJP, SCSA from 1998
-
Jim:
This is OK for old fashioned temporary tablespaces with datafiles.
Iyyappan has the true temporary tablespace and switching permanent/temporary fails with it:
system@oracle>system@oracle> create temporary tablespace temp1
2 tempfile 'g:\orant\database\temp1a.dbf' size 1M reuse
3 extent management local uniform size 64k
4 /
Tablespace created.
system@oracle> alter tablespace temp1
2 default storage (pctincrease 1)
3 /
alter tablespace temp1
*
ERROR at line 1:
ORA-03217: invalid option for alter of TEMPORARY TABLESPACE
system@oracle> alter tablespace temp1 permanent
2 /
alter tablespace temp1 permanent
*
ERROR at line 1:
ORA-03217: invalid option for alter of TEMPORARY TABLESPACE
system@oracle> alter tablespace temp1
2 add tempfile 'g:\orant\database\temp1b.dbf' size 1M reuse
3 /
Tablespace altered.
system@oracle> alter database tempfile 'g:\orant\database\temp1a.dbf' offline
2 /
Database altered.
system@oracle> alter database tempfile 'g:\orant\database\temp1a.dbf' drop
2 /
Database altered.
Ales The whole difference between a little boy and an adult man is the price of toys
-
Ales,
Your quite right, I didn't read the intermediate posts properly. In this case the method you have given is the least painful
Regards
Jim
Oracle Certified Professional
"Build your reputation by helping other people build theirs."
"Sarcasm may be the lowest form of wit but its still funny"
Click HERE to vist my website!
-
Hi Ales,
Thanks for ur reply.
Have a good day,
Regards,
Iyyappan.M
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
|