import

impdp too smart for its own good - ORA-2000 and missing indexes on target...

As we move on to 12.2 and 19c we're seeing more instances where simple schema export/import migrations are missing objects on the target database.

If you do object counts after a simple schema migration... Or see something like this pop up in your impdp logs:

ORA-01440 trying to convert FLOATs to NUMBERs

I recently came across a legacy database that was using Oracle FLOAT datatype for Primary and Foreign Keys. As the database needed some attention I was asked to get data types in order and get rid of the FLOATs in favor of NUMBER data types.

Unloading data from Oracle?

I recently fielded a question about getting data out of Oracle quickly. Without much detail on the systems involved here is my answer:

Question:

I need to migrate data from Oracle to MySQL quickly (in less than 1 day). What are my options?

My answer:

Oracle does not supply an out-of-the-box unload utility.

Keep in mind without comprehensive info about your environment (oracle version? server platform? how much data? what datatypes?) everything here is YMMV and you would want to give it a go on your system for performance and timing.

Oracle imp utility COMMIT parameter and LOBS…

If you have a lot of LOB data to move around with Oracle import/export utilities this might help you out. The default for the COMMIT parameter is No.

COMMIT=N : For tables that have a LOB column, commit is performed after loading each table
This is much faster but be aware that you will use more Rollback space since each table will be committed in one large transaction.

COMMIT=Y : For tables that have a LOB column, commit is performed after each row (slow!, much overhead added because so many transactions are used to import the data)

Subscribe to import