Getting Database information from OutSystems

OutSystems on Oracle (author: Marcel Lambrechts)

inSystems monitors

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;

Share this

Contact

Heb je vragen of opmerkingen? Aarzel niet om contact op te nemen. Dat kan via de algemene contactgegevens, rechtstreeks met de genoemde contactpersoon op een van de detailpagina’s, of via onderstaande knop.

28 april 2025

Toegankelijkheidfuncties in OutSystems

In deze blog beschrijft Paul Reuvers de mogelijkheden die OutSystems biedt om toegankelijkheidsfuncties in te schakelen en mee te werken, bijv. om slechtzienden een pagina goed te kunnen laten bekijken.

7 mei 2025

Milko Zuijdendorp 12,5 jaar in dienst

Time flies when you’re having fun! … en dat geldt zeker in dit geval. Het is al weer 12,5 geleden dat Milko Zuijdendorp onze gelederen kwam versterken, destijds nog onder de fier wapperende Quobell-vlag.

Opdrachten uitgevoerd voor

VION Food Group
Politie
Arval BNP Paribas
Ziggo
Gemeente Apeldoorn
Cannock
RIGD LOXIA
inSystems KLM
Van Iperen Groeispecialisten
Bank Mendes Gans
SVB
Provincie Utrecht
Gemeente Gouda
Eneco
Universiteit Tilburg
Telegraaf Media Groep
Alphabet
UBR Uitvoeringsorganisatie Bedrijfsvoering Rijk
Rabobank
VAA ICT Consultancy
Altran
WVDB Adviseurs Accountants
ASR Nederland
Allianz
VTTI
MN Services
Gemeente Den Haag
Multi Tank Card
Rechtspraak
PC Uitvaart
DICTU
TU Eindhoven
Greenchoice
Stedin
Oracle University
BMW
Ahold
Nederlandse Spoorwegen
Fokker
Ministerie van Economische Zaken, Landbouw & Innovatie
Rendo
Leaseplan
IBM
ING Bank
TenneT
GVB
CBR
RAVU
Darling
Thales
Syntrus Achmea Real Estate & Finance
JUVA
CB Logistics