- Use the Database Upgrade Assistant (DBUA). The Database Upgrade Assistant can be launched by the Oracle Universal Installer, depending upon the type of installation that you select, and provides a graphical user interface (GUI) that guides you through the upgrade of a database. During installation, you can choose to not use the DBUA, instead choosing to launch it as a standalone tool at any time in the future to upgrade a database.
- Perform a manual upgrade. A manual upgrade provides a command-line upgrade of a database, using SQL scripts and utilities.
- Perform a full or partial export from your database, followed by a full or partial import into a new Oracle9i database. Export/Import can copy a subset of the data in a database. Export/Import leaves the database unchanged, and makes a copy of the data.
- Copy data from a database into a new Oracle9i database using the SQL*Plus COPY command or the AS clause of the CREATE TABLE SQL statement. Data copying can copy a subset of the data in a database. Data copying leaves the database unchanged, and makes a copy of the data.
The guide also describes the advantages and disadvantages of each method. You should review the migration guide to determine which method would be most beneficial in your environment.
With respect to your second question, it's difficult for me to say whether your code will run unmodified after the migration, or whether you will have to make changes. The only way to determine what the impact of a migration will be is to test it in a non-production environment, several times if possible. Doing this will not only give you some indication of what needs to be changed, it will also ensure that all applications supported by the database you are migrating will be sufficiently tested before the migration occurs in your production environment.
Patches for Oracle 8.1 and 9.2 can be downloaded from Oracle's Metalink Web site. To register for Metalink, you will need a valid Support Identifier (CSI). CSI numbers are unique support identifiers given to each customer with a current support contract. As an aside, if you are upgrading to Oracle 9.2, you might as well upgrade to the latest release which is 126.96.36.199 if it's available for your platform and operating system.
Dig Deeper on Oracle database export, import and migration
Related Q&A from Maria Anderson
I have been trying to install Oracle 8.1.7 on SUSE Linux 9.0 and got the error: "Error in invoking target install of makefile /opt/oracle/...../*.mk." Continue Reading
I'm getting errors while installing the Database Configuration Assistant. Continue Reading
Can I use /var/opt/oracle/oratab to specify listener information? Continue Reading