Temporary space usage

set pagesize 10000
set linesize 135

column tablespace format a15 heading ‘TABLESPACE NAME’
column segfile# format 9,999 heading ‘FILE|ID’
column spid format 9,999 heading ‘UNIX|ID’
column segblk# format 999,999,999 heading ‘BLOCK|ID’
column size_mb format 999,999,990.00 heading “MB|USED”
column username format a15
column osuser format a10
column program format a15

SELECT
b.tablespace,
b.segfile#,
b.segblk#,
round(((b.blocks*p.value)/1024/1024),2) size_mb,
a.sid,
a.serial#,
a.username,
a.osuser,
a.program,
a.status
FROM
v$session a,
v$sort_usage b,
v$process c,
v$parameter p
WHERE
p.name=’db_block_size’ AND
a.saddr = b.session_addr AND
a.paddr=c.addr
ORDER BY
b.tablespace,
b.segfile#,
b.segblk#,
b.blocks;

Advertisements

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

%d bloggers like this: