Saturday, May 12, 2007

Memory Disk usage of user in tablespace database

http://vsbabu.org/oracle/sect03.html


This following SQL display the usage of each table / user within the BE_LARGE_DATA tablespace.
Useful if you want to find the motherf*** which stucked your database.

select OWNER,
sum(BYTES)
from dba_segments t
where TABLESPACE_NAME like 'BE_LARGE_DATA'
and OWNER not in ('SYS','SYSTEM')
group by t.OWNER;

or in more detailed way :

select OWNER,
SEGMENT_NAME,
SEGMENT_TYPE,
TABLESPACE_NAME,
BYTES
from dba_segments
where TABLESPACE_NAME like 'BE_LARGE_DATA'
and OWNER not in ('SYS','SYSTEM')
order by OWNER, SEGMENT_NAME

No comments: