A full database export and import can be a good way to replicate or clean up a database. However, to avoid problems be sure to keep the following points in mind:
A full export does not export triggers owned by schema
SYS. You must manually re-create
SYS triggers either before or after the full import. Oracle recommends that you re-create them after the import in case they define actions that would impede progress of the import.
A full export also does not export the default profile. If you have modified the default profile in the source database (for example, by adding a password verification function owned by schema
SYS), then you must manually pre-create the function and modify the default profile in the target database after the import completes.
If possible, before beginning, make a physical copy of the exported database and the database into which you intend to import. This ensures that any mistakes are reversible.
Before you begin the export, it is advisable to produce a report that includes the following information:
A list of tablespaces and data files
A list of rollback segments
A count, by user, of each object type such as tables, indexes, and so on
This information lets you ensure that tablespaces have already been created and that the import was successful.
If you are creating a completely new database from an export, then remember to create an extra rollback segment in
SYSTEM and to make it available in your initialization parameter file (
ora)before proceeding with the import.
When you perform the import, ensure you are pointing at the correct instance. This is very important because on some UNIX systems, just the act of entering a subshell can change the database against which an import operation was performed.
Do not perform a full import on a system that has more than one database unless you are certain that all tablespaces have already been created. A full import creates any undefined tablespaces using the same data file names as the exported database. This can result in problems in the following situations:
If the data files belong to any other database, then they will become corrupted. This is especially true if the exported database is on the same system, because its data files will be reused by the database into which you are importing.
If the data files have names that conflict with existing operating system files.