How to query the database to find the size of confluence spaces
Platform notice: Server and Data Center only. This article only applies to Atlassian products on the server and data center platforms.
Summary
This document will help to identify all spaces size from the underlying database.
Solution
The below SQL will help us to know the size of spaces.
Oracle
SELECT Count(CONTENT.CONTENTID) AS number_of_pages,
nvl((sum(dbms_lob.getlength(bodycontent.BODY))),0) AS total_size_bytes,
SPACES.spacename as space_name
FROM BODYCONTENT
inner join content
ON ( CONTENT.CONTENTID = BODYCONTENT.CONTENTID )
inner join SPACES
ON ( CONTENT.SPACEID = SPACES.SPACEID )
WHERE BODYCONTENT.CONTENTID IN (SELECT CONTENTID
FROM CONTENT
WHERE content_status <> 'deleted'
AND (contenttype = 'PAGE'
OR contenttype = 'BLOGPOST'))
GROUP BY SPACES.spacename;
Postgres
SELECT Count(CONTENT.CONTENTID) AS number_of_pages,
sum(LENGTH(bodycontent.BODY)) AS total_size_bytes,
SPACES.spacename as space_name
FROM BODYCONTENT
inner join content
ON ( CONTENT.CONTENTID = BODYCONTENT.CONTENTID )
inner join SPACES
ON ( CONTENT.SPACEID = SPACES.SPACEID )
WHERE BODYCONTENT.CONTENTID IN (SELECT CONTENTID
FROM CONTENT
WHERE content_status <> 'deleted'
AND (contenttype = 'PAGE'
OR contenttype = 'BLOGPOST'))
GROUP BY SPACES.spacename;
Last modified on Mar 13, 2023
Powered by Confluence and Scroll Viewport.