How to analyze the disk size of library items in Spotfire 12.0 LTS or higher

Problem:

Spotfire 12.0 LTS or higher supports library versioning of library items. As Spotfire admin, I want to analyze the disk size of version history for all DXP in a library.

Resolution:

The following SQL can be ran against the Spotfire server database to retrieve the name, full library path, file size, and the version history name of all DXP items in a library.

• The SQL was created for Oracle database and for Spotfire 12.0 LTS or higher.
• You need to change the SPOTFIRE_DB_SCHEMA to your organization's Oracle schema.
• SQL can be execute from Spotfire Connector and then analyzed using Spotfire in the way you like.

WITH
List1(ITEM_ID, TITLE, LIBRARY_PATH, PARENT_ID) as(
SELECT
A.ITEM_ID,
A.TITLE,
CAST(A.TITLE AS varchar(2000)) AS LIBRARY_PATH, 
A.Parent_ID
FROM 
SPOTFIRE_DB_SCHEMA.LIB_CURRENT_ITEMS A
LEFT JOIN SPOTFIRE_DB_SCHEMA.LIB_ITEM_TYPES lit on lit.TYPE_ID=A.ITEM_TYPE
WHERE
lit.DISPLAY_NAME='folder' and A.hidden=0
),
List2(ITEM_ID, TITLE, LIBRARY_PATH,PARENT_ID) as(
SELECT
ITEM_ID,
TITLE,
CAST(TITLE AS nvarchar2(1000)) AS LIBRARY_PATH, 
Parent_ID
FROM List1
WHERE TITLE = 'root'
UNION ALL
SELECT
A.ITEM_ID,
A.TITLE,
REPLACE(CAST((B.LIBRARY_PATH || '/' || A.TITLE) AS nvarchar2(1000)),'root', '') AS LIBRARY_PATH, 
A.Parent_ID
FROM List1 A inner join List2 B ON A.PARENT_ID = B.ITEM_ID
),
List3(item_id, title, parent_id, display_name) as(
SELECT A.item_id, A.title, A.parent_id, lit.DISPLAY_NAME
FROM SPOTFIRE_DB_SCHEMA.LIB_CURRENT_ITEMS A
LEFT JOIN SPOTFIRE_DB_SCHEMA.LIB_ITEM_TYPES lit on lit.TYPE_ID=A.ITEM_TYPE
WHERE lit.DISPLAY_NAME = 'dxp'
),
List4(ITEM_ID, TITLE, PATH, DATA_ID,VERSION_NAME) as(
SELECT
A.item_id as ITEM_ID,
A.TITLE as TITLE,
REPLACE(CAST((B.LIBRARY_PATH || '/' || A.TITLE) AS nvarchar2(1000)),'root', '') AS PATH,
C.DATA_ID as DATA_ID,
C.NAME as VERSION_NAME
FROM List3 A
left join List2 B on A.parent_id=B.item_id
left join lib_item_versions C on A.item_id=C.item_ID
) select A.ITEM_ID,A.TITLE,A.PATH,A.DATA_ID,A.VERSION_NAME,B.CONTENT_SIZE from List4 A left join LIB_DATA B on A.DATA_ID=B.DATA_ID