Product: Confluence

Environment

Error Message

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

at com.ibm.ws.webcontainer.webapp.WebAppDispatcherContext.sendError(WebAppDispatcherContext.java:597)
at com.ibm.ws.webcontainer.srt.SRTServletResponse.sendError(SRTServletResponse.java:827)
at javax.servlet.http.HttpServletResponseWrapper.sendError(HttpServletResponseWrapper.java:152)
at javax.servlet.http.HttpServletResponseWrapper.sendError(HttpServletResponseWrapper.java:152)
at com.opensymphony.module.sitemesh.filter.PageResponseWrapper.sendError(PageResponseWrapper.java:176)
at com.opensymphony.webwork.dispatcher.ServletDispatcher.sendError(ServletDispatcher.java:349)
at com.opensymphony.webwork.dispatcher.ServletDispatcher.serviceAction(ServletDispatcher.java:235)
at com.opensymphony.webwork.dispatcher.ServletDispatcher.service(ServletDispatcher.java:199)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:856)
at com.ibm.ws.webcontainer.servlet.ServletWrapper.service(ServletWrapper.java:1282)
at com.ibm.ws.webcontainer.servlet.ServletWrapper.service(ServletWrapper.java:1239)
at com.ibm.ws.webcontainer.filter.WebAppFilterChain.doFilter(WebAppFilterChain.java:136)
at com.opensymphony.module.sitemesh.filter.PageFilter.parsePage(PageFilter.java:118)
at com.atlassian.confluence.util.profiling.ProfilingPageFilter.parsePage(ProfilingPageFilter.java:137)
at com.opensymphony.module.sitemesh.filter.PageFilter.doFilter(PageFilter.java:54)
at com.ibm.ws.webcontainer.filter.FilterInstanceWrapper.doFilter(FilterInstanceWrapper.java:142)
at com.ibm.ws.webcontainer.filter.WebAppFilterChain.doFilter(WebAppFilterChain.java:121)
at com.atlassian.core.filters.ServletContextThreadLocalFilter.doFilter(ServletContextThreadLocalFilter.java:21)
at com.ibm.ws.webcontainer.filter.FilterInstanceWrapper.doFilter(FilterInstanceWrapper.java:142)
at com.ibm.ws.webcontainer.filter.WebAppFilterChain.doFilter(WebAppFilterChain.java:121)

Symptom

First installing confluence to a new oracle database. Error messages in logs are....
[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:@oracle-stable.dsto.defence.gov.au:1521:logdb
[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'

Diagnostics/Test

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.

Root Cause

Insufficient privileges to create tables in database.

Solution

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.