Summary

Confluence environment is migrated to a different Oracle database schema/username. However, when Confluence starts multiple 'ORA-00955: Name is already used by an existing object' are observed, impacting functionality. 

Diagnosis

An ORA-00955 is triggered when an attempt was made to create a database object (such as a table, view, cluster, index, or synonym) that already exists. 
The initial troubleshooting in this situation should cover the following checkpoints: 

• Make sure you connect to Confluence with a username that matches the schema to which the new database tables are being created into.
• Check that the "ALL_OBJECTS" view exists for the Oracle user schema
• Validate the database permissions for the user under Step 2 in Database Setup for Oracle. Not having the proper privileges can cause Confluence to erroneously see a table from another schema, if the other schema shares the same table name.

All these points seem correct when reviewed by your Oracle DBA. 

Cause

In older versions of Confluence (till Confluence 6.3) the official documentation Database Setup for Oracle explicitly requested to add a local ALL_OBJECTS view to the user's schema, to prevent a conflict that can occur when a table exists in another schema with the same name as one of the Confluence tables. This was a workaround for bug:  CONFSERVER-3613 - Getting issue details... STATUS

Recent versions of the same documentation (starting in Confluence 6.4 and later) had no mention about this local ALL_OBJECTS view.  Hence, if you had an old confluence environment which was installed on a version previous to Confluence 6.3, and later upgraded to a most recent version, you might have still the local  ALL_OBJECTS view in your Oracle database. 

To check this, you can use the following SQL statements: 

SQL> select count(1) from ALL_OBJECTS where owner='<OLD_ORACLE_CONFLUENCE_USERNAME>';
COUNT(1)
----------- 
          1086                

SQL> select count(1) from ALL_OBJECTS where owner='<NEW_ORACLE_CONFLUENCE_USERNAME>';
COUNT(1)
----------- 
               0     

Solution

When you do a export/import of an Oracle schema and you want to change the username as part of the migration, this local ALL_OBJECTS view needs to be recreated with the new username.

If not, it will report there is no access granted to the right tables.

1. Stop Confluence

2. Drop the existing local ALL_OBJECTS view:

   • Oracle

   drop view <OLD_ORACLE_CONFLUENCE_USERNAME>.all_objects ;              

3. Create a new ALL_OBJECTS view that is owned by the new username: 

   • Oracle

   create view <NEW_ORACLE_CONFLUENCE_USERNAME>.all_objects as
   select *
   from sys.all_objects
   where owner = upper('<NEW_ORACLE_CONFLUENCE_USERNAME>');

4. Start Confluence