Versionen im Vergleich

Alte Version 1

changes.mady.by.user Inga Sertel

Gespeichert am

verglichen mit

Neue Version Aktuell

changes.mady.by.user Inga Sertel

Gespeichert am

Schlüssel

  • Diese Zeile wurde hinzugefügt.
  • Diese Zeile wurde entfernt.
  • Formatierung wurde geändert.

1) Größe der Tablespaces feststellen:

Codeblock
select df.tablespace_name "Tablespace",
totalusedspace "Used MB",
(df.totalspace - tu.totalusedspace) "Free MB",
df.totalspace "Total MB",
round(100 * ( (df.totalspace - tu.totalusedspace)/ df.totalspace))
"Pct. Free"
from
(select tablespace_name,
round(sum(bytes) / 1048576) TotalSpace
from dba_data_files
group by tablespace_name) df,
(select round(sum(bytes)/(1024*1024)) totalusedspace, tablespace_name
from dba_segments
group by tablespace_name) tu
where df.tablespace_name = tu.tablespace_name ;

 


2) Größe der Schemas feststellen:

Codeblock
select
   round(sum(bytes)/1024/1024) as size_in_mega, owner
from
   dba_segments
group by
  owner
order by size_in_mega;


3) Statistic über ein Schema erstellen:

Codeblock
begin
dbms_stats.gather_schema_stats ('<schema>');
end;
/

4) Größe der Tabellen feststellen (reine Daten ohne CLOB und BLOB):

Codeblock
select owner, table_name, round((num_rows*avg_row_len)/(1024*1024)) MB 
from all_tables 
where owner = '<schema>'
and num_rows > 0 -- Ignore empty Tables.
order by MB asc -- Biggest last.
;


5) Gesamtgröße der Tabellen feststellen (mit Index, LOBs und alle subpartitionen):

Codeblock
SELECT owner, table_name, ROUND(sum(bytes)/1024/1024/1024, 2) GB FROM (SELECT segment_name table_name, owner, bytes FROM dba_segments WHERE segment_type IN ('TABLE', 'TABLE PARTITION', 'TABLE SUBPARTITION') UNION ALL SELECT i.table_name, i.owner, s.bytes FROM dba_indexes i, dba_segments s WHERE s.segment_name = i.index_name AND s.owner = i.owner AND s.segment_type IN ('INDEX', 'INDEX PARTITION', 'INDEX SUBPARTITION') UNION ALL SELECT l.table_name, l.owner, s.bytes FROM dba_lobs l, dba_segments s WHERE s.segment_name = l.segment_name AND s.owner = l.owner AND s.segment_type = 'LOBSEGMENT' UNION ALL SELECT l.table_name, l.owner, s.bytes FROM dba_lobs l, dba_segments s WHERE s.segment_name = l.index_name AND s.owner = l.owner AND s.segment_type = 'LOBINDEX') WHERE owner in UPPER('&owner') GROUP BY table_name, owner HAVING SUM(bytes)/1024/1024 > 10 /* Ignore really small tables */ ORDER BY SUM(bytes) DESC ;

 

...