List inactive (unused) entity tables

It is pretty simple to get a list of all inactive entity (unused – the entity got removed from OutSystems) database tables.

SELECT esp.name ESPACE,
ent.name ENTITY,
ent.physical_table_name TABLE_NAME
FROM osadmin.ossys_espace esp
INNER JOIN osadmin.ossys_entity ent ON ent.espace_id = esp.id
WHERE ent.is_system = 0
AND ent.is_active = 0
ORDER BY esp.name, ent.name;

List orphaned table columns

To get a list of all orphan table columns (table columns that are not registered in OutSystems metadata anymore – happens when an entity attribute is renamed) that exist in any active OutSystems user table, use one of the following queries.

The query when executed as user OSADMIN:

SELECT esp.name ESPACE,
ent.name ENTITY,
tco.table_name,
tco.column_name
FROM ossys_espace esp
INNER JOIN ossys_entity ent ON ent.espace_id = esp.id
INNER JOIN user_tab_cols tco ON tco.table_name = UPPER(ent.physical_table_name)
WHERE tco.column_id IS NOT NULL
AND ent.is_system = 0
AND ent.is_active = 1
AND NOT EXISTS (SELECT 1
FROM ossys_entity_attr eat
WHERE eat.entity_id = ent.id
AND UPPER(eat.name) = tco.column_name)
ORDER BY esp.name, ent.name, tco.column_id;

The query when executed as a DBA user:

SELECT esp.name ESPACE,
ent.name ENTITY,
tco.table_name,
tco.column_name
FROM osadmin.ossys_espace esp
INNER JOIN osadmin.ossys_entity ent ON ent.espace_id = esp.id
INNER JOIN dba_tab_cols tco ON tco.table_name = UPPER(ent.physical_table_name)
WHERE tco.owner = ‘OSADMIN’
AND tco.column_id IS NOT NULL
AND ent.is_system = 0
AND ent.is_active = 1
AND NOT EXISTS (SELECT 1
FROM osadmin.ossys_entity_attr eat
WHERE eat.entity_id = ent.id
AND UPPER(eat.name) = tco.column_name)
ORDER BY esp.name, ent.name, tco.column_id;

Space used by inactive (unused) entities

Like described in one of my other posts (Adding/Changing/Deleting entities) on OutSystems, the platform does not physically remove a table from the database when you delete an entity. While the physical tables are never removed from the database, they will keep using space in the database, space that cannot be (re)used.

The following query will show you what tables are not used by OutSystems anymore (are inactive) and how much space they use in the database.

The query when executed as user OSADMIN:

WITH table_size AS (
SELECT tab.table_name,
SUM(seg.bytes)/1024/1024 size_mb
FROM user_tables tab, user_segments seg
WHERE tab.table_name = seg.segment_name
AND segment_type LIKE ‘TABLE%’
GROUP BY tab.table_name
), tab_indexes_size AS (
SELECT ind.table_name,
SUM(seg.bytes)/1024/1024 size_mb
FROM user_indexes ind, user_segments seg
WHERE ind.index_name = seg.segment_name
AND seg.segment_type LIKE ‘INDEX%’
GROUP BY ind.table_name
)
SELECT es.name espace,
en.name Entity,
UPPER(en.physical_table_name) unused_table,
NVL(ts.size_mb,0)+NVL(ise.size_mb,0) “Total size (MB)”,
NVL(ts.size_mb,0) “Table size (MB)”,
NVL(ise.size_mb,0) “Indexes (sum) size (MB)”
FROM ossys_entity en
INNER JOIN (ossys_espace) es ON (en.espace_id) = es.id
LEFT OUTER JOIN (table_size) ts ON UPPER(en.physical_table_name) = ts.table_name
LEFT OUTER JOIN (tab_indexes_size) ise ON UPPER(en.physical_table_name) = ise.table_name
WHERE UPPER(en.physical_table_name) LIKE ‘OSUSR_%’
AND en.is_active = 0
UNION ALL
SELECT es.name,
‘All Unused Entities’,
‘-‘,
SUM(NVL(ts.size_mb,0)+NVL(ise.size_mb,0)) “Total size (MB)”,
SUM(NVL(ts.size_mb,0)) “Table size (MB)”,
SUM(NVL(ise.size_mb,0)) “Indexes (sum) size (MB)”
FROM ossys_entity en
INNER JOIN (ossys_espace) es ON (en.espace_id) = es.id
LEFT OUTER JOIN (table_size) ts ON UPPER(en.physical_table_name) = ts.table_name
LEFT OUTER JOIN (tab_indexes_size) ise ON UPPER(en.physical_table_name) = ise.table_name
WHERE UPPER(en.physical_table_name) LIKE ‘OSUSR_%’
AND en.is_active = 0
GROUP BY es.name
ORDER BY 1 ASC, 3 DESC;

The query when executed as a DBA user:

WITH table_size AS (
SELECT tab.table_name, SUM(seg.bytes)/1024/1024 size_mb
FROM dba_tables tab, dba_segments seg
WHERE tab.table_name = seg.segment_name
AND segment_type LIKE ‘TABLE%’
AND tab.owner = ‘OSADMIN’
GROUP BY tab.table_name
), tab_indexes_size AS (
SELECT ind.table_name, SUM(seg.bytes)/1024/1024 size_mb
FROM dba_indexes ind, dba_segments seg
WHERE ind.index_name = seg.segment_name
AND ind.table_owner = seg.owner
AND seg.segment_type LIKE ‘INDEX%’
AND ind.table_owner = ‘OSADMIN’
GROUP BY ind.table_name
)
SELECT es.name espace,
en.name Entity,
UPPER(en.physical_table_name) unused_table,
NVL(ts.size_mb,0)+NVL(ise.size_mb,0) “Total size (MB)”,
NVL(ts.size_mb,0) “Table size (MB)”,
NVL(ise.size_mb,0) “Indexes (sum) size (MB)”
FROM osadmin.ossys_entity en
INNER JOIN (osadmin.ossys_espace) es ON (en.espace_id) = es.id
LEFT OUTER JOIN (table_size) ts ON UPPER(en.physical_table_name) = ts.table_name
LEFT OUTER JOIN (tab_indexes_size) ise ON UPPER(en.physical_table_name) = ise.table_name
WHERE UPPER(en.physical_table_name) LIKE ‘OSUSR_%’
AND en.is_active = 0
UNION ALL
SELECT es.name,
‘All Unused Entities’,
‘-‘,
SUM(NVL(ts.size_mb,0)+NVL(ise.size_mb,0)) “Total size (MB)”,
SUM(NVL(ts.size_mb,0)) “Table size (MB)”,
SUM(NVL(ise.size_mb,0)) “Indexes (sum) size (MB)”
FROM osadmin.ossys_entity en
INNER JOIN (osadmin.ossys_espace) es ON (en.espace_id) = es.id
LEFT OUTER JOIN (table_size) ts ON UPPER(en.physical_table_name) = ts.table_name
LEFT OUTER JOIN (tab_indexes_size) ise ON UPPER(en.physical_table_name) = ise.table_name
WHERE UPPER(en.physical_table_name) LIKE ‘OSUSR_%’
AND en.is_active = 0
GROUP BY es.name
ORDER BY 1 ASC, 3 DESC;