Scenario

These SQL commands provide an optional way to remove pages which are not working or causing instability within confluence.  Before using this approach, please be sure to try removing the page from confluence interface first .

Resolution

1.  Get the content ID of the page. 
   1. View the page.
   2. Click on the ... > Page Information.
   3. The content ID will be listed at the end of the resulting URL string.  For example, https://yoursite.com/pages/viewinfo.action?pageId=12345
2. Now, stop Confluence.
3. Create a full database backup prior to running these queries against your database

4. Run the SQL commands below, in order, since the database contains constraints that prevent foreign keys from being violated.  Replace the <content_id> with the actual content ID you identified earlier:

   • PostgreSQL

   /* Create a set of tables to process the deletion of the page and its related records. */
   DROP TABLE IF EXISTS DELETE_CONTENT_PREP;
   DROP TABLE IF EXISTS DELETE_CONTENT;
   DROP TABLE IF EXISTS DELETE_CONTENT_COMMENTS_PREP_1;
   DROP TABLE IF EXISTS DELETE_CONTENT_COMMENTS_PREP_2;
   DROP TABLE IF EXISTS DELETE_CONTENT_COMMENTS;
   DROP TABLE IF EXISTS DELETE_CONTENT_ALL;
   
   /* Make first pass of the main content */
   CREATE TABLE DELETE_CONTENT_PREP AS (SELECT CONTENTID FROM CONTENT C WHERE CONTENTID = <content_id>);
   
   /* Get the rest of the main content */
   CREATE TABLE DELETE_CONTENT AS 
   (SELECT CONTENTID FROM DELETE_CONTENT_PREP 
   UNION 
   SELECT CONTENTID FROM CONTENT WHERE PAGEID IN (SELECT CONTENTID FROM DELETE_CONTENT_PREP)
   UNION
   SELECT CONTENTID FROM CONTENT WHERE PREVVER IN (SELECT CONTENTID FROM DELETE_CONTENT_PREP));
   
   /* Get the content related to comments and inline comments */
   CREATE TABLE DELETE_CONTENT_COMMENTS_PREP_1 AS 
   (SELECT CONTENTID FROM CONTENT WHERE CONTENTTYPE='COMMENT' AND PAGEID IN (SELECT CONTENTID FROM DELETE_CONTENT_PREP)
   UNION
   SELECT CONTENTID FROM CONTENT WHERE CONTENTTYPE='CUSTOM' AND PAGEID IN (SELECT CONTENTID FROM DELETE_CONTENT_PREP));
   
   /* Get the previous versions of these comments */
   CREATE TABLE DELETE_CONTENT_COMMENTS_PREP_2 AS 
   (SELECT CONTENTID FROM CONTENT WHERE PREVVER IN (SELECT CONTENTID FROM DELETE_CONTENT_COMMENTS_PREP_1)
   UNION
   SELECT CONTENTID FROM CONTENT WHERE PAGEID IN (SELECT CONTENTID FROM DELETE_CONTENT_COMMENTS_PREP_1));
   
   /* Combine both sets of comments */
   CREATE TABLE DELETE_CONTENT_COMMENTS AS (SELECT * FROM DELETE_CONTENT_COMMENTS_PREP_1 UNION SELECT * FROM DELETE_CONTENT_COMMENTS_PREP_2);
   
   /* Combine both sets of content and comments. This resultset will be used to query against tables related to the content table */
   CREATE TABLE DELETE_CONTENT_ALL AS (SELECT * FROM DELETE_CONTENT UNION SELECT * FROM DELETE_CONTENT_COMMENTS);
   
   /* Add a primary key to prevent dupes */
   ALTER TABLE DELETE_CONTENT ADD PRIMARY KEY (CONTENTID);
   ALTER TABLE DELETE_CONTENT_COMMENTS ADD PRIMARY KEY (CONTENTID);
   ALTER TABLE DELETE_CONTENT_ALL ADD PRIMARY KEY (CONTENTID);
   
   /* Delete records from EXTRNLNKS  Note: this table has been dropped in newer versions of Confluence.  Skip, if this is the case. */
   DELETE FROM EXTRNLNKS WHERE CONTENTID IN (SELECT CONTENTID FROM DELETE_CONTENT_ALL);
   
   /* Delete records from TRACKBACK LINKS  Note: this table has been dropped in newer versions of Confluence. Skip, if this is the case.  */
   DELETE FROM TRACKBACKLINKS WHERE CONTENTID IN (SELECT CONTENTID FROM DELETE_CONTENT_ALL);
   
   /* Delete records from CONFANCESTORS */
   DELETE FROM CONFANCESTORS WHERE DESCENDENTID IN (SELECT CONTENTID FROM DELETE_CONTENT_ALL) OR ANCESTORID IN (SELECT CONTENTID FROM DELETE_CONTENT_ALL);
   
   /* Delete records from BODYCONTENT */
   DELETE FROM BODYCONTENT WHERE CONTENTID IN (SELECT CONTENTID FROM DELETE_CONTENT_ALL);
   
   /* Delete records from CONTENT_LABEL */
   DELETE FROM CONTENT_LABEL WHERE CONTENTID IN (SELECT CONTENTID FROM DELETE_CONTENT_ALL);
   
   /* Delete records from OS_PROPERTYENTRY */
   DELETE FROM OS_PROPERTYENTRY WHERE ENTITY_ID IN (SELECT CONTENTID FROM DELETE_CONTENT_ALL);
   
   /* Delete records from LINKS */
   DELETE FROM LINKS WHERE CONTENTID IN (SELECT CONTENTID FROM DELETE_CONTENT_ALL);
   
   /* Delete records from IMAGEDETAILS  */
   DELETE FROM IMAGEDETAILS WHERE ATTACHMENTID IN (SELECT CONTENTID FROM DELETE_CONTENT_ALL);
    
   /* Delete records from ATTACHMENT DATA */
   DELETE FROM ATTACHMENTDATA WHERE ATTACHMENTID IN (SELECT CONTENTID FROM DELETE_CONTENT_ALL);
   
   /* Delete records from CONTENTPROPERTIES */
   DELETE FROM CONTENTPROPERTIES WHERE CONTENTID IN (SELECT CONTENTID FROM DELETE_CONTENT_ALL);
   
   /* Delete records from CONTENT_PERM */
   DELETE FROM CONTENT_PERM WHERE CPS_ID IN (SELECT ID FROM CONTENT_PERM_SET WHERE CONTENT_ID IN (SELECT CONTENTID FROM DELETE_CONTENT_ALL));
   
   /* Delete records from CONTENT_PERM_SET */
   DELETE FROM CONTENT_PERM_SET WHERE CONTENT_ID IN (SELECT CONTENTID FROM DELETE_CONTENT_ALL);
   
   /* Delete records from NOTIFICATIONS (content) */
   DELETE FROM NOTIFICATIONS WHERE CONTENTID IN (SELECT CONTENTID FROM DELETE_CONTENT_ALL);
   
   /* Delete records from LIKES */
   DELETE FROM LIKES WHERE CONTENTID IN (SELECT CONTENTID FROM DELETE_CONTENT_ALL);
   
   /* Delete records from USERCONTENT_RELATION */
   DELETE FROM USERCONTENT_RELATION WHERE TARGETCONTENTID IN (SELECT CONTENTID FROM DELETE_CONTENT_ALL);
   
   /* Delete records from COMMENTS */
   DELETE FROM CONTENT WHERE CONTENTID IN (SELECT CONTENTID FROM DELETE_CONTENT_COMMENTS);
   
   /* Delete main records from CONTENT */
   DELETE FROM CONTENT WHERE CONTENTID IN (SELECT CONTENTID FROM DELETE_CONTENT); 

   In case you encounter the below error, while running the delete query on the content table, please ensure to first delete the child pages of the page that you are currently trying to delete and then proceed with the current page deletion

   DELETE FROM CONTENT WHERE CONTENTID IN (SELECT CONTENTID FROM DELETE_CONTENT)
   Error report -
   ORA-02292: integrity constraint (XXX.FKOXTT893WEUJKYH0IICOXSM37V) violated - child record found

5. Rebuild the indexes from scratch for server or Rebuild the indexes from scratch for DC
6. Restart Confluence for the changes.