TEMP tablespace - URGENT...

Printable View

11-12-2002, 04:05 AM
iyyappan

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
11-12-2002, 04:17 AM
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
11-12-2002, 04:27 AM
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
11-12-2002, 05:24 AM
Orca777

Quote:

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
:confused:
11-12-2002, 05:40 AM
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.
11-12-2002, 05:50 AM
jovery

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
11-12-2002, 06:15 AM
Calvin_Qiu

Refer to Managing Tablespaces chapter of Oracle 9i Administrator Guide for technical information about LOCALLY MANAGED TEMPORARY TABLESPACE.
11-12-2002, 06:17 AM
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.
11-12-2002, 06:22 AM
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
11-12-2002, 07:38 AM
iyyappan

Hi Ales,

Thanks for ur reply.

Have a good day,

Regards,
Iyyappan.M
11-12-2002, 07:39 AM
pgoenka

i guess approach suggested here are right but one more thing can be done
create one more temporary tablespace say temp01 etc
alter the existing user to allocate this new tablespace as there temporary .
later u can drop your orignal tablespace or you can allocate these tables space as one to some set of users (depending on the sort etc requirement) and other to other set.