Problem: The database migration takes a long time to complete due to a huge amount of data stored in the ENTITY_CONFIG table.
Solution: Deactivate unused entities and delete them from the ENTITY_CONFIG table. Follow these steps:
create table entity_config_bkp as (SELECT * from entity_config A where a.entity_id IN (SELECT entity_id FROM entity B WHERE B.entity_id=A.entity_id and EXISTS (SELECT 1 FROM entity_inactive_view C WHERE C.guid = b.guid )) and trunc(insert_date) < TO_DATE('01-JAN-14'));
delete from entity_config A where a.entity_id IN (SELECT entity_id FROM entity B WHERE B.entity_id=A.entity_id and EXISTS (SELECT 1 FROM entity_inactive_view C WHERE C.guid = b.guid )) and trunc(insert_date) < TO_DATE('01-JAN-14'); Commit;
After deleting the inactive entities, run the following code to analyze the table and rebuild indexes:
BEGIN FOR table_entry IN (SELECT table_name FROM user_tables WHERE table_name ='ENTITY_CONFIG') LOOP EXECUTE IMMEDIATE 'ANALYZE TABLE ' || table_entry.table_name || ' ESTIMATE STATISTICS'; END LOOP; FOR index_entry IN (select INDEX_NAME from user_indexes where table_name ='ENTITY_CONFIG') LOOP EXECUTE IMMEDIATE 'ALTER INDEX ' || index_entry.INDEX_NAME || ' REBUILD'; END LOOP; END;