Simply Renaming A Schema

What started off as just moving a database from UAT to QA soon turned into something slightly more complex, with an interesting discovery for me.

For reasons that preceed my involvement in this project, not only did the database name indicate the region (UAT, QA, production), but the username for the application schema also indicated the region. This didn’t fit with the standard, but the work required to change this in all regions wasn’t considered viable.

Anyway, this left me in a position of having copied and renamed the UAT database to QA, but the application username/schema indicated UAT. This was clearly a recipe for later confusion… What I needed was to rename the schema, but unfortunately Oracle doesn’t provide this as one-liner.

This is a 9.2.0.8 database, so it was time for imp and exp… “Just a case of export user, create new appropriately named user, import from original user to new user, drop original user if all goes smoothly.” Well, it didn’t.

The export went fine, but when importing I hit ORA-02304 errors in more than one place. This was a new one on me. An example of the errors is shown below:

IMP-00003: ORACLE error 2304 encountered
ORA-02304: invalid object identifier literal
IMP-00017: following statement failed with ORACLE error 2304:
"CREATE TYPE "xxxxxxx" TIMESTAMP 'yyyy-mm-dd:hh24:mi:ss' OID '###############"

Oracle was complaining about the fact that I was trying to create objects with same object identifier (OID) as objects that already exist in the database. I had decided that keeping the original schema in place until I’d the new user was successfully imported was a sensible thing to do just in case things didn’t go to plan, but as it turned out keeping the original user in the database whilst importing under the new user was the root of my problem.

If you found this page because, like me, you haven’t yet got to the point of checking MetaLink as the first port of call then check out MetaLink note 1066139.6 for more detail.