Home » RDBMS Server » Server Administration » Drop Temporary Tablespace
Drop Temporary Tablespace [message #237632] Tue, 15 May 2007 04:57 Go to next message
Brayan
Messages: 315
Registered: June 2002
Senior Member
Hi,

How can I find whether the temporary tablespace is used by any users.

Becase If I'm drop the tblspace, its hanging.

alter database default temporary tablespace temp_ts;

drop tablespace temp including contents and datafiles;


So, Before trying to drop I want to check whether used by any user.

Brayan.
Re: Drop Temporary Tablespace [message #237643 is a reply to message #237632] Tue, 15 May 2007 05:36 Go to previous messageGo to next message
dbashailendra
Messages: 2
Registered: May 2007
Junior Member
You cannot drop a temporary tablespace if it is part of the database default temporary tablespace group.
You must first remove the tablespace from the database default temporary tablespace group and then drop it.
Re: Drop Temporary Tablespace [message #237647 is a reply to message #237632] Tue, 15 May 2007 06:03 Go to previous messageGo to next message
Brayan
Messages: 315
Registered: June 2002
Senior Member

Hi Shailendra,

Quote:

You cannot drop a temporary tablespace if it is part of the database default temporary tablespace group.
You must first remove the tablespace from the database default temporary tablespace group and then drop it.

Yes, that is why I use

alter database default temporary tablespace temp_ts;


My question was to find whether any user using the temporary tablespace, before changing the default and droping.

Brayan.
Re: Drop Temporary Tablespace [message #237703 is a reply to message #237647] Tue, 15 May 2007 08:50 Go to previous messageGo to next message
harshad.gohil
Messages: 157
Registered: April 2007
Location: USA
Senior Member
It's necessary that you have to check the default temporary tablespace before dropping any temp tablespace, else create one more temporary tablespace assign evryone as an default temporary tablespace and than drop the previous one.


Regards,
Harsh
Re: Drop Temporary Tablespace [message #237716 is a reply to message #237632] Tue, 15 May 2007 09:07 Go to previous messageGo to next message
Brayan
Messages: 315
Registered: June 2002
Senior Member
Hi,

May be I'm not putting across the message correctly.

Suppose, user runs a query and it uses temporary segment for sorts.

Now, I want to know which user is using the temporary tablespace.

Brayan.
Re: Drop Temporary Tablespace [message #237717 is a reply to message #237716] Tue, 15 May 2007 09:10 Go to previous message
harshad.gohil
Messages: 157
Registered: April 2007
Location: USA
Senior Member
Check this query.

SELECT b.tablespace,
ROUND(((b.blocks*p.value)/1024/1024),2)||'M' "SIZE",
a.sid||','||a.serial# SID_SERIAL,
a.username,
a.program
FROM sys.v_$session a,
sys.v_$sort_usage b,
sys.v_$parameter p
WHERE p.name = 'db_block_size'
AND a.saddr = b.session_addr
ORDER BY b.tablespace, b.blocks;
Previous Topic: Table Partition - Rebuild local Indexes
Next Topic: ORA-01578
Goto Forum:
  


Current Time: Wed Dec 07 18:15:28 CST 2016

Total time taken to generate the page: 0.10356 seconds