TEMP tablespace - URGENT...

[iyyappan]

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

[Orca777]

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

[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

[Orca777]

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

[ales]

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.

[jovery]

There is a simpler way to 'coalesce' a temporary type tablespace

First switch the tablespace back to permenant

alter tablespace xxx permanent;

Then coalesce the tablespace

alter tablespace xxx coalesce;

Lastly switch the tablespace back to temporary

alter tablespace xxx temporary;

Using this method you do not have to create any additional datafiles or drop any.

Regards

[Calvin_Qiu]

Refer to Managing Tablespaces chapter of Oracle 9i Administrator Guide for technical information about LOCALLY MANAGED TEMPORARY TABLESPACE.

[ales]

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.

[jovery]

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

[iyyappan]

Hi Ales,

Thanks for ur reply.

Have a good day,

Regards,
Iyyappan.M