スペース インポートの失敗後、スペース データが残っているために再インポートできない

お困りですか?

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

コミュニティに質問

問題

スペース インポートがタイム アウトによって失敗したり、データベース内のスペース情報が破損したりしたあとに、スペースおよびコンテンツにアクセスしたり、それらをスペース管理ページから削除したりすることができない ( CONF-31528 - Getting issue details... STATUS を参照)。


ソリューション 1

インポートに失敗したスペースを削除するもっとも簡単な方法は、UI から操作することです。インポートに失敗してスペースがスペース ディレクトリに表示されない場合も、次の URL で削除できます。

  • <Confluence のベース URL>/spaces/removespace.action?key=<スペース キー>
    • タグを適切な情報で置き換えます。
    • この URL により、対象のスペースの removespace ページが表示されます。[OK] をクリックすることでスペースの削除を開始できます。

これは、インポートが "space" オブジェクトの作成から開始されるために使用できます。このため、"space" オブジェクトがある場合、スペースおよびすべての関連情報を削除できます。ほかの手順と同様、作業の実行前にデータベースのバックアップを作成しておくようにします。

ソリューション 2

インポートされたスペースと関連コンテンツを、Confluence データベースから手動で削除できます。データベースの更新を行う前に、次の点をご確認ください。

  • これはサポート対象外の操作であるため、以降のクエリは参考情報として提供されます。
  • 最初に「スペースを削除できない」をご確認ください。
  • データベース制約によってスペースが削除できない場合、制約をドロップして作業の完了後に戻すようにします。

データベースを変更する前には必ずデータをバックアップするようにします。

スペースを削除するデータベース クエリ

次のスクリプトを実行する前に、削除が必要なスペースのスペース キーを確認しておきます。

(info) これらのクエリは MySQL 5.7、Postgres 9.5 および Microsoft SQL Server (2016) でテストされていますが、スクリプトをご利用のプラットフォームで使用するには少量の調整が必要な可能性がある点にご注意ください。

また、これらのクエリを実行する前に Confluence をシャットダウンするようにします。


MySQL のクエリ
/* 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 のクエリ
/* 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 のクエリ
/* 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 のクエリ
-- 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 のクエリ
/* 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 のクエリ

注意: コンテンツの再インデックスを行ってから以降のコマンドを実行します。

以降のスクリプトをコピーそ、次の置換を行います。

  • YOUR_SPACEKEY_HERE = <スペース キー>


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

作成された content_delete テーブルに、削除済みのすべてのコンテンツの一覧が含まれます。作業が完了したら、次のコマンドでこれらのテーブルをドロップできます。

DROP TABLE content_delete;
DROP TABLE pagetemp_delete;

完了したら、インデックスをゼロから再構築して ancestor テーブルを再構築します。

関連ページ

PostgreSQL でデータベース制約をドロップおよび再作成する方法

最終更新日 2019 年 8 月 19 日

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

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