We are trying to migrate a 270-gig database from Oracle 22.214.171.124 32-bit to 126.96.36.199 64-bit under AIX 5.2. I would like to know the best way to migrate to locally managed tablespaces with automatic segment space management. We are running SAP application.
I think that if I were doing that, I'd try to use the "ALTER TABLE MOVE" syntax after the upgrade. The destination tablespace for the move should be a new (locally-managed, ASSM) tablespace. Once all objects are moved or rebuilt from the old tablespace to the new ones, you can drop the old tablespaces.
The only other option would be to export/import a table or two at a time. You'd have to determine which one is faster by testing both. However, I feel that the ALTER TABLE MOVE method is probably less risky since it doesn't include a DROP TABLE in the procedure (as you would need to do for export/import).
Dig Deeper on Oracle database installation, upgrades and patches