ORA-01795 is thrown when a single page have more than 1000 attachments

Steps to replicate

1. Install Confluence 5.8.18 or 5.10.3 with Oracle 12c
2. Login to Confluence
3. Create a new Space
4. Create a new page
5. Generate 1001 dummy files
6. Drag all 1001 files and drop it to the newly created page
7. Save the page
8. Click on one of the files to preview after saving and you will see the following error in the logs :

   Caused by: java.sql.SQLSyntaxErrorException: ORA-01795: maximum number of expressions in a list is 1000
   
   	at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:450)
   	at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:399)
   	at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1059)
   	at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:522)
   	at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:257)
   	at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:587)
   	at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:225)
   	at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:53)
   	at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:774)
   	at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:925)
   	at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1111)
   	at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:4798)
   	at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:4845)
   	at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1501)
   	at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeQuery(NewProxyPreparedStatement.java:379)
   	at net.sf.hibernate.impl.BatcherImpl.getResultSet(BatcherImpl.java:89)
   	at net.sf.hibernate.loader.Loader.getResultSet(Loader.java:880)
   	at net.sf.hibernate.loader.Loader.doQuery(Loader.java:273)
   	at net.sf.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:138)
   	at net.sf.hibernate.loader.Loader.doList(Loader.java:1063)
   	at net.sf.hibernate.loader.Loader.list(Loader.java:1048)
   	at net.sf.hibernate.hql.QueryTranslator.list(QueryTranslator.java:854)
   	at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1570)
   	at net.sf.hibernate.impl.QueryImpl.list(QueryImpl.java:49)
   	at com.atlassian.hibernate.HibernateObjectDao.lambda$findNamedQueryStringParams$5(HibernateObjectDao.java:432)
   	at org.springframework.orm.hibernate.HibernateTemplate.execute(HibernateTemplate.java:370)
   	... 324 more

Current behavior : an Oracle error is thrown if more than 1000 attachments is being attached to a page.
Expected behavior : Oracle error should not be thrown when more than 1000 attachment is being attached.

Possible Workaround

If the issue is occurring due to attachment versions, try the following for attachments with many versions:

1. Download the latest version of an attachment
2. Delete the attachment from the page
3. Go to Space Tools >> Content Tools >> Trash
4. Click on Purge All or purge only the attachment in question
   • The entries will be removed from the database including all older versions
5. Add the attachment back to the same page

Mass Attachment Deletion Workaround

It is also possible to delete older versions of the attachment via the database:

We always recommend taking a database backup before trying any database manipulation queries.
Confluence must be stopped when running these DELETE commands

Locate the page-id for a problem page. This can be found by navigating to the page information ((***) > Page Information)) for the page. It will give you a URL like:

http://localhost:8090/pages/viewinfo.action?pageId=2654209

The number at the end is the page id that will be substituted in for <page-id> in the queries below:

DELETE FROM contentproperties WHERE contentid IN (SELECT contentid FROM content WHERE contenttype = 'ATTACHMENT' AND PAGEID = <page-id> and prevver IS NOT NULL);
DELETE FROM content WHERE contenttype = 'ATTACHMENT' AND PAGEID = <page-id> and prevver IS NOT NULL;