What to look for when downgrading from Oracle 10g to 9i?

Question

I having to downgrade my Oracle instance from 10g (10.1.0.2.0) to 9i (9.2.x.x.x). Not having planned on ever doing this, I didn't document 10g dependencies.

What are some of the dependencies on 10g that I will have to address?

Is there any type of query that I could perform to detect dependencies?

Of course I'm hoping for a magic bullet, not sifting through volumes of feature additions to compare with 100 klocs of PL/SQL.

By the way, we are not downgrading the database in place, we are migrating from a 10g instance to a separate 9i instance.

Answer 1

Take a look at the Oracle 10G New Features documentation to see what you need to not be using.

Answer 2

There are a host of parameters you could set to put you in 9i compatibility, turn off hash group by's etc.

You could start to make your 10g instance look a lot more like 9i without moving it anywhere. Won't be perfect but it's a start.

Answer 3

The biggies (that I know of). If you have functionality in these areas, take a closer look:

• Regular expressions are new in 10g.
• Nested table collection functions
• Some functionality for hierarchical queries (CONNECT_BY_ISCYCLE, NOCYCLE, ...)
• Objects and Collections refinements (new collection functions, modifying the max size of a varray after creation, ...)
• Inter row calculations (SQL MODEL clause).

@tony-andrews: as Tony mentions, there's probably more details in the 10g new features documentation that I'm sure my foggy and uninformed brain has left out.