Teil 10: Platzverbrauch in der Datenbank

Laufend wird ein Tablespace zu klein, immer geht irgendwo der Platz aus! Was sind jetzt die großen Objekte in der Datenbank?

Natürlich ist es einfach zu prüfen, welches die größten Objekte in der Datenbank sind (DBA_SEGMENTS.BYTES), aber dies berücksichtigt leider nicht welche Indexes und Lobs zu einer Tabelle dazu gehören und wenn man Partitioning einsetzt, bekommt man über diese Weg oft nicht einmal die größten Tabellen (weil diese viele Partitionen haben).

Aufbereitung der Platzinformation

Das Statement liefert eine Liste mit folgendem Inhalt:

• OWNER ... Eigentümer der Tabelle (oder des MViews), wobei die Oracle eigenen Benutzer ausgenommen sind. Wenn Sie das nicht möchten, brauchen Sie nur im ersten WITH Block die WHERE Clause löschen - dann bekommen Sie auch die Oracle Benutzer.
• TABLE_NAME ... Name der Tabelle, des MViews bzw. des MView Logs (sind intern ja auch nur Tabellen).
• TABLE_MB ... Größe der Tabelle in MB. Wenn es sich um partitionierte Tabellen handelt, werden alle Partitionens zusammen gezählt.
• INDEX_MB ... Größe aller Indexes auf der Tabelle.
• LOB_SEGMENT_MB ... Größe aller LOB Segmente dieser Tabelle.
• LOB_INDEX_MB ... Größe der LOB Index Segmente dieser Tabelle.

Mögliche Einschränkung:

Wir haben keine Domain Indexes (beispielsweise Oracle Text Indexes) und Nested Tables verifiziert - diese kommen in der Praxis nicht sehr oft vor. IOT fallen leider durch die Analyse, weil es hier keine Tabelle gibt (sondern nur Indexes).

Weitere Hinweise:

• Das Statement funktioniert mit allen Oracle Versionen. Ab Oracle 12c kann man die Oracle Supplied User einfacher feststellen, wie im Artikel Oracle Supplied Users beschrieben.
• Wenn auch die Oracle Supplied User mit ausgegeben werden sollen, einfach die WHERE Clause im ersten WITH Block entfernen.

Platzverbrauchanalyse-Statement

with myOwner as
(select /*+ MATERIALIZE */ username from dba_users
  where username not in (select OWNER 
                           from DBA_LOGSTDBY_SKIP 
                          where STATEMENT_OPT = 'INTERNAL SCHEMA' 
                          union all
                         select username from dba_users
                          where username like 'APEX%'
                        )
),
my_lobs as
(select /*+ MATERIALIZE */ owner, table_name, column_name, segment_name as log_segment, index_name as lob_index
 from dba_lobs
 where owner in (select username from myOwner)
 ),
my_segments as
(select /*+ MATERIALIZE */ owner, segment_name, segment_type, trunc(bytes/1024/1024,3) as MB
   from dba_segments
  where owner in (select username from myOwner)
),
my_indx as
(select /*+ MATERIALIZE */ owner, table_name, index_name, index_type
   from dba_indexes
  where (owner, table_name) in (select owner, segment_name from my_segments where segment_type='TABLE')
)
select ms.owner, ms.segment_name as Table_name,
       sum(ms.MB) as TABLE_MB,
       (select sum(MB) from my_segments s join my_indx i on (s.owner = i.owner and s.segment_name = i.index_name)
         where ms.owner=s.owner and ms.segment_name = i.table_name) as INDEX_MB,
       (select sum(MB) from my_segments s join my_lobs l on (s.owner = l.owner and s.segment_name = l.log_segment)
         where ms.owner=s.owner and ms.segment_name = l.table_name)  as LOB_SEGMENT_MB,
       (select sum(MB) from my_segments s join my_lobs l on (s.owner = l.owner and s.segment_name = l.lob_index)
         where ms.owner=s.owner and ms.segment_name = l.table_name) AS LOB_INDEX_MB
  from my_segments ms
 where segment_type like 'TABLE%' or segment_Type='CLUSTER'
 group by owner, segment_name
 order by owner, nvl(TABLE_MB,0) + nvl(INDEX_MB,0) + nvl(LOB_SEGMENT_MB,0) + nvl(LOB_INDEX_MB,0) desc;

Beispiel Ergebnis

OWNER  TABLE_NAME    TABLE_MB   INDEX_MB LOB_SEGMENT_MB LOB_INDEX_MB
------ ----------- ---------- ---------- -------------- ------------
SCOTT  FOO               .062       .062
SCOTT  MLOG$_FOO         .062       .062
SCOTT  SYS_TAB           .062
SCOTT  SCOTT_TAB         .062       .124           .124         .062