Given a db user name list the count of objects based on object type and the amount of space used by each object type
SET LINESIZE 200
SET PAGESIZE 9999
COLUMN owner FORMAT A15 HEADING "OWNER"
COLUMN tablespace_name FORMAT a15 HEADING "TABLESPACE NAME"
COLUMN segment_type FORMAT A10 HEADING "SEGMENT TYPE"
COLUMN segment_name FORMAT A20 HEADING "SEGMENT NAME"
COLUMN bytes FORMAT 9,999,999,999,999 HEADING "SIZE (IN BYTES)"
COLUMN seg_count FORMAT 9,999,999,999 HEADING "SEGMENT COUNT"
break on report on owner skip 2
--compute sum label "" of seg_count bytes on owner
compute sum label "Grand Total: " of seg_count bytes on report
SELECT
owner
, tablespace_name
, segment_name
, segment_type
, sum(bytes) bytes
, count(*) seg_count
FROM
dba_segments
where lower(owner)='&owner'
GROUP BY
owner
, tablespace_name
, segment_type
, segment_name
ORDER BY
owner
, tablespace_name
, segment_type
/
SET PAGESIZE 9999
COLUMN owner FORMAT A15 HEADING "OWNER"
COLUMN tablespace_name FORMAT a15 HEADING "TABLESPACE NAME"
COLUMN segment_type FORMAT A10 HEADING "SEGMENT TYPE"
COLUMN segment_name FORMAT A20 HEADING "SEGMENT NAME"
COLUMN bytes FORMAT 9,999,999,999,999 HEADING "SIZE (IN BYTES)"
COLUMN seg_count FORMAT 9,999,999,999 HEADING "SEGMENT COUNT"
break on report on owner skip 2
--compute sum label "" of seg_count bytes on owner
compute sum label "Grand Total: " of seg_count bytes on report
SELECT
owner
, tablespace_name
, segment_name
, segment_type
, sum(bytes) bytes
, count(*) seg_count
FROM
dba_segments
where lower(owner)='&owner'
GROUP BY
owner
, tablespace_name
, segment_type
, segment_name
ORDER BY
owner
, tablespace_name
, segment_type
/
Comments