OATM export import testcase

You can use export and import to migrate tablespaces to OATM, in cases where OATM is unable to do the migration without errors:

Here's how I tested:

Export a table

Drop the table

Change default tablespace of user to a new tablespace (Done by OATM automatically)

Drop the tablespace in which the table existed

import the table.

Table now exists in new tablespace.

This is not supported by Oracle and you should do this only as a last resort.

Improve imp and exp performance

orafaq.com has a very good information on imp and exp here. I am reproducing what they have written on improving imp and exp performance:

How can one improve Import/ Export performance?

EXPORT:

• Set the BUFFER parameter to a high value (e.g. 2M)
• Set the RECORDLENGTH parameter to a high value (e.g. 64K)
• Stop unnecessary applications to free-up resources for your job.
• If you run multiple export sessions, ensure they write to different physical disks.
• DO NOT export to an NFS mounted filesystem. It will take forever.

IMPORT:

• Create an indexfile so that you can create indexes AFTER you have imported data. Do this by setting INDEXFILE to a filename and then import. No data will be imported but a file containing index definitions will be created. You must edit this file afterwards and supply the passwords for the schemas on all CONNECT statements.
• Place the file to be imported on a separate physical disk from the oracle data files
• Increase DB_CACHE_SIZE (DB_BLOCK_BUFFERS prior to 9i) considerably in the init$SID.ora file
• Set the LOG_BUFFER to a big value and restart oracle.
• Stop redo log archiving if it is running (ALTER DATABASE NOARCHIVELOG;)
• Create a BIG tablespace with a BIG rollback segment inside. Set all other rollback segments offline (except the SYSTEM rollback segment of course). The rollback segment must be as big as your biggest table (I think?)
• Use COMMIT=N in the import parameter file if you can afford it
• Use ANALYZE=N in the import parameter file to avoid time consuming ANALYZE statements
• Remember to run the indexfile previously created

Export from 8i and import to 10g

Today, Sandeep asked me to confirm whether it is possible to import an 8i export dump file into 10g using data pump (impdp). As per metalink note 462261.1:

Regular export/import (exp/imp) utilities are different from DataPump expdp/impdp
utilities. The dump written by regular export/import (written with exp/imp utility) cannot be handled by DataPump import (expdp/impdp utility).

ERROR MESSAGES:

ORA-39000: bad dump file specification
Cause: The user specified a dump file that could not be used in the current job.
Action: Specify a dump file that is usable for the job.

ORA-31640: unable to open dump file "string" for read
Cause: Import was unable to open the export file for reading.
Action: Take appropriate action to restore the device.

From the error messages it is clear that Datapump is not able to read the dump file.

SOLUTION:

Use the regular import (imp utility - not the 10g IMDP) version 10g (10.2) to read the export dump written by exp utility version 8i.

Reference:http://download.oracle.com/docs/cd/B28359_01/server.111/b28319/dp_overview.htm#i1009203

Note:
Dump files generated by the Data Pump Export utility are not compatible with dump files generated by the original Export utility. Therefore, files generated by the original Export (exp) utility cannot be imported with the Data Pump Import (impdp) utility.

You could also refer to metalink note 132904.1 which has the Compatibility Matrix for Export & Import Between Different Oracle Versions.