find the monsters [ 1123 views ]
Goal: how to find the big tables in the oracle database
Just a quick way to localize the monsters in the database. Of course you need dba rights.
select e.owner || '.' || e.SEGMENT_NAME obj, e.*
from sys.DBA_EXTENTS e
where e.SEGMENT_TYPE = 'TABLE'
and owner = 'SCHEMA_NAME_HERE' -- or comment this line
order by e.BYTES desc
If you see the monsters, you can start thinking about the solution.
And the indexes:
select e.owner || '.' || e.SEGMENT_NAME obj, e.*
from sys.DBA_EXTENTS e
where e.SEGMENT_TYPE = 'INDEX'
and owner = 'SCHEMA_NAME_HERE' -- or comment this line
order by e.BYTES desc
more selects:
1. list of schema vs. size
select e.owner, to_char(sum(e.bytes),'999G999G999G999') total_size
from sys.DBA_EXTENTS e
group by e.owner
order by total_size desc
2. list of object vs. size
select e.owner || '.' || e.SEGMENT_NAME obj,
to_char(sum(e.bytes),'999G999G999G999') total_size
from sys.DBA_EXTENTS e
group by e.owner,e.SEGMENT_NAME
order by total_size desc
Sunday, June 15th, 2014 at 10:31.
Filed under oracle
.
