RDBMS versions involved : 12.1, 19c
Operating systems : HP UX, RHEL 7.9
I did an export of a schema named SKUMASTER from production (version 12.1 in HPUX) and imported it into a development database which is of version 19c hosted in a RHEL 7.9 server.
At the source (production), the schema size is : 137.12 GB . I exported this schema using expdp and then imported the dump file into the 19c Dev database.
In the Dev database, the schema size is only 85.94 GB !!! , which is like 37% smaller. I have confirmed that the object counts between source and target schemas are same.
The difference in these size must be due to fragmentation in table/index segments.
So, I want find those tables which are fragmented in production and may be run the following to save some space
Step 1. alter table mytable enable row movement;
Step 2. alter table mytable shrink space compact;
Step 3. alter table mytable shrink space;
Step 4. alter table mytable disable row movement;
Details from Source and Target databases:
--- Source side (Production, version 12.1 but non-CDB )
SQL> select object_type, count(*) from dba_objects where owner in ('SKUMASTER')
GROUP BY OBJECT_tYPE
order by count(*) desc; 2 3
OBJECT_TYPE COUNT(*)
----------------------- ----------
INDEX 628
TABLE 246
SEQUENCE 101
LOB 5
SQL> select sum(bytes/power(1024,3)) sizegb from dba_segments where owner = 'SKUMASTER';
SIZEGB
----------
137.121643
SQL> select banner from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
PL/SQL Release 12.1.0.2.0 - Production
CORE 12.1.0.2.0 Production
TNS for HPUX: Version 12.1.0.2.0 - Production
NLSRTL Version 12.1.0.2.0 - Production
--- Target side, after the import using impdp (a 19c Pluggable Database)
SQL> select object_type, count(*) from dba_objects where owner in ('SKUMASTER')
GROUP BY OBJECT_tYPE
order by count(*) desc; 2 3
OBJECT_TYPE COUNT(*)
----------------------- ----------
INDEX 628
TABLE 246
SEQUENCE 101
LOB 5
SQL> select sum(bytes/power(1024,3)) sizegb from dba_segments where owner = 'SKUMASTER';
SIZEGB
----------
85.9415283
SQL> select banner from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
SQL>