| Drop Temporary Tablespace [message #237632] |
Tue, 15 May 2007 04:57  |
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  |
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 |
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 |
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 |
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 |
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;
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
