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;