First find the block size:
SELECT VALUE "BLOCK_SIZE"
FROM v$parameter
WHERE name='db_block_size' ;
FROM v$parameter
WHERE name='db_block_size' ;
Now check the usage of the tablespace:
SELECT
s.username "USER",s.sid,s.osuser,
u.TABLESPACE "TS" ,
SUM(u.blocks) * &BLOCK_SIZE./1024/1024 MB,
x.sql_text
FROM v$session s,v$sort_usage u,v$sqltext x
WHERE s.saddr=u.session_addr
AND s.sql_address=x.address
GROUP BY
s.sid, s.username, osuser,
TABLESPACE, sql_text, address, piece
ORDER BY sid, piece ASC;
s.username "USER",s.sid,s.osuser,
u.TABLESPACE "TS" ,
SUM(u.blocks) * &BLOCK_SIZE./1024/1024 MB,
x.sql_text
FROM v$session s,v$sort_usage u,v$sqltext x
WHERE s.saddr=u.session_addr
AND s.sql_address=x.address
GROUP BY
s.sid, s.username, osuser,
TABLESPACE, sql_text, address, piece
ORDER BY sid, piece ASC;
