ORACLE DB compression best approach

[mamartins]

I have DBA background but ORACLE isn't my main DB engine. I'm starting a project for upgrading ORACLE from 12.1 to 19c and on top of it, get better space management.

I already made a test with ADO/ILM, but SAP choose to hide it and not develop any tool to manage it, so it's a no go.

My test case will be a SOLMAN system that have 2 schemas, one for ABAP and the second for the JAVA instance. The key thing here on the ABAP schema is that have "standard" OLTP tables and BW tables. On top of this, there is also LOB objects.

I'm seeking validation for the best approach to tackle this. My task list (according to my readings) will be:

1. Compress indexes. Do you know any tool do this automatically? Or I have to identify the largest ones and manually compress them?
2. Create new tablespace using BR*TOOLS
3. Reorganize tables migrating them to the new tablespace
4. What happens to the LOB objects? Any special care here?
5. BW tables will remain on the main tablespace or will be migrated to DIM, FACT and ODS ones?

Best regards!

[JamesZ · Accepted solution]

This FAQ should covers most of the questions:
1289494 - FAQ: Oracle compression

[janos_mucsi-besze]

You could use SWPM to perform the DB upgrade with a System Copy approach, that way SWPM can turn on ADO/ILM throughout the process and you end up with a full supported SAP standard (nothing else is supported) compression method