Comparing several schemas with dbms_space.object_space_usage

This is useful, for example, if we import or refresh a schema to another instance.
In this example we'll compare schemas DWH03 and PRUEBA03.

set serveroutput on

DECLARE
CURSOR C IS select OWNER, SEGMENT_NAME, SEGMENT_TYPE,100, PARTITION_NAME
from dba_segments
where (OWNER LIKE 'PRUEBA03' OR OWNER LIKE 'DWH03')
and segment_name not like 'BIN%'
AND PARTITION_NAME IS NULL
and SEGMENT_TYPE in ('TABLE', 'INDEX', 'CLUSTER')
group by owner, SEGMENT_NAME, SEGMENT_TYPE, PARTITION_NAME
order by owner, SEGMENT_TYPE, SEGMENT_NAME;

F C%rowtype;
SPACE_USED NUMBER;
SPACE_ALLOCATED NUMBER;
TOTAL1 NUMBER;TOTAL2 NUMBER;

BEGIN
DBMS_OUTPUT.ENABLE(999999999);
open c;
fetch c into f;
TOTAL1:=0; TOTAL2:=0;
LOOP
EXIT WHEN C%NOTFOUND;
dbms_space.object_space_usage(F.OWNER, F.SEGMENT_NAME,
F.SEGMENT_TYPE,100, F.PARTITION_NAME,SPACE_USED,
SPACE_ALLOCATED);
if F.OWNER='DWH03' THEN TOTAL1:=TOTAL1+SPACE_USED;
elsif F.OWNER='PRUEBA03' THEN TOTAL2:=TOTAL2+SPACE_USED;
end if;
dbms_output.put_line
(F.OWNER','F.SEGMENT_TYPE','F.SEGMENT_NAME','F.PARTITION_NAME','SPACE_USED','SPACE_ALLOCATED);
fetch c into f;
END LOOP;
close c;
dbms_output.put_line('SUMA DE DWH03 = 'TOTAL1);
dbms_output.put_line('SUMA DE PRUEBA03 = 'TOTAL2);
END;
/