|
|
| Re: to check for temporary space usage [message #429600 is a reply to message #429598] |
Wed, 04 November 2009 13:09   |
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
|
Senior Member |
|
|
This script reports temporary tablespace usage. (This script was created for Oracle9i Database.)
With this script you can monitor the actual space used in a temporary tablespace and see the HWM of the temporary tablespace. The script is designed to run when there is only one temporary tablespace in the database.
select sum( u.blocks * blk.block_size)/1024/1024 "Mb. in sort segments"
, (hwm.max * blk.block_size)/1024/1024 "Mb. High Water Mark"
from v$sort_usage u, (select block_size
from dba_tablespaces
where contents = 'TEMPORARY') blk
, (select segblk#+blocks max
from v$sort_usage
where segblk# = (select max(segblk#) from v$sort_usage) ) hwm
group by hwm.max * blk.block_size/1024/1024;
[Updated on: Wed, 04 November 2009 13:09] Report message to a moderator |
|
|
|
| Re: to check for temporary space usage [message #429647 is a reply to message #429600] |
Thu, 05 November 2009 00:10 |
ankush_chawla
Messages: 136
Registered: November 2006
|
Senior Member |
|
|
I m executing the rebuild operation .
After that when i execute this command i dont find any result.
Size of index is 500 Mb
Does it mean rebuild doesnot used any temporary tablespace
Morever we even checked with temp_space_header . However we still find that after rebuild the value doesnot increases.
Kindly explain.
|
|
|
|
|
|
|
|
|
|
| Re: to check for temporary space usage [message #429683 is a reply to message #429665] |
Thu, 05 November 2009 03:54 |
ankush_chawla
Messages: 136
Registered: November 2006
|
Senior Member |
|
|
Thanks a lot for your replies and help
But i still have 2 more concerns.
- I wanted to confirm while rebuilding It creates the temp segment in the same tablespace where the index is created and drops the previous index as soon as new index is created.
- But i m confused when i m rebuilding an large index more than 5 B it gives error : Cannot extend temp segment. Then now how it moves to temporary tablespace.
|
|
|
|
| Re: to check for temporary space usage [message #429701 is a reply to message #429683] |
Thu, 05 November 2009 05:33 |
 |
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
1/ Confirmed
2/ If you read the name of the temp segment tablespace used you will see it is the target tablespace. As I said, Oracle uses temp space (segment) in this one not the temporary tablespace (don't mix up, temp tablespace and temp segment).
Regards
Michel
[Edit: typos]
[Updated on: Thu, 05 November 2009 08:51] Report message to a moderator |
|
|
|
|
|
|
|
) 1 Vote
] 
Blog
Search
Help
Members
Register
Login
Home
