| Auszug |
|---|
|
Oracle-SQLPlus-Abfragen für Nuclos-Betrieb: Tablespace-, Schema- und Tabellengröße ermitteln und Schema-Statistiken erzeugen. |
Sprache: Deutsch · English
Oracle SqlPlus Tools
Nützliche SQLPlus-Abfragen für Oracle: Größe von Tablespaces, Schemas und Tabellen sowie Statistiken.
| Status |
|---|
| colour | Green |
|---|
| title | Stand: Jul 2026 |
|---|
|
| Status |
|---|
| colour | Grey |
|---|
| title | gilt fuer Nuclos 4.2026.x |
|---|
|
Größe der Tablespaces
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 ROUND(100 * ( (df.totalspace - tu.totalusedspace) / df.totalspace))
"Pct. Free"
from
FROM (selectSELECT tablespace_name,
round ROUND(sumSUM(bytes) / 1048576) TotalSpace
fromtotalspace
FROM dba_data_files
group GROUP byBY tablespace_name) df,
(selectSELECT roundROUND(sumSUM(bytes)/(1024*1024) / 1048576) totalusedspace, tablespace_name
from FROM dba_segments
group GROUP byBY tablespace_name) tu
whereWHERE df.tablespace_name = tu.tablespace_name ; |
...
Größe der Schemas
...
| Codeblock |
|---|
|
SELECT ROUND(SUMselect
round(sum(bytes)/1024/1024) asAS size_in_megamb, owner
from
FROM dba_segments
group by
GROUP BY owner
order ORDER byBY size_in_megamb; |
...
Schema-Statistik erzeugen
| Codeblock |
|---|
|
BEGIN
|
| Codeblock |
|---|
begin
dbms_stats.gather_schema_stats ('<schema>');
endEND;
/ |
...
Größe der Tabellen
...
(reine Daten
...
)
...
| Codeblock |
|---|
|
selectSELECT owner, table_name, roundROUND((num_rows * avg_row_len)/(1024*1024)) MB
fromFROM all_tables
whereWHERE owner = '<schema>'
and AND num_rows > 0 -- Ignore empty Tables.
order by
ORDER BY MB asc -- Biggest last.
; |
...
Verwandte Seiten
