OCP Blog
Super DBA
How Find Large Object in DB
by vazha mantua Monday, November 8, 2010 11:48 AM
This is small script which provide how oracle developer can find large object in oracle DB for a schema.

script :

select round(sum(s),2),t
from (
select sum(b.bytes)/1024/1024/1024 s, a.table_name t from dba_indexes a, dba_segments b
where b.segment_type in ('INDEX','INDEX PARTITION')
and a.index_name=b.segment_name
and a.owner=&SCHEMA
group by a.table_name

union all

select sum(a.bytes)/1024/1024/1024 s,a.segment_name t from dba_segments a
where a.owner=&SCHEMA
and a.segment_type in ('TABLE','TABLE PARTITION')
group by a.segment_name

union all

select sum(b.bytes)/1024/1024/1024 s,a.table_name t from dba_lobs a, dba_segments b
where a.owner=&SCHEMA
and a.segment_name=b.segment_name
group by a.table_name

)
group by t
order by 1 desc

Tags: , ,

Filter by APML

Calendar

<<  November 2018  >>
MoTuWeThFrSaSu
2930311234
567891011
12131415161718
19202122232425
262728293012
3456789

View posts in large calendar

TextBox