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;
/

MOVING OBJECTS TO NEW TABLESPACES WITH IMPDP

The scenario consists in having about 180 tablespaces and wish to move the objects to a set of 10 news tablespaces (based on the table size criteria for every schema). The “alter table…move…” technique generates thousands of individual orders and is very difficult to trace. For that reason I've used impdp and a few orders easy to follow for anyone.

1) Export the schemas to reorganize:
expdp system schemas=dwh01, dwh02 directory=exports dumpfile=dwh01-2_dp_1.dmp, ...
logfile=dwh01-2_dp.log content=all keep_master=N parallel=3 filesize=25G

2) Create new tablespaces.

3) Create new users (we can't rename it) or drop and recreate the existing ones

4) Import one schema at a time: impdp system PARFILE=dwh01_P.par

Example.par:
------------
directory=exports
dumpfile=dwh01-2_dp_1.dmp, dwh01-2_dp_2.dmp, ...
logfile=dwh01_import_P.log
content=all
keep_master=N
parallel=4
REMAP_SCHEMA=DWH01:PRUEBA01
EXCLUDE=PACKAGE
EXCLUDE=PACKAGE_BODY
EXCLUDE=FUNCTION
REMAP_TABLESPACE=AWCUBO:DWHTSDATP01,...
REMAP_TABLESPACE= ...,
TABLES=DWH01.LKBACAMPO01,...

5) Recreate invalid or unusable indexes

6) Recompile

7) Check the quantity of objects between source and destination

select owner, count(table_name)
from dba_tables
where (OWNER LIKE 'PRUEBA0_' OR OWNER LIKE 'DEMETRIO0_' OR OWNER LIKE 'D__0_')
group by owner
order by 1;