One of our clients, a technology manufacturing company from Argentina has, EBS R12 since 2009 and they have a big, 6.2T database. When we were implementing Katrina DevOps for their EBS environments we recommended 1 dev instance, 2 test instance and of course the prod instance and the argument that they used to reject the second test instance was the size of their database. So, we went for an Oracle E-Business Suite R12 Data purge project. After purging GL and Inventory, leaving only 2 years of data, the size of the database went from 6.2T to 2.9T. Of course, the tablespaces have almost the same space reserved but Gather Schema Statistics and other maintenance and usage of the purge tables performance increased drastically.
One of the last steps used in the process is export, drop and reimport of these purged tables, which means data defragmentation.
Always make sure to keep a backup and do all steps needed to keep information save.
Some of our scripts for your reference:
#sql: CREATE OR REPLACE DIRECTORY XX_GL_DUMP_DIR AS ‘/u03/ancap/’
expdp system/sysdesa2 TABLES=GL.GL_BALANCES directory=XX_GL_DUMP_DIR dumpfile=GL_BALANCES.dmp logfile=expdpTEST.log compression=all &
expdp system/sysdesa2 TABLES=GL.GL_JE_BATCHES directory=XX_GL_DUMP_DIR dumpfile=GL_JE_BATCHES.dmp logfile=GL_JE_BATCHES_exp.log compression=all &
expdp system/sysdesa2 TABLES=GL.GL_JE_HEADERS directory=XX_GL_DUMP_DIR dumpfile=GL_JE_HEADERS.dmp logfile=GL_JE_HEADERS_exp.log compression=all &
expdp system/sysdesa2 TABLES=GL.GL_JE_LINES directory=XX_GL_DUMP_DIR dumpfile=GL_JE_LINES.dmp logfile=GL_JE_LINES_exp.log compression=all &
expdp system/sysdesa2 TABLES=GL.GL_IMPORT_REFERENCES directory=XX_GL_DUMP_DIR dumpfile=GL_IMPORT_REFERENCES.dmp logfile=GL_IMPORT_REFERENCES_exp.log compression=all &
impdp system/sysdesa2 directory=XX_GL_DUMP_DIR dumpfile=GL_BALANCES.dmp logfile=GL_BALANCES_imp.log &
impdp system/sysdesa2 directory=XX_GL_DUMP_DIR dumpfile=GL_JE_BATCHES.dmp logfile=GL_JE_BATCHES_imp.log &
impdp system/sysdesa2 directory=XX_GL_DUMP_DIR dumpfile=GL_JE_HEADERS.dmp logfile=GL_JE_HEADERS_imp.log &
impdp system/sysdesa2 directory=XX_GL_DUMP_DIR dumpfile=GL_JE_LINES.dmp logfile=GL_JE_LINES_imp.log &
impdp system/sysdesa2 directory=XX_GL_DUMP_DIR dumpfile=GL_IMPORT_REFERENCES.dmp logfile=GL_IMPORT_REFERENCES_imp.log &
expdp system/sysdesa2 TABLES=INV.MTL_UNIT_TRANSACTIONS directory=XX_GL_DUMP_DIR dumpfile=MTL_UNIT_TRANSACTIONS.dmp logfile=MTL_UNIT_TRANSACTIONS_exp.log compression=all &
expdp system/sysdesa2 TABLES=INV.MTL_CST_ACTUAL_COST_DETAILS directory=XX_GL_DUMP_DIR dumpfile=MTL_CST_ACTUAL_COST_DETAILS.dmp logfile=MTL_CST_ACTUAL_COST_DETAILS_exp.log compression=all &
expdp system/sysdesa2 TABLES=INV.MTL_TRANSACTION_ACCOUNTS directory=XX_GL_DUMP_DIR dumpfile=MTL_TRANSACTION_ACCOUNTS.dmp logfile=MTL_TRANSACTION_ACCOUNTS_exp.log compression=all &
expdp system/sysdesa2 TABLES=INV.MTL_ACTUAL_COST_SUBELEMENT directory=XX_GL_DUMP_DIR dumpfile=MTL_ACTUAL_COST_SUBELEMENT.dmp logfile=MTL_ACTUAL_COST_SUBELEMENT_exp.log compression=all &
expdp system/sysdesa2 TABLES=INV.MTL_CST_TXN_COST_DETAILS directory=XX_GL_DUMP_DIR dumpfile=MTL_CST_TXN_COST_DETAILS.dmp logfile=MTL_CST_TXN_COST_DETAILS_exp.log compression=all &
expdp system/sysdesa2 TABLES=INV.MTL_TXN_REQUEST_HEADERS directory=XX_GL_DUMP_DIR dumpfile=MTL_TXN_REQUEST_HEADERS.dmp logfile=MTL_TXN_REQUEST_HEADERS_exp.log compression=all &
expdp system/sysdesa2 TABLES=INV.MTL_TXN_REQUEST_LINES directory=XX_GL_DUMP_DIR dumpfile=MTL_TXN_REQUEST_LINES.dmp logfile=MTL_TXN_REQUEST_LINES_exp.log compression=all &
impdp system/sysdesa2 directory=XX_GL_DUMP_DIR dumpfile=MTL_UNIT_TRANSACTIONS.dmp logfile=MTL_UNIT_TRANSACTIONS_imp.log &
impdp system/sysdesa2 directory=XX_GL_DUMP_DIR dumpfile=MTL_CST_ACTUAL_COST_DETAILS.dmp logfile=MTL_CST_ACTUAL_COST_DETAILS_imp.log &
impdp system/sysdesa2 directory=XX_GL_DUMP_DIR dumpfile=MTL_TRANSACTION_ACCOUNTS.dmp logfile=MTL_TRANSACTION_ACCOUNTS_imp.log &
impdp system/sysdesa2 directory=XX_GL_DUMP_DIR dumpfile=MTL_ACTUAL_COST_SUBELEMENT.dmp logfile=MTL_ACTUAL_COST_SUBELEMENT_imp.log &
impdp system/sysdesa2 directory=XX_GL_DUMP_DIR dumpfile=MTL_CST_TXN_COST_DETAILS.dmp logfile=MTL_CST_TXN_COST_DETAILS_imp.log &
impdp system/sysdesa2 directory=XX_GL_DUMP_DIR dumpfile=MTL_TXN_REQUEST_HEADERS.dmp logfile=MTL_TXN_REQUEST_HEADERS_imp.log &
impdp system/sysdesa2 directory=XX_GL_DUMP_DIR dumpfile=MTL_TXN_REQUEST_LINES.dmp logfile=MTL_TXN_REQUEST_LINES_imp.log &
I am not sure where you’re getting your information, but good topic. I needs to spend some time learning much more or understanding more.
Thanks for fantastic information I was looking for this information for my mission.