The qtemp script will show the used and free temp file blocks, who is using the temp blocks and the details of the temp files.


prompt###Temp segment status

col file_name format a65
prompt###Who is using the temp segments
SELECT b.tablespace, b.segfile#, b.segblk#, b.blocks, a.sid, a.serial#,
a.username, a.osuser, a.status, b.sqlhash
FROM v$session a,v$sort_usage b
WHERE a.saddr = b.session_addr
ORDER BY b.tablespace, b.segfile#, b.segblk#, b.blocks;

prompt###Temp Tablespace details
Select tablespace_name, file_name, bytes/1024/1024 MB from dba_temp_files;



Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s