OutSystems – Getting Database Information

      No Comments on OutSystems – Getting Database Information

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.