Porting an application from Oracle 9 to Oracle 11 - gottchas?

Question

We have an application (well, 3) which uses Oracle 9i, however this is being end-of-life-ed shortly, and we need to move to another platform - Oracle 11 or Postgress being the obvious ones.

I've heard comments to the effect that porting from 9i to 11 isn't straight forward, but no-one here can confirm one way or the other. I'm not overly concerned with "oh, the query plan engine changed", I'm more concerned with "this feature isn't there anymore".

None of the apps are complex - it's either basic crud stuff in client-side SQL (and a big multi-table search routine, but we are going to rewrite that using Lucene anyway), or the same in PL/SQL.

Postgress only came up as a possibly easier and cheaper target to port to, but the license cost is offset by the training cost for our DBAs.

Any experience comments appreciated.

Answer 1

Well the rule base optimizer is gone or almost gone so query plans indeed change.

But missing features... I don't know any. You should aks the dudes and dudettes who told you about missing features to back that statement up. Ask them which features are missing?

I wouldn't be suprised if they couldn't give examples.

Answer 2

Some things to note:

• CREATE VIEW privilege is part of CONNECT role in 9i. But not in 10g. Probably also in 11G. You may want to add a grant to your installation scripts.
• 11G has case sensitive passwords. So if you find yourself unable to connect this may be the problem.

Answer 3

Here's a useful whitepaper from Oracle on some of the changes between 9i and 10g that will get you started: http://www.oracle.com/technology/products/bi/db/10g/pdf/twp_bidw_optimizer_10gr2_0208.pdf

The documentation sets for 10g and 11g will give you a very thorough list of new and deprecated features, and of modified default behaviour. eg. http://download.oracle.com/docs/cd/B28359_01/readmes.111/b28280/toc.htm#BABGIGDC

Answer 4

There were some changes to the privileges required for things like UTL_HTTP to tighten up security. But really there's nothing that would break which can't be fixed pretty easily. The last thing Oracle want to do is break applications already running on their database.

If you've already paid for Oracle licences, the big cost has already been incurred. Your maintenance and support costs may be lower for Postgres but you'd still want to be paying for support for that day when the DB goes down and the recovery from backup isn't going as expected.

PS. Have you looked at the built-in Oracle Text functionality as opposed to Lucene ?

Answer 5

Something that hit us:

Between 9i and 10g the ordering behaviour of DISTINCT changed.

In 9i, you could write

SELECT DISTINCT X
FROM   Y;

and the rows would come back ordered by X (clearly you should have included a "ORDER BY X" clause, but sometimes people forgot because it worked without).

This isn't the case in 10g... the implementation of DISTINCT has changed and the rows will now typically come back in a non-ordered manner.

(Easy enough to fix once you've found the offending code.)