What procedure is most efficient for upgrading my Oracle8i database to 9i? I currently have 8i on Windows 2000....
We're also looking at upgrading to Win2003 in a few months. There are plenty of factors to consider when upgrading and choosing an upgrade method. Most efficient depends on a lot of things including your comfort level and ability to perform the chosen upgrade method.
Here are some things to consider:
If you're not using locally-managed tablespaces, an upgrade is a time that you may consider converting. If you do, then export/import is probably easiest, but creating new tablespaces and using ALTER TABLE MOVE or CTAS may be better for some tables depending on your application and how it uses the tables.
If you have a short window for the upgrade and have a lot of data, then export/import probably won't be an option for you since it may take significantly longer to complete.
Don't shy away from using the tried and tested upgrade procedure. I seem to talk with a lot of DBAs who prefer export/import just because they prefer NOT going through the upgrade steps. Upgrading a database isn't hard (it's getting easier each release) and it can be much, much faster than export/import.
What are you comfortable with? Your abilities and comfort level will be the biggest factors in your success.