Problem

 This problem is very specific and you will most probably not encounter it. However, in case you are experiencing it, please continue reading

Oracle TEMP tablespace keeps on increasing at a rapid rate without any significant pattern. The TEMP tablespace will only be reduced if you restart the application.

Diagnosis

Environment

Oracle uses TEMP tablespace in order to store data used while executing database queries. It is heavily used for table scans, joins and sorting. All this data is stored in this TEMP tablespace and then cleared out once the database connection session is destroyed.

The problem happens on Confluence 5.10.8 with Oracle 12c database and if you have JDBC Datasource on Tomcat level configured (defined in either server.xml or context.xml)

 Please note that this is not a bug in Confluence

Diagnostic Steps

If you see this behavior, there several steps that you need to check before confirming the problem, as this could be related to many other issues. Here's a checklist of what you need to check

1. Verify that the all_bjects view is created. If not, please follow this guide to do so: Database Setup for Oracle
2. Make sure that you are running a supported Database JDBC Driver

3. You will need to understand what is being stored in the TEMP tablespace. Please use the below query to understand that

   SELECT   S.sid || ',' || S.serial# sid_serial, 
            S.username,
            T.blocks * 8192 / 1024 / 1024 mb_used, 
            T.tablespace,
            Q.sql_fulltext, 
            q.sql_id
   FROM v$sort_usage T
     join v$session S on T.session_addr = S.saddr
     left join v$sqlarea Q on T.sqladdr = Q.address
   ORDER BY mb_used desc, sid_serial;

4. Check for frequent queries in that list and check whether those are linked to a specific plugin. If yes, try to disable that plugin for a day or two and monitor the size of the TEMP tablespace

5. You will need to check the objects held in the TEMP tablespace. You can use the below query to inspect those objects as well as the status of those sessions

   select 
      srt.tablespace, 
      srt.segfile#, 
      srt.segblk#, 
      srt.blocks, 
      a.sid, 
      a.serial#, 
      a.username, 
      a.osuser, 
      a.status 
   from 
      v$session    a,
      v$sort_usage srt 
   where 
      a.saddr = srt.session_addr 
   order by 
      srt.tablespace, srt.segfile#, srt.segblk#, 
      srt.blocks;

   P.S. Sessions with status INACTIVE will not be freed from the TEMP tablespace

Cause

If you went through the above checklist and the problem is still occurring, this means that we have something going wrong in the JDBC configuration. It could be one of the following:

1. Database Connections are way less than Tomcat max threads. This will keep database sessions open in order to serve more requests. Those sessions will be holding the objects in the TEMP tablespace and you will see it growing over time
2. JDBC Configuration is causing the database sessions to misbehave

Resolution

To resolve this issue, you will need to change the JDBC database configuration. If you are using a JDBC Datasource at Tomcat level, revert back to the default Hibernate JDBC configuration found in confluence.cfg.xml

	<property name="hibernate.c3p0.acquire_increment">1</property>
    <property name="hibernate.c3p0.idle_test_period">100</property>
    <property name="hibernate.c3p0.max_size">48</property>
    <property name="hibernate.c3p0.max_statements">0</property>
    <property name="hibernate.c3p0.min_size">20</property>
    <property name="hibernate.c3p0.timeout">30</property>
    <property name="hibernate.connection.SetBigStringTryClob">true</property>
    <property name="hibernate.connection.driver_class">oracle.jdbc.OracleDriver</property>
    <property name="hibernate.connection.isolation">2</property>
    <property name="hibernate.connection.password">*******</property>
    <property name="hibernate.connection.url">jdbc:oracle:thin:@ORACLE_DB_HOSTNAME:ORACLE_DB_PORT/SID</property>
    <property name="hibernate.connection.username">confluence-db-user</property>
    <property name="hibernate.dialect">net.sf.hibernate.dialect.OracleIntlDialect</property>
    <property name="hibernate.setup">true</property>

If you are already using the Hibernate JDBC configuration, change it to a JDBC Datasource on Tomcat level

Configuring an Oracle Datasource in Apache Tomcat