In this post I will provide some queries that you can use to get information on the OutSystems schemas in an Oracle database.
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;
This article on Quobell website.