Skip to Content
Last modified by Nefedenkova Viktoria on 2022/09/30 13:13
Show last authors
1 Как получить размер таблиц в Oracle, подробно можно ознакомиться [[тут>>https://overcoder.net/q/12059/%D0%BA%D0%B0%D0%BA-%D1%80%D0%B0%D1%81%D1%81%D1%87%D0%B8%D1%82%D0%B0%D1%82%D1%8C-%D1%80%D0%B0%D0%B7%D0%BC%D0%B5%D1%80-%D1%82%D0%B0%D0%B1%D0%BB%D0%B8%D1%86%D1%8B-%D0%B2-oracle||rel="__blank" title="Рассчитать размер таблицы в Oracle"]].
2
3 Сам запрос выглядит так:
4
5 (% style="font-size: 14px; font-family: Consolas; background-color:#fbfdff" %)
6 SELECT
7 owner,
8 table_name,
9 TRUNC(sum(bytes)/1024/1024) Meg,
10 ROUND( ratio_to_report( sum(bytes) ) over () * 100) Percent
11 FROM
12 (SELECT segment_name table_name, owner, bytes
13 FROM dba_segments
14 WHERE segment_type IN ('TABLE', 'TABLE PARTITION', 'TABLE SUBPARTITION')
15 UNION ALL
16 SELECT i.table_name, i.owner, s.bytes
17 FROM dba_indexes i, dba_segments s
18 WHERE s.segment_name = i.index_name
19 AND s.owner = i.owner
20 AND s.segment_type IN ('INDEX', 'INDEX PARTITION', 'INDEX SUBPARTITION')
21 UNION ALL
22 SELECT l.table_name, l.owner, s.bytes
23 FROM dba_lobs l, dba_segments s
24 WHERE s.segment_name = l.segment_name
25 AND s.owner = l.owner
26 AND s.segment_type IN ('LOBSEGMENT', 'LOB PARTITION')
27 UNION ALL
28 SELECT l.table_name, l.owner, s.bytes
29 FROM dba_lobs l, dba_segments s
30 WHERE s.segment_name = l.index_name
31 AND s.owner = l.owner
32 AND s.segment_type = 'LOBINDEX')
33 WHERE owner in UPPER('&owner')
34 GROUP BY table_name, owner
35 HAVING SUM(bytes)/1024/1024 > 10 /* Ignore really small tables */
36 ORDER BY SUM(bytes) desc;
37
38 Запрос выдает, сколько места распределено для каждой таблицы с учетом индексов и любых LOB в таблице.