'java.sql.SQLException ORA-00942 table or view does not exist' due to database permissions
Platform notice: Server and Data Center only. This article only applies to Atlassian products on the Server and Data Center platforms.
Support for Server* products ended on February 15th 2024. If you are running a Server product, you can visit the Atlassian Server end of support announcement to review your migration options.
*Except Fisheye and Crucible
Problem
After installation, Confluence will not start. The following appears in the user interface:
Original Exception:
Error Message: Hibernate operation: Could not execute query; bad SQL grammar []; nested exception is java.sql.SQLException: ORA-00942: table or view does not exist
Error Code: 500
Target Servlet: action
Error Stack:
com.ibm.ws.webcontainer.webapp.WebAppErrorReport: Hibernate operation: Could not execute query; bad SQL grammar []; nested exception is java.sql.SQLException: ORA-00942: table or view does not exist
The following appears in the atlassian-confluence.log:
[9/11/07 13:17:33:766 CST] 0000001d C3P0Connectio I net.sf.hibernate.connection.C3P0ConnectionProvider configure C3P0 using driver: oracle.jdbc.OracleDriver at URL: jdbc:oracle:thin:@localhost:1521:confluence
[9/11/07 13:17:33:781 CST] 0000001d C3P0Connectio I net.sf.hibernate.connection.C3P0ConnectionProvider configure Connection properties:
{user=C3IDWIKIUSER, password=Password1, SetBigStringTryClob=true}
[9/11/07 13:17:35:156 CST] 0000001d SchemaExport E net.sf.hibernate.tool.hbm2ddl.SchemaExport execute Unsuccessful: create table CONFVERSION (CONFVERSIONID number(19,0) not null, BUILDNUMBER number(10,0) not null unique, INSTALLDATE date, VERSIONTAG varchar2(255), CREATIONDATE date, LASTMODDATE date, primary key (CONFVERSIONID))
[9/11/07 13:17:35:188 CST] 0000001d SchemaExport E net.sf.hibernate.tool.hbm2ddl.SchemaExport execute ORA-01950: no privileges on tablespace 'USERS'
[9/11/07 13:17:35:219 CST] 0000001d SchemaExport E net.sf.hibernate.tool.hbm2ddl.SchemaExport execute Unsuccessful: create table os_user_group (group_id number(19,0) not null, user_id number(19,0) not null, primary key (user_id, group_id))
[9/11/07 13:17:35:219 CST] 0000001d SchemaExport E net.sf.hibernate.tool.hbm2ddl.SchemaExport execute ORA-01950: no privileges on tablespace 'USERS'
[9/11/07 13:17:35:234 CST] 0000001d SchemaExport E net.sf.hibernate.tool.hbm2ddl.SchemaExport execute Unsuccessful: create table PLUGINDATA (PLUGINDATAID number(19,0) not null, PLUGINKEY varchar2(255) not null unique, FILENAME varchar2(255) not null unique, LASTMODDATE date, DATA blob, primary key (PLUGINDATAID))
[9/11/07 13:17:35:250 CST] 0000001d SchemaExport E net.sf.hibernate.tool.hbm2ddl.SchemaExport execute ORA-01950: no privileges on tablespace 'USERS'
Cause
The database user does not have sufficient privileges to create tables in database.
Diagnosis
Try connecting to your oracle database with a database client such as sqldeveloper or dbvisualizer using the login credentials of your confluence database user. Once you are connected, try and manually create a table in the database.
create table t ( a number, b varchar2(10) );If you are thrown a permission error, then your confluence database user does not have sufficient privileges to create tables in the database. After failing to create tables confluence then tries to insert data into the tables and throws the above error.
Resolution
The confluence database user should have at least the connect and resource privilege to the confluence database. If you are unsure how to grant this permission, talk to your oracle DBA. The user and password are stored in the <confluence-data>/confluence.cfg.xml file as the hibernate user and hibernate password elements.
In this particular case the resource role is missing: this is the role that grants the ability for users to create objects in their own schema.