After a failed space import, it's not possible to re-import because of leftover space data

お困りですか?

アトラシアン コミュニティをご利用ください。

コミュニティに質問

問題

After a space import times out fails, or the space information becomes corrupted in the database, the space and content cannot be accessed or removed from the Space Admin page (see CONF-31528 - Getting issue details... STATUS ).


ソリューション 1

The easiest way to delete a space which its import has failed is to do it through the UI. We can remove it, even if it fails and doesn't show in the space directory, by using this url:

  • <confluence base url>/spaces/removespace.action?key=<space key>
    • Make sure to change the tags with the correct information.
    • With this url, the removespace page for the respective space will be shown, by clicking OK, you will initiate the space removal.

This is only possible because, during the import, it starts from the "space" object creation. So, if you have a "space" object, you will be able to delete the space and everything related to it. As usual, make sure to create a database backup before doing so.

ソリューション 2

The imported Space and related content can be removed manually from the Confluence database. Before you proceed with the database updates, please note that:

  • the queries provided below are for informational purposes, as this is an unsupported process;
  • make sure to check Unable to Delete Space first;
  • if there might be database constraints in place that restrict Space deletion, make sure to drop the constraint and add it back after the process is completed.

Always backup your data before performing any modifications to the database.

Delete Space database queries

Before running these scripts, please note the space key of the space that needs to be deleted.

(info) While these queries were tested against MySQL 5.7, Postgres 9.5 and Microsoft SQL Server (2016), please bear in mind that these scripts may need minor adjustments before they work on other platforms.

Also, make sure to shutdown Confluence before running these queries.


MySQL Queries
/* Create a set of tables to process the deletion of space records */
DROP TABLE IF EXISTS DELETE_SPACE;
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;
DROP TABLE IF EXISTS DELETE_TEMPLATE_CURRENT;
DROP TABLE IF EXISTS DELETE_TEMPLATE_PREVVER;
DROP TABLE IF EXISTS DELETE_TEMPLATE_ALL;

/* Create temp table to hold space id and key */
CREATE TABLE DELETE_SPACE AS (SELECT SPACEID, SPACEKEY FROM SPACES WHERE SPACEKEY = 'YOUR_SPACEKEY_HERE');

/* Remove reference to the deleted space */
UPDATE SPACES SET HOMEPAGE=NULL, SPACEDESCID=NULL WHERE SPACEID = (SELECT SPACEID FROM DELETE_SPACE);

/* Make first pass of the main content */
CREATE TABLE DELETE_CONTENT_PREP AS (SELECT CONTENTID FROM CONTENT C JOIN SPACES AS S ON S.SPACEID = C.SPACEID WHERE S.SPACEKEY = (SELECT SPACEKEY FROM DELETE_SPACE));

/* 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);

/* Before performing the deletion statements below, check to see if there are any records associated with spaces other than the one being deleted, but which still have a reference to a record in the space being deleted.  This has been documented to occur wehn importing spaces from the cloud in CONFCLOUD-64563.  If records are returned from any of these queries, STOP further processing and share the results of these queries with support as these will need to be manually dealt with by either moving or deleting them via the UI if possible */
SELECT * FROM CONTENT WHERE PARENTID IN (SELECT * FROM DELETE_CONTENT) AND SPACEID != (SELECT SPACEID FROM DELETE_SPACE);

SELECT * FROM CONTENT WHERE PAGEID IN (SELECT * FROM DELETE_CONTENT) AND SPACEID != (SELECT SPACEID FROM DELETE_SPACE);

SELECT * FROM CONTENT WHERE PREVVER IN (SELECT * FROM DELETE_CONTENT) AND SPACEID != (SELECT SPACEID FROM DELETE_SPACE);

/* 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 EXTRNLNKS */
DELETE FROM EXTRNLNKS 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 TRACKBACK LINKS */
DELETE FROM TRACKBACKLINKS WHERE CONTENTID 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 NOTIFICATIONS (space) */
DELETE FROM NOTIFICATIONS WHERE SPACEID = (SELECT SPACEID FROM DELETE_SPACE);
 
/* Get currently active templates from PAGETEMPLATES */
CREATE TABLE DELETE_TEMPLATE_CURRENT AS (SELECT TEMPLATEID FROM PAGETEMPLATES WHERE SPACEID=(SELECT SPACEID FROM DELETE_SPACE));

/* Get previous versions of these templates, if any, from PAGETEMPLAES */
CREATE TABLE DELETE_TEMPLATE_PREVVER AS (SELECT TEMPLATEID FROM PAGETEMPLATES WHERE PREVVER IN (SELECT TEMPLATEID FROM DELETE_TEMPLATE_CURRENT));

/* Combine these two sets of records */
CREATE TABLE DELETE_TEMPLATE_ALL AS (SELECT * FROM DELETE_TEMPLATE_CURRENT) UNION (SELECT * FROM DELETE_TEMPLATE_PREVVER);

/* Delete template related records from CONTENT_LABEL */
DELETE FROM CONTENT_LABEL WHERE PAGETEMPLATEID IN (SELECT TEMPLATEID FROM DELETE_TEMPLATE_ALL);

/* Delete previous versions of PAGETEMPLATES */
DELETE FROM PAGETEMPLATES WHERE TEMPLATEID IN (SELECT TEMPLATEID FROM DELETE_TEMPLATE_PREVVER);

/* Delete records from PAGETEMPLATES */
DELETE FROM PAGETEMPLATES WHERE TEMPLATEID IN (SELECT TEMPLATEID FROM DELETE_TEMPLATE_CURRENT);

/* Delete space's space permissions from SPACEPERMISSIONS */
DELETE FROM SPACEPERMISSIONS WHERE SPACEID = (SELECT SPACEID FROM DELETE_SPACE);
 
/* 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) AND PREVVER IS NOT NULL;

DELETE FROM CONTENT WHERE CONTENTID IN (SELECT CONTENTID FROM DELETE_CONTENT_COMMENTS) AND PARENTCOMMENTID IS NOT NULL ORDER BY CONTENTID DESC;

DELETE FROM CONTENT WHERE CONTENTID IN (SELECT CONTENTID FROM DELETE_CONTENT_COMMENTS) ORDER BY CONTENTID DESC;

/* Delete main records from CONTENT */
DELETE FROM CONTENT WHERE CONTENTID IN (SELECT CONTENTID FROM DELETE_CONTENT) AND PREVVER IS NOT NULL;

DELETE FROM CONTENT WHERE CONTENTID IN (SELECT CONTENTID FROM DELETE_CONTENT) AND PAGEID IS NOT NULL ORDER BY PAGEID DESC;

UPDATE CONTENT SET PARENTID = NULL WHERE CONTENTID IN (SELECT CONTENTID FROM DELETE_CONTENT ORDER BY CONTENTID DESC) AND PARENTID IS NOT NULL;

DELETE FROM CONTENT WHERE CONTENTID IN (SELECT CONTENTID FROM DELETE_CONTENT) ORDER BY CONTENTID DESC;

/* Delete records related to space from DECORATOR */
DELETE FROM DECORATOR WHERE SPACEKEY = (SELECT SPACEKEY FROM DELETE_SPACE);

/* Delete the space from SPACES */
DELETE FROM SPACES WHERE SPACEID = (SELECT SPACEID FROM DELETE_SPACE);
 
/* Delete record related to space from BANDANA */
DELETE FROM BANDANA WHERE BANDANACONTEXT = (SELECT SPACEKEY FROM DELETE_SPACE); 
Microsoft SQL Server Queries
/* DISCLAIMER: the commands below were created and tested with SQL Server 2016.  If you're using older versions of SQL Server, these may need some adjustment */

/* Create a set of tables to process the deletion of space records */
DROP TABLE IF EXISTS DELETE_SPACE;
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;
DROP TABLE IF EXISTS DELETE_TEMPLATE_CURRENT;
DROP TABLE IF EXISTS DELETE_TEMPLATE_PREVVER;
DROP TABLE IF EXISTS DELETE_TEMPLATE_ALL;

/* Create temp table to hold space id and key */
SELECT SPACEID, SPACEKEY INTO DELETE_SPACE FROM SPACES WHERE SPACEKEY = 'YOUR_SPACEKEY_HERE';

/* Remove reference to the deleted space */
UPDATE SPACES SET HOMEPAGE=NULL, SPACEDESCID=NULL WHERE SPACEID = (SELECT SPACEID FROM DELETE_SPACE);

/* Make first pass of the main content */
SELECT CONTENTID INTO DELETE_CONTENT_PREP FROM CONTENT C JOIN SPACES S ON S.SPACEID = C.SPACEID WHERE S.SPACEKEY = (SELECT SPACEKEY FROM DELETE_SPACE);

/* Get the rest of the main content */
SELECT CONTENTID INTO DELETE_CONTENT 
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 */
SELECT u.CONTENTID INTO DELETE_CONTENT_COMMENTS_PREP_1 
FROM 
(
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)
) u;

/* Get the previous versions of these comments */
SELECT u.CONTENTID INTO DELETE_CONTENT_COMMENTS_PREP_2 
FROM 
(
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)
) u;

/* Combine both sets of comments */
SELECT u.CONTENTID INTO DELETE_CONTENT_COMMENTS FROM (SELECT CONTENTID FROM DELETE_CONTENT_COMMENTS_PREP_1 UNION SELECT CONTENTID FROM DELETE_CONTENT_COMMENTS_PREP_2) u;

/* Combine both sets of content and comments. This resultset will be used to query against tables related to the content table */
SELECT u.CONTENTID INTO DELETE_CONTENT_ALL FROM (SELECT CONTENTID FROM DELETE_CONTENT UNION SELECT CONTENTID FROM DELETE_CONTENT_COMMENTS) u;

/* 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);

/* Before performing the deletion statements below, check to see if there are any records associated with spaces other than the one being deleted, but which still have a reference to a record in the space being deleted.  This has been documented to occur wehn importing spaces from the cloud in CONFCLOUD-64563.  If records are returned from any of these queries, STOP further processing and share the results of these queries with support as these will need to be manually dealt with by either moving or deleting them via the UI if possible */
SELECT * FROM CONTENT WHERE PARENTID IN (SELECT * FROM DELETE_CONTENT) AND SPACEID != (SELECT SPACEID FROM DELETE_SPACE);
SELECT * FROM CONTENT WHERE PAGEID IN (SELECT * FROM DELETE_CONTENT) AND SPACEID != (SELECT SPACEID FROM DELETE_SPACE);
SELECT * FROM CONTENT WHERE PREVVER IN (SELECT * FROM DELETE_CONTENT) AND SPACEID != (SELECT SPACEID FROM DELETE_SPACE);

/* 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 EXTRNLNKS */
DELETE FROM EXTRNLNKS 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 TRACKBACK LINKS */
DELETE FROM TRACKBACKLINKS WHERE CONTENTID 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 NOTIFICATIONS (space) */
DELETE FROM NOTIFICATIONS WHERE SPACEID = (SELECT SPACEID FROM DELETE_SPACE);
 
/* Get currently active templates from PAGETEMPLATES */
SELECT TEMPLATEID INTO DELETE_TEMPLATE_CURRENT FROM PAGETEMPLATES WHERE SPACEID = (SELECT SPACEID FROM DELETE_SPACE);

/* Get previous versions of these templates, if any, from PAGETEMPLAES */
SELECT TEMPLATEID INTO DELETE_TEMPLATE_PREVVER FROM PAGETEMPLATES WHERE PREVVER IN (SELECT TEMPLATEID FROM DELETE_TEMPLATE_CURRENT);

/* Combine these two sets of records */
SELECT u.TEMPLATEID INTO DELETE_TEMPLATE_ALL FROM (SELECT TEMPLATEID FROM DELETE_TEMPLATE_CURRENT UNION SELECT TEMPLATEID FROM DELETE_TEMPLATE_PREVVER) u;

/* Delete template related records from CONTENT_LABEL */
DELETE FROM CONTENT_LABEL WHERE PAGETEMPLATEID IN (SELECT TEMPLATEID FROM DELETE_TEMPLATE_ALL);

/* Delete previous versions of PAGETEMPLATES */
DELETE FROM PAGETEMPLATES WHERE TEMPLATEID IN (SELECT TEMPLATEID FROM DELETE_TEMPLATE_PREVVER);

/* Delete records from PAGETEMPLATES */
DELETE FROM PAGETEMPLATES WHERE TEMPLATEID IN (SELECT TEMPLATEID FROM DELETE_TEMPLATE_CURRENT);
 
/* Delete space's space permissions from SPACEPERMISSIONS */
DELETE FROM SPACEPERMISSIONS WHERE SPACEID = (SELECT SPACEID FROM DELETE_SPACE);
 
/* 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);

/* Delete records related to space from DECORATOR */
DELETE FROM DECORATOR WHERE SPACEKEY = (SELECT SPACEKEY FROM DELETE_SPACE);

/* Delete the space from SPACES */
DELETE FROM SPACES WHERE SPACEID = (SELECT SPACEID FROM DELETE_SPACE);
 
/* Delete record related to space from BANDANA */
DELETE FROM BANDANA WHERE BANDANACONTEXT = (SELECT SPACEKEY FROM DELETE_SPACE); 
Postgres Queries
/* Create a set of tables to process the deletion of space records */
DROP TABLE IF EXISTS DELETE_SPACE;
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;
DROP TABLE IF EXISTS DELETE_TEMPLATE_CURRENT;
DROP TABLE IF EXISTS DELETE_TEMPLATE_PREVVER;
DROP TABLE IF EXISTS DELETE_TEMPLATE_ALL;

/* Create temp table to hold space id and key */
CREATE TABLE DELETE_SPACE AS (SELECT SPACEID, SPACEKEY FROM SPACES WHERE SPACEKEY = 'YOUR_SPACEKEY_HERE');

/* Remove reference to the deleted space */
UPDATE SPACES SET HOMEPAGE=NULL, SPACEDESCID=NULL WHERE SPACEID = (SELECT SPACEID FROM DELETE_SPACE);

/* Make first pass of the main content */
CREATE TABLE DELETE_CONTENT_PREP AS (SELECT CONTENTID FROM CONTENT C JOIN SPACES AS S ON S.SPACEID = C.SPACEID WHERE S.SPACEKEY = (SELECT SPACEKEY FROM DELETE_SPACE));

/* 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);

/* Before performing the deletion statements below, check to see if there are any records associated with spaces other than the one being deleted, but which still have a reference to a record in the space being deleted.  This has been documented to occur wehn importing spaces from the cloud in CONFCLOUD-64563.  If records are returned from any of these queries, STOP further processing and share the results of these queries with support as these will need to be manually dealt with by either moving or deleting them via the UI if possible */
SELECT * FROM CONTENT WHERE PARENTID IN (SELECT * FROM DELETE_CONTENT) AND SPACEID != (SELECT SPACEID FROM DELETE_SPACE);
SELECT * FROM CONTENT WHERE PAGEID IN (SELECT * FROM DELETE_CONTENT) AND SPACEID!= (SELECT SPACEID FROM DELETE_SPACE);
SELECT * FROM CONTENT WHERE PREVVER IN (SELECT * FROM DELETE_CONTENT) AND SPACEID != (SELECT SPACEID FROM DELETE_SPACE);

/* 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 EXTRNLNKS */
DELETE FROM EXTRNLNKS 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 TRACKBACK LINKS */
DELETE FROM TRACKBACKLINKS WHERE CONTENTID 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 NOTIFICATIONS (space) */
DELETE FROM NOTIFICATIONS WHERE SPACEID = (SELECT SPACEID FROM DELETE_SPACE);

/* Get currently active templates from PAGETEMPLATES */
CREATE TABLE DELETE_TEMPLATE_CURRENT AS (SELECT TEMPLATEID FROM PAGETEMPLATES WHERE SPACEID=(SELECT SPACEID FROM DELETE_SPACE));

/* Get previous versions of these templates, if any, from PAGETEMPLAES */
CREATE TABLE DELETE_TEMPLATE_PREVVER AS (SELECT TEMPLATEID FROM PAGETEMPLATES WHERE PREVVER IN (SELECT TEMPLATEID FROM DELETE_TEMPLATE_CURRENT));

/* Combine these two sets of records */
CREATE TABLE DELETE_TEMPLATE_ALL AS (SELECT * FROM DELETE_TEMPLATE_CURRENT UNION SELECT * FROM DELETE_TEMPLATE_PREVVER);

/* Delete template related records from CONTENT_LABEL */
DELETE FROM CONTENT_LABEL WHERE PAGETEMPLATEID IN (SELECT TEMPLATEID FROM DELETE_TEMPLATE_ALL);

/* Delete previous versions of PAGETEMPLATES */
DELETE FROM PAGETEMPLATES WHERE TEMPLATEID IN (SELECT TEMPLATEID FROM DELETE_TEMPLATE_PREVVER);

/* Delete records from PAGETEMPLATES */
DELETE FROM PAGETEMPLATES WHERE TEMPLATEID IN (SELECT TEMPLATEID FROM DELETE_TEMPLATE_CURRENT);

/* Delete space's space permissions from SPACEPERMISSIONS */
DELETE FROM SPACEPERMISSIONS WHERE SPACEID = (SELECT SPACEID FROM DELETE_SPACE);

/* 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);

/* Delete records related to space from DECORATOR */
DELETE FROM DECORATOR WHERE SPACEKEY = (SELECT SPACEKEY FROM DELETE_SPACE);

/* Delete the space from SPACES */
DELETE FROM SPACES WHERE SPACEID = (SELECT SPACEID FROM DELETE_SPACE);

/* Delete record related to space from BANDANA */
DELETE FROM BANDANA WHERE BANDANACONTEXT = (SELECT SPACEKEY FROM DELETE_SPACE);
Oracle Queries
-- Run this SQL FIRST to generate the DROP TABLE SQL statements
SELECT owner, 'drop table ' || table_name || ';'
FROM all_tables where
table_name in ('DELETE_SPACE',
               'DELETE_CONTENT_PREP',
               'DELETE_CONTENT',
               'DELETE_CONTENT_COMMENTS_PREP_1',
               'DELETE_CONTENT_COMMENTS_PREP_2',
               'DELETE_CONTENT_COMMENTS',
               'DELETE_CONTENT_ALL',
               'DELETE_TEMPLATE_CURRENT',
               'DELETE_TEMPLATE_PREVVER',
               'DELETE_TEMPLATE_ALL'
               )
and owner = 'YOUR_ORACLE_DB_SCHEMA';

-- *************************************************************************************************
-- !! IMPORTANT - REPLACE **YOUR_ORACLE_DB_SCHEMA** above with your Oracle Confluence DB Schema Name
--
-- If any rows come back from the above, run the generated SQL to drop the temporary tables
-- If no rows came back, continue on :)
--
-- *************************************************************************************************

/* Create temp table to hold space id and key */
CREATE TABLE DELETE_SPACE AS (SELECT SPACEID, SPACEKEY FROM SPACES WHERE SPACEKEY = 'YOUR_SPACEKEY_HERE');

/* Remove reference to the deleted space */
UPDATE SPACES SET HOMEPAGE=NULL, SPACEDESCID=NULL WHERE SPACEID = (SELECT SPACEID FROM DELETE_SPACE);

/* Make first pass of the main content */
CREATE TABLE DELETE_CONTENT_PREP AS (SELECT CONTENTID FROM CONTENT C JOIN SPACES S ON S.SPACEID = C.SPACEID WHERE S.SPACEKEY = (SELECT SPACEKEY FROM DELETE_SPACE));

/* 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);

/* Before performing the deletion statements below, check to see if there are any records associated with spaces other than the one being deleted, but which still have a reference to a record in the space being deleted.  This has been documented to occur wehn importing spaces from the cloud in CONFCLOUD-64563.  If records are returned from any of these queries, STOP further processing and share the results of these queries with support as these will need to be manually dealt with by either moving or deleting them via the UI if possible */
SELECT * FROM CONTENT WHERE PARENTID IN (SELECT * FROM DELETE_CONTENT) AND SPACEID != (SELECT SPACEID FROM DELETE_SPACE);
SELECT * FROM CONTENT WHERE PAGEID IN (SELECT * FROM DELETE_CONTENT) AND SPACEID!= (SELECT SPACEID FROM DELETE_SPACE);
SELECT * FROM CONTENT WHERE PREVVER IN (SELECT * FROM DELETE_CONTENT) AND SPACEID != (SELECT SPACEID FROM DELETE_SPACE);

/* 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 EXTRNLNKS */
DELETE FROM EXTRNLNKS 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 TRACKBACK LINKS */
DELETE FROM TRACKBACKLINKS WHERE CONTENTID 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 NOTIFICATIONS (space) */
DELETE FROM NOTIFICATIONS WHERE SPACEID = (SELECT SPACEID FROM DELETE_SPACE);

/* Get currently active templates from PAGETEMPLATES */
CREATE TABLE DELETE_TEMPLATE_CURRENT AS (SELECT TEMPLATEID FROM PAGETEMPLATES WHERE SPACEID=(SELECT SPACEID FROM DELETE_SPACE));

/* Get previous versions of these templates, if any, from PAGETEMPLAES */
CREATE TABLE DELETE_TEMPLATE_PREVVER AS (SELECT TEMPLATEID FROM PAGETEMPLATES WHERE PREVVER IN (SELECT TEMPLATEID FROM DELETE_TEMPLATE_CURRENT));

/* Combine these two sets of records */
CREATE TABLE DELETE_TEMPLATE_ALL AS (SELECT * FROM DELETE_TEMPLATE_CURRENT UNION SELECT * FROM DELETE_TEMPLATE_PREVVER);

/* Delete template related records from CONTENT_LABEL */
DELETE FROM CONTENT_LABEL WHERE PAGETEMPLATEID IN (SELECT TEMPLATEID FROM DELETE_TEMPLATE_ALL);

/* Delete previous versions of PAGETEMPLATES */
DELETE FROM PAGETEMPLATES WHERE TEMPLATEID IN (SELECT TEMPLATEID FROM DELETE_TEMPLATE_PREVVER);

/* Delete records from PAGETEMPLATES */
DELETE FROM PAGETEMPLATES WHERE TEMPLATEID IN (SELECT TEMPLATEID FROM DELETE_TEMPLATE_CURRENT);

/* Delete space's space permissions from SPACEPERMISSIONS */
DELETE FROM SPACEPERMISSIONS WHERE SPACEID = (SELECT SPACEID FROM DELETE_SPACE);

/* 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);

/* Delete records related to space from DECORATOR */
DELETE FROM DECORATOR WHERE SPACEKEY = (SELECT SPACEKEY FROM DELETE_SPACE);

/* Delete the space from SPACES */
DELETE FROM SPACES WHERE SPACEID = (SELECT SPACEID FROM DELETE_SPACE);

/* Delete record related to space from BANDANA */
DELETE FROM BANDANA WHERE BANDANACONTEXT = (SELECT SPACEKEY FROM DELETE_SPACE); 
H2 Queries
/* Create a set of tables to process the deletion of space records */
DROP TABLE IF EXISTS DELETE_SPACE;
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;
DROP TABLE IF EXISTS DELETE_TEMPLATE_CURRENT;
DROP TABLE IF EXISTS DELETE_TEMPLATE_PREVVER;
DROP TABLE IF EXISTS DELETE_TEMPLATE_ALL;

/* Create temp table to hold space id and key */
CREATE TABLE DELETE_SPACE AS (SELECT SPACEID, SPACEKEY FROM SPACES WHERE SPACEKEY = 'YOUR_SPACEKEY_HERE');

/* Remove reference to the deleted space */
UPDATE SPACES SET HOMEPAGE=NULL, SPACEDESCID=NULL WHERE SPACEID = (SELECT SPACEID FROM DELETE_SPACE);

/* Make first pass of the main content */
CREATE TABLE DELETE_CONTENT_PREP AS (SELECT CONTENTID FROM CONTENT C JOIN SPACES AS S ON S.SPACEID = C.SPACEID WHERE S.SPACEKEY = (SELECT SPACEKEY FROM DELETE_SPACE));

/* 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 ALTER COLUMN CONTENTID SET NOT NULL;
ALTER TABLE DELETE_CONTENT ADD PRIMARY KEY (CONTENTID);
ALTER TABLE DELETE_CONTENT_COMMENTS ALTER COLUMN CONTENTID SET NOT NULL;
ALTER TABLE DELETE_CONTENT_COMMENTS ADD PRIMARY KEY (CONTENTID);
ALTER TABLE DELETE_CONTENT_ALL ALTER COLUMN CONTENTID SET NOT NULL;
ALTER TABLE DELETE_CONTENT_ALL ADD PRIMARY KEY (CONTENTID);

/* Before performing the deletion statements below, check to see if there are any records associated with spaces other than the one being deleted, but which still have a reference to a record in the space being deleted.  This has been documented to occur wehn importing spaces from the cloud in CONFCLOUD-64563.  If records are returned from any of these queries, STOP further processing and share the results of these queries with support as these will need to be manually dealt with by either moving or deleting them via the UI if possible */
SELECT * FROM CONTENT WHERE PARENTID IN (SELECT * FROM DELETE_CONTENT) AND SPACEID != (SELECT SPACEID FROM DELETE_SPACE);
SELECT * FROM CONTENT WHERE PAGEID IN (SELECT * FROM DELETE_CONTENT) AND SPACEID!= (SELECT SPACEID FROM DELETE_SPACE);
SELECT * FROM CONTENT WHERE PREVVER IN (SELECT * FROM DELETE_CONTENT) AND SPACEID != (SELECT SPACEID FROM DELETE_SPACE);

/* 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 EXTRNLNKS */
DELETE FROM EXTRNLNKS 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 TRACKBACK LINKS */
DELETE FROM TRACKBACKLINKS WHERE CONTENTID 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 NOTIFICATIONS (space) */
DELETE FROM NOTIFICATIONS WHERE SPACEID = (SELECT SPACEID FROM DELETE_SPACE);

/* Get currently active templates from PAGETEMPLATES */
CREATE TABLE DELETE_TEMPLATE_CURRENT AS (SELECT TEMPLATEID FROM PAGETEMPLATES WHERE SPACEID=(SELECT SPACEID FROM DELETE_SPACE));

/* Get previous versions of these templates, if any, from PAGETEMPLAES */
CREATE TABLE DELETE_TEMPLATE_PREVVER AS (SELECT TEMPLATEID FROM PAGETEMPLATES WHERE PREVVER IN (SELECT TEMPLATEID FROM DELETE_TEMPLATE_CURRENT));

/* Combine these two sets of records */
CREATE TABLE DELETE_TEMPLATE_ALL AS (SELECT * FROM DELETE_TEMPLATE_CURRENT UNION SELECT * FROM DELETE_TEMPLATE_PREVVER);

/* Delete template related records from CONTENT_LABEL */
DELETE FROM CONTENT_LABEL WHERE PAGETEMPLATEID IN (SELECT TEMPLATEID FROM DELETE_TEMPLATE_ALL);

/* Delete previous versions of PAGETEMPLATES */
DELETE FROM PAGETEMPLATES WHERE TEMPLATEID IN (SELECT TEMPLATEID FROM DELETE_TEMPLATE_PREVVER);

/* Delete records from PAGETEMPLATES */
DELETE FROM PAGETEMPLATES WHERE TEMPLATEID IN (SELECT TEMPLATEID FROM DELETE_TEMPLATE_CURRENT);

/* Delete space's space permissions from SPACEPERMISSIONS */
DELETE FROM SPACEPERMISSIONS WHERE SPACEID = (SELECT SPACEID FROM DELETE_SPACE);

/* 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);

/* Delete records related to space from DECORATOR */
DELETE FROM DECORATOR WHERE SPACEKEY = (SELECT SPACEKEY FROM DELETE_SPACE);

/* Delete the space from SPACES */
DELETE FROM SPACES WHERE SPACEID = (SELECT SPACEID FROM DELETE_SPACE);

/* Delete record related to space from BANDANA */
DELETE FROM BANDANA WHERE BANDANACONTEXT = (SELECT SPACEKEY FROM DELETE_SPACE); 
Confluence Cloud Queries

Note: Perform a Content Indexing prior to running the commands below.

Copy the script below, then do a find and replace for:

  • YOUR_SPACEKEY_HERE = <your space key>


/* REMOVE REFERENCES IN SPACES TO CONTENT */
UPDATE SPACES SET homepage=NULL, spacedescid=NULL WHERE spaceid = (select spaceid from SPACES where spacekey = 'YOUR_SPACEKEY_HERE');
 
/* CREATE A DELETE SET */
DROP TABLE IF EXISTS content_delete;
CREATE TABLE content_delete AS (SELECT contentid FROM content c JOIN SPACES s ON s.spaceid = c.spaceid WHERE s.spacekey = 'YOUR_SPACEKEY_HERE');
 
/* INSERT INTO THE DELETE SET, ALL CHILD PAGES */
INSERT INTO content_delete (SELECT contentid FROM content WHERE prevver IN(SELECT contentid FROM content c JOIN SPACES s ON s.spaceid = c.spaceid WHERE s.spacekey='YOUR_SPACEKEY_HERE') and contentid not in (select contentid from content_delete));
 
/* INSERT INTO THE DELETE SET, ALL COMMENTS (a few times to make sure we add them all)*/
INSERT INTO content_delete (SELECT contentid FROM content WHERE pageid IN (SELECT * FROM content_delete) and contentid not in (select contentid from content_delete));
INSERT INTO content_delete (SELECT contentid FROM content WHERE pageid IN (SELECT * FROM content_delete) and contentid not in (select contentid from content_delete));
INSERT INTO content_delete (SELECT contentid FROM content WHERE pageid IN (SELECT * FROM content_delete) and contentid not in (select contentid from content_delete));
INSERT INTO content_delete (SELECT contentid FROM content WHERE pageid IN (SELECT * FROM content_delete) and contentid not in (select contentid from content_delete));
INSERT INTO content_delete (SELECT contentid FROM content WHERE pageid IN (SELECT * FROM content_delete) and contentid not in (select contentid from content_delete));
INSERT INTO content_delete (SELECT contentid FROM content WHERE pageid IN (SELECT * FROM content_delete) and contentid not in (select contentid from content_delete));
INSERT INTO content_delete (SELECT contentid FROM content WHERE pageid IN (SELECT * FROM content_delete) and contentid not in (select contentid from content_delete));
 
/* INSERT INTO THE DELETE SET ALL VERSIONS OF ALL COMMENTS */
INSERT INTO content_delete (SELECT contentid FROM content WHERE contenttype ='COMMENT' AND prevver IN (SELECT * FROM content_delete) and contentid not in (select contentid from content_delete));
 
/* DELETE CONFANCESTORS */
DELETE FROM confancestors WHERE descendentid IN (SELECT contentid FROM content_delete) OR ancestorid IN (SELECT contentid FROM content_delete);
 
/* DELETE BODYCONTENT */
DELETE FROM bodycontent WHERE contentid IN (SELECT contentid FROM content_delete);
 
/* DELETE CONTENT_LABEL */
DELETE FROM content_label WHERE contentid IN (SELECT contentid FROM content_delete);
 
/* DELETE OS_PROPERTYENTRY */
DELETE FROM os_propertyentry WHERE entity_id IN (SELECT contentid FROM content_delete);
 
/* DELETE LINKS */
DELETE FROM links WHERE contentid IN (SELECT contentid FROM content_delete);
 
/* DELETE EXTRNLNKS */
DELETE FROM extrnlnks WHERE contentid IN (SELECT contentid FROM content_delete);
 
/* DELETE IMAGEDETAILS  */
DELETE FROM imagedetails WHERE attachmentid IN (SELECT c.contentid FROM content c WHERE c.pageid IN (SELECT contentid FROM content_delete));
 
/* DELETE ATTACHMENT DATA */
DELETE FROM attachmentdata WHERE attachmentid IN (SELECT c.contentid FROM content c WHERE c.pageid IN (SELECT contentid FROM content_delete));

/* DELETE CONTENTPROPERTIES */
DELETE FROM contentproperties WHERE contentid IN (SELECT contentid FROM content_delete);
/* DELETE ATTACHMENTS */
DELETE FROM content WHERE contenttype = 'ATTACHMENT' AND pageid IN (SELECT contentid FROM content_delete);

/* DELETE CONTENT_PERM */
DELETE FROM content_perm WHERE cps_id IN (SELECT id FROM content_perm_SET WHERE content_id IN (SELECT contentid FROM content_delete));
 
/* DELETE CONTENT_PERM_SET */
DELETE FROM content_perm_SET WHERE content_id IN (SELECT contentid FROM content_delete);
 
/* DELETE TRACKBACK LINKS */
DELETE FROM trackbacklinks WHERE contentid IN (SELECT contentid FROM content_delete);
 
/* DELETE NOTIFICATIONS (CONTENT) */
DELETE FROM notifications WHERE contentid IN (SELECT contentid FROM content_delete);
 
/* DELETE NOTIFICATIONS (SPACE) */
DELETE FROM notifications WHERE spaceid = (select spaceid from SPACES where spacekey = 'YOUR_SPACEKEY_HERE');
 
/* DELETE PREVIOUS VERSIONS OF PAGE TEMPLATES */
CREATE TABLE pagetemp_delete AS (SELECT templateid FROM pagetemplates WHERE spaceid=(select spaceid from SPACES where spacekey = 'YOUR_SPACEKEY_HERE'));
DELETE FROM pagetemplates WHERE prevver IN (SELECT templateid from pagetemp_delete);
 
/* DELETE PAGETEMPLATES */
DELETE FROM pagetemplates WHERE spaceid=(select spaceid from SPACES where spacekey = 'YOUR_SPACEKEY_HERE');
 
/*DELETE SPACEPERMISSIONS */
DELETE FROM spacepermissions WHERE spaceid=(select spaceid from SPACES where spacekey = 'YOUR_SPACEKEY_HERE');
 
/* DELETE LIKES */
DELETE FROM likes WHERE contentid IN (SELECT contentid FROM content_delete);
 
/* DELETE USER CONTENT RELATION */
DELETE from usercontent_relation where targetcontentid in (SELECT contentid FROM content_delete);

/* DELETE CONTENT */
DELETE FROM content WHERE contentid IN (SELECT contentid FROM content_delete);
 
/* DELETE ANY DECORATORS FOR THE SPACE */
DELETE FROM decorator where SPACEKEY='YOUR_SPACEKEY_HERE';

/* DELETE SPACE */
DELETE FROM SPACES WHERE spaceid=(select spaceid from SPACES where spacekey = 'YOUR_SPACEKEY_HERE');
 
/* DELETE REFERENCE FROM BANDANA */
DELETE FROM bandana WHERE bandanacontext='YOUR_SPACEKEY_HERE';

A listing of all the deleted content is located in the content_delete table that has been created. You can drop these table when you're done with them by executing:

DROP TABLE content_delete;
DROP TABLE pagetemp_delete;

Finally, you'll need to rebuild your index from scratch and rebuild the ancestor table.

関連情報記載ページ:

How to drop and recreate the database constraints on PostgreSQL.

最終更新日 2019 年 8 月 19 日

この内容はお役に立ちましたか?

はい
いいえ
この記事についてのフィードバックを送信する
Powered by Confluence and Scroll Viewport.