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

関連コンテンツ

  • 関連コンテンツがありません

お困りですか?

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

コミュニティに質問

問題

スペースのインポートがタイムアウトに失敗するか、データベース内のスペース情報が破損すると、スペースやコンテンツにアクセスしたり、スペース管理ページからスペースやコンテンツを削除したりできなくなる。

See: CONF-31528 - Getting issue details... STATUS

ソリューション 1

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

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

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

ソリューション 2

インポートされたスペースと関連コンテンツを、Confluence データベースから手動で削除できます。

(info) データベースの更新を行う前に、次の点をご確認ください。

  • これはサポート対象外の操作であるため、以降のクエリは参考情報として提供されます。
  • 最初に「スペースを削除できない」をご確認ください。
  • After following these resolution steps, attachments for this space will be orphaned in the attachment directory.  Attachment files related to the space can be removed manually from the file server disk by deleting the space sub-folder (as noted by the space ID in question) as well as all of its sub-folders.  This ONLY applies for attachments stored under the old ver003 folder structure which means only on Confluence versions prior to v8.1.0 as from v8.1.0 onwards Confluence uses version 4 storage layout format. 
    • (warning) スペース ID フォルダとそのサブフォルダを削除する前に、これらのバックアップを作成して別の場所に保存してください。   
    • (info)  Also, for more information on how Confluence stores attachments on the file system under ver003, please see ver003 Hierarchical File System Attachment Storage. 

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

  • また、Confluence 7.x 以降では EXTRNLNKS および TRACKBACKLINKS テーブルは廃止となっているため、データベースには存在しない点にご注意ください。このため、これら 2 つのテーブルを対象としたコマンドはスキップできます。


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

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

準備

  1. 削除するスペースのスペース キーとスペース ID を特定します。これは、次のクエリを実行することで確認できます。  

    SELECT SPACEID, SPACEKEY, SPACENAME FROM SPACES WHERE SPACENAME = '<enter-the-space-name>';
  2. これらのクエリを実行する前に Confluence を停止してください。
  3. 次に、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 IN (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 IN (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 NOT IN (SELECT SPACEID FROM DELETE_SPACE);

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

SELECT * FROM CONTENT WHERE PREVVER IN (SELECT * FROM DELETE_CONTENT) AND SPACEID NOT IN (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 destspacekey IN (SELECT SPACEKEY FROM DELETE_SPACE);
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 IN (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 IN (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 CONTENT_RELATION (particularly relevant if the imported space was from Cloud) */ 
DELETE FROM CONTENT_RELATION WHERE SOURCECONTENTID 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 IN (SELECT SPACEKEY FROM DELETE_SPACE);

/* Delete the space from SPACES */
DELETE FROM SPACES WHERE SPACEID IN (SELECT SPACEID FROM DELETE_SPACE);
 
/* Delete record related to space from BANDANA */
DELETE FROM BANDANA WHERE BANDANACONTEXT IN (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 IN (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 IN (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 NOT IN (SELECT SPACEID FROM DELETE_SPACE);
SELECT * FROM CONTENT WHERE PAGEID IN (SELECT * FROM DELETE_CONTENT) AND SPACEID NOT IN (SELECT SPACEID FROM DELETE_SPACE);
SELECT * FROM CONTENT WHERE PREVVER IN (SELECT * FROM DELETE_CONTENT) AND SPACEID NOT IN (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 DESTSPACEKEY IN (SELECT SPACEKEY FROM DELETE_SPACE);
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 IN (SELECT SPACEID FROM DELETE_SPACE);
 
/* Get currently active templates from PAGETEMPLATES */
SELECT TEMPLATEID INTO DELETE_TEMPLATE_CURRENT FROM PAGETEMPLATES WHERE SPACEID IN (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 IN (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 CONTENT_RELATION (particularly relevant if the imported space was from Cloud) */ 
DELETE FROM CONTENT_RELATION WHERE SOURCECONTENTID 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 IN (SELECT SPACEKEY FROM DELETE_SPACE);

/* Delete the space from SPACES */
DELETE FROM SPACES WHERE SPACEID IN (SELECT SPACEID FROM DELETE_SPACE);
 
/* Delete record related to space from BANDANA */
DELETE FROM BANDANA WHERE BANDANACONTEXT IN (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 IN (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 IN (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)
UNION
SELECT CONTENTID FROM CONTENT WHERE PAGEID IN (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 NOT IN (SELECT SPACEID FROM DELETE_SPACE);
SELECT * FROM CONTENT WHERE PAGEID IN (SELECT * FROM DELETE_CONTENT) AND SPACEID NOT IN (SELECT SPACEID FROM DELETE_SPACE);
SELECT * FROM CONTENT WHERE PREVVER IN (SELECT * FROM DELETE_CONTENT) AND SPACEID NOT IN (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 destspacekey IN (SELECT SPACEKEY FROM DELETE_SPACE);
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 IN (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 IN (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 CONTENT_RELATION (particularly relevant if the imported space was from Cloud) */ 
DELETE FROM CONTENT_RELATION WHERE SOURCECONTENTID IN (SELECT CONTENTID FROM DELETE_CONTENT_ALL);
DELETE FROM CONTENT_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 IN (SELECT SPACEKEY FROM DELETE_SPACE);

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

/* Delete record related to space from BANDANA */
DELETE FROM BANDANA WHERE BANDANACONTEXT IN (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 IN (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 IN (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 NOT IN (SELECT SPACEID FROM DELETE_SPACE);
SELECT * FROM CONTENT WHERE PAGEID IN (SELECT * FROM DELETE_CONTENT) AND SPACEID NOT IN (SELECT SPACEID FROM DELETE_SPACE);
SELECT * FROM CONTENT WHERE PREVVER IN (SELECT * FROM DELETE_CONTENT) AND SPACEID NOT IN (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 destspacekey IN (SELECT SPACEKEY FROM DELETE_SPACE);
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 IN (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 IN (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 CONTENT_RELATION (particularly relevant if the imported space was from Cloud) */ 
DELETE FROM CONTENT_RELATION WHERE SOURCECONTENTID 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 IN (SELECT SPACEKEY FROM DELETE_SPACE);

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

/* Delete record related to space from BANDANA */
DELETE FROM BANDANA WHERE BANDANACONTEXT IN (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 IN (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 IN (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 NOT IN (SELECT SPACEID FROM DELETE_SPACE);
SELECT * FROM CONTENT WHERE PAGEID IN (SELECT * FROM DELETE_CONTENT) AND SPACEID NOT IN (SELECT SPACEID FROM DELETE_SPACE);
SELECT * FROM CONTENT WHERE PREVVER IN (SELECT * FROM DELETE_CONTENT) AND SPACEID NOT IN (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 destspacekey IN (SELECT SPACEKEY FROM DELETE_SPACE);
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 IN (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 IN (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 CONTENT_RELATION (particularly relevant if the imported space was from Cloud) */ 
DELETE FROM CONTENT_RELATION WHERE SOURCECONTENTID 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 IN (SELECT SPACEKEY FROM DELETE_SPACE);

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

/* Delete record related to space from BANDANA */
DELETE FROM BANDANA WHERE BANDANACONTEXT IN (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 IN (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 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 destspacekey IN (SELECT SPACEKEY FROM DELETE_SPACE);
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 IN (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 テーブルを再構築します。

(info) 注意: メインの CONTENT テーブルからレコードを削除すると、整合性の制約違反のエラーが発生する場合があります。違反を発生させているレコードを特定するのは非常に難しいため、1 つの回避策として、エラー メッセージで参照されている制約チェックを一時的に無効化する方法が考えられます。この方法の例をご紹介します。

MySQL

-- Disable single constraint at the session level
SET FOREIGN_KEY_CHECKS=0;

-- rerun the deletion command for the main CONTENT table in the script above...

DELETE FROM CONTENT WHERE CONTENTID IN (SELECT CONTENTID FROM DELETE_CONTENT);

-- Enable single constraint

SET FOREIGN_KEY_CHECKS=1;
Microsoft SQL Server

-- Disable single constraint

ALTER TABLE content NOCHECK CONSTRAINT constraint_name;

-- rerun the deletion command for the main CONTENT table in the script above...

DELETE FROM CONTENT WHERE CONTENTID IN (SELECT CONTENTID FROM DELETE_CONTENT);

-- Enable single constraint
ALTER TABLE content CHECK CONSTRAINT constraint_name;

Postgres

-- Disable single constraint at the session level

ALTER TABLE content DISABLE TRIGGER constraint_name;

-- rerun the deletion command for the main CONTENT table in the script above...

DELETE FROM CONTENT WHERE CONTENTID IN (SELECT CONTENTID FROM DELETE_CONTENT);

-- Enable single constraint

ALTER TABLE content ENABLE TRIGGER constraint_name;

Oracle

-- Disable single constraint at the session level

ALTER TABLE content DISABLE CONSTRAINT constraint_name;

-- rerun the deletion command for the main CONTENT table in the script above...

DELETE FROM CONTENT WHERE CONTENTID IN (SELECT CONTENTID FROM DELETE_CONTENT);

-- Enable single constraint

ALTER TABLE content ENABLE CONSTRAINT constraint_name;

関連ページ

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

最終更新日 2024 年 9 月 19 日

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

はい
いいえ
この記事についてのフィードバックを送信する

関連コンテンツ

  • 関連コンテンツがありません
Powered by Confluence and Scroll Viewport.