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

Question

We have an application (well, 3) that uses Oracle 9i, however it is ending soon, and we need to switch to another platform - Oracle 11 or Postgress, which are obvious.

I heard comments that the transfer from 9i to 11 is not direct, but no one here can confirm, anyway. I'm not too worried that “oh, the query plan engine has changed,” I'm more interested in “this feature no longer exists.”

None of the applications are complex - this is either the main bastard material on the SQL client side (and a large search routine on several tables, but we are going to rewrite that using Lucene anyway), or in PL / SQL.

Postgress has just appeared as a simpler and cheaper target for the port, but the cost of the license is offset by the training costs for our database administrators.

Any comments on the experience are appreciated.

Answer 1

Some notes:

• The CREATE VIEW privilege is part of the CONNECT role in 9i. But not in 10g. Perhaps also in 11G. You can add a grant to your installation scripts.
• 11G has a case sensitive password. Therefore, if you cannot connect, this can be a problem.

Answer 2

Oracle 9i 10g, : http://www.oracle.com/technology/products/bi/db/10g/pdf/twp_bidw_optimizer_10gr2_0208.pdf

10g 11g . . http://download.oracle.com/docs/cd/B28359_01/readmes.111/b28280/toc.htm#BABGIGDC

Answer 3

-, :

9i 10g DISTINCT.

9i

SELECT DISTINCT X
FROM   Y;

X (, "ORDER BY X", , ).

10g... DISTINCT , .

( , .)

Answer 4

, , .

... . aks dudes dudettes, , . , ?

, .

Answer 5

, , UTL_HTTP, . , , . , Oracle, - , .

If you have already paid for Oracle licenses, a lot of the cost is already incurred. Maintenance and support costs may be lower for Postgres, but you still want to pay for support on the day the database goes down and the restore from the backup is not going as expected.

PS. Have you considered the built-in functionality of Oracle Text, not Lucene?