Oracle schema size ...
set linesize 200 pagesize 200 timing on time on
col Owner for a28
col "HUMAN_READABLE" for a14
col "size" for 9999999999999
col "size GB" for 999999.99
select obj.owner "Owner"
--, obj_cnt "Objects",decode(seg_size, NULL, 0, seg_size) "size"
,decode(seg_size, NULL, 0, seg_size)/1024/1024/1024 "size GB"
,dbms_xplan.FORMAT_SIZE(decode(seg_size, NULL, 0, seg_size)) "HUMAN_READABLE"
from ( select /*+ parallel(8) */ owner, count(*) obj_cnt from dba_objects group by owner) obj,
( select owner, ceil(sum(bytes)) seg_size from dba_segments group by owner) segment
where obj.owner = segment.owner(+)
order by 2 desc
--order by 3 desc, 2 desc, 1
;
DEFINE schema_name = 'XXXX'
set linesize 300
col schema_size_gb for 9999999.99
SELECT sum(sizegb) schema_size_gb FROM (
-- tablesize !!!
--SELECT * FROM (
SELECT
owner, object_name, object_type, table_name, ROUND(bytes)/1024/1024/1024 AS sizegb,tablespace_name, extents, initial_extent,
ROUND(Sum(bytes/1024/1024) OVER (PARTITION BY table_name)) AS total_table_MB
FROM (
-- Tables
SELECT owner, segment_name AS object_name, 'TABLE' AS object_type,segment_name AS table_name, bytes,tablespace_name, extents, initial_extent
FROM dba_segments
WHERE segment_type IN ('TABLE', 'TABLE PARTITION', 'TABLE SUBPARTITION')
UNION ALL
-- Indexes
SELECT i.owner, i.index_name AS object_name, 'INDEX' AS object_type,i.table_name, s.bytes,s.tablespace_name, s.extents, s.initial_extent
FROM dba_indexes i, dba_segments s
WHERE s.segment_name = i.index_name
AND s.owner = i.owner
AND s.segment_type IN ('INDEX', 'INDEX PARTITION', 'INDEX SUBPARTITION')
--LOB Segments
UNION ALL
SELECT l.owner, l.column_name AS object_name, 'LOB_COLUMN' AS object_type,l.table_name, s.bytes,s.tablespace_name, s.extents, s.initial_extent
FROM dba_lobs l, dba_segments s
WHERE s.segment_name = l.segment_name
AND s.owner = l.owner
AND s.segment_type = 'LOBSEGMENT'
-- LOB Indexes
UNION ALL
SELECT l.owner, l.column_name AS object_name, 'LOB_INDEX' AS object_type,l.table_name, s.bytes,s.tablespace_name, s.extents, s.initial_extent
FROM dba_lobs l, dba_segments s
WHERE s.segment_name = l.index_name
AND s.owner = l.owner
AND s.segment_type = 'LOBINDEX'
)
WHERE owner in UPPER('&schema_name')
)
--WHERE total_table_MB > 10
--ORDER BY total_table_MB DESC, MB DESC
/
====
with count
set linesize 100 pagesize 200
col Owner for a28
select obj.owner "Owner", obj_cnt "Objects",decode(seg_size, NULL, 0, seg_size) "size GB"
from ( select owner, count(*) obj_cnt from dba_objects group by owner) obj,
( select owner, ceil(sum(bytes)/1024/1024/1024) seg_size from dba_segments group by owner) segment
where obj.owner = segment.owner(+)
order by 3 desc, 2 desc, 1;
col TABLE_NAME for a30
BREAK ON REPORT
COMPUTE SUM LABEL TOTAL OF meg ON REPORT
select * from (
SELECT owner,
table_name,
trunc(SUM(bytes) / 1024 / 1024) meg
FROM (SELECT segment_name table_name,
owner,
bytes
FROM dba_segments
WHERE segment_type = 'TABLE'
UNION ALL
SELECT i.table_name,
i.owner,
s.bytes
FROM dba_indexes i,
dba_segments s
WHERE s.segment_name = i.index_name
AND s.owner = i.owner
AND s.segment_type = 'INDEX'
UNION ALL
SELECT l.table_name,
l.owner,
s.bytes
FROM dba_lobs l,
dba_segments s
WHERE s.segment_name = l.segment_name
AND s.owner = l.owner
AND s.segment_type = 'LOBSEGMENT'
UNION ALL
SELECT l.table_name,
l.owner,
s.bytes
FROM dba_lobs l,
dba_segments s
WHERE s.segment_name = l.index_name
AND s.owner = l.owner
AND s.segment_type = 'LOBINDEX')
WHERE 1=1
-- owner NOT IN ('SYS', 'SYSTEM')
and owner IN ('xx')
GROUP BY table_name, owner
HAVING SUM(bytes) / 1024 / 1024 > 0 /* Ignore tables lower than 30 MB */
)
where 1=1
and meg!=0
ORDER BY meg DESC
/
