Будучи использованным (и потенциально испорченным) MSSQL, мне интересно, как я могу получить размер таблиц в Oracle 10g. У меня есть googled, поэтому я теперь знаю, что у меня может быть не так просто, как sp_spaceused. Тем не менее, потенциальные ответы, которые я получил, в большинстве случаев устарели или не работают. Возможно, потому, что я не администратор базы данных по схеме, с которой я работаю.
У кого-нибудь есть решения или рекомендации?
Вам может быть интересен этот запрос. Он сообщает вам, сколько места распределено для каждой таблицы с учетом индексов и любых LOB в таблице. Часто вам интересно узнать "Сколько пробелов занимает таблица заказов на поставку, включая любые индексы", а не только сама таблица. Вы всегда можете вникать в детали. Обратите внимание, что для этого требуется доступ к представлениям DBA_ *.
COLUMN TABLE_NAME FORMAT A32
COLUMN OBJECT_NAME FORMAT A32
COLUMN OWNER FORMAT A10
SELECT
owner,
table_name,
TRUNC(sum(bytes)/1024/1024) Meg,
ROUND( ratio_to_report( sum(bytes) ) over () * 100) Percent
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 IN ('LOBSEGMENT', 'LOB PARTITION')
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
;
-- Tables + Size MB
select owner, table_name, round((num_rows*avg_row_len)/(1024*1024)) MB
from all_tables
where owner not like 'SYS%' -- Exclude system tables.
and num_rows > 0 -- Ignore empty Tables.
order by MB desc -- Biggest first.
;
--Tables + Rows
select owner, table_name, num_rows
from all_tables
where owner not like 'SYS%' -- Exclude system tables.
and num_rows > 0 -- Ignore empty Tables.
order by num_rows desc -- Biggest first.
;
Примечание. Это оценки, сделанные более точно с помощью сбора статистики:
exec dbms_utility.analyze_schema(user,'COMPUTE');
null
( num_rows
, avg_row_len
), вам необходимо провести некоторый анализ, прежде чем с помощью следующего оператора ANALYZE TABLE your_table COMPUTE STATISTICS
Во-первых, я обычно предупреждал, что сбор табличной статистики для проведения космического анализа является потенциально опасной задачей. Сбор статистики может изменить планы запросов, особенно если администратор базы данных настроил задание сбора статистики, которое использует параметры, отличные от параметров по умолчанию, которые ваш вызов не использует, и заставит Oracle повторно разбирать запросы, которые используют рассматриваемую таблицу, которая может быть высокой удар. Если администратор базы данных намеренно покинул несколько таблиц без статистики (общий, если ваш OPTIMIZER_MODE
CHOOSE), сбор статистики может привести к тому, что Oracle перестанет использовать оптимизатор на основе правил и начнет использовать оптимизатор на основе затрат для набора запросов, которые могут быть серьезная головная боль, если это неожиданно происходит в производстве. Если ваша статистика верна, вы можете напрямую запросить USER_TABLES
(или ALL_TABLES
или DBA_TABLES
), не вызывая GATHER_TABLE_STATS
. Если ваши статистические данные не точны, вероятно, есть причина для этого, и вы не хотите нарушать статус-кво.
Во-вторых, ближайшим эквивалентом процедуры SQL Server sp_spaceused
является, скорее всего, пакет Oracle DBMS_SPACE
. У Tom Kyte есть приятная show_space
процедура, которая обеспечивает простой интерфейс для этого пакета и выводит информацию, аналогичную тому, что печатает sp_spaceused
.
Сначала собирайте статистику оптимизатора в таблице (если вы еще этого не сделали):
begin
dbms_stats.gather_table_stats('MYSCHEMA','MYTABLE');
end;
/
ПРЕДУПРЕЖДЕНИЕ: Как говорит Джастин в своем ответе, сбор статистики оптимизатора влияет на оптимизацию запросов и не должен выполняться без должной осторожности и внимания!
Затем найдите количество блоков, занятых таблицей, из созданной статистики:
select blocks, empty_blocks, num_freelist_blocks
from all_tables
where owner = 'MYSCHEMA'
and table_name = 'MYTABLE';
Общее количество блоков, выделенных для таблицы, это блоки + empty_blocks + num_freelist_blocks.
block - это количество блоков, которые фактически содержат данные.
Умножьте количество блоков на используемый размер блока (обычно 8 КБ), чтобы получить занятое пространство - например. 17 блоков x 8KB = 136 КБ.
Чтобы сделать это для всех таблиц в схеме сразу:
begin
dbms_stats.gather_schema_stats ('MYSCHEMA');
end;
/
select table_name, blocks, empty_blocks, num_freelist_blocks
from user_tables;
Примечание. Изменения, внесенные выше, после прочтения этого потока AskTom
Я изменил WW-запрос, чтобы предоставить более подробную информацию:
SELECT * FROM (
SELECT
owner, object_name, object_type, table_name, ROUND(bytes)/1024/1024 AS meg,
tablespace_name, extents, initial_extent,
ROUND(Sum(bytes/1024/1024) OVER (PARTITION BY table_name)) AS total_table_meg
FROM (
-- Tables
SELECT owner, segment_name AS object_name, 'TABLE' AS object_type,
segment_name AS table_name, bytes,
tablespace_name, extents, initial_extent
FROM dba_segments
WHERE segment_type IN ('TABLE', 'TABLE PARTITION', 'TABLE SUBPARTITION')
UNION ALL
-- Indexes
SELECT i.owner, i.index_name AS object_name, 'INDEX' AS object_type,
i.table_name, s.bytes,
s.tablespace_name, s.extents, s.initial_extent
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')
-- LOB Segments
UNION ALL
SELECT l.owner, l.column_name AS object_name, 'LOB_COLUMN' AS object_type,
l.table_name, s.bytes,
s.tablespace_name, s.extents, s.initial_extent
FROM dba_lobs l, dba_segments s
WHERE s.segment_name = l.segment_name
AND s.owner = l.owner
AND s.segment_type = 'LOBSEGMENT'
-- LOB Indexes
UNION ALL
SELECT l.owner, l.column_name AS object_name, 'LOB_INDEX' AS object_type,
l.table_name, s.bytes,
s.tablespace_name, s.extents, s.initial_extent
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 = UPPER('&owner')
)
WHERE total_table_meg > 10
ORDER BY total_table_meg DESC, meg DESC
/
Для подсегментированных таблиц и индексов мы можем использовать следующий запрос
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
;
IIRC нужны таблицы DBA_TABLES, DBA_EXTENTS или DBA_SEGMENTS и DBA_DATA_FILES. Существуют также версии USER_ и ALL_ для этих таблиц, которые вы можете видеть, если у вас нет административных разрешений на машине.
Вот вариант ответа WWs, он включает в себя разделы и подразделы, как предлагали другие выше, плюс столбец для отображения TYPE: Table/Index/LOB и т.д.
SELECT
owner, "Type", table_name "Name", TRUNC(sum(bytes)/1024/1024) Meg
FROM
( SELECT segment_name table_name, owner, bytes, 'Table' as "Type"
FROM dba_segments
WHERE segment_type in ('TABLE','TABLE PARTITION','TABLE SUBPARTITION')
UNION ALL
SELECT i.table_name, i.owner, s.bytes, 'Index' as "Type"
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, 'LOB' as "Type"
FROM dba_lobs l, dba_segments s
WHERE s.segment_name = l.segment_name
AND s.owner = l.owner
AND s.segment_type IN ('LOBSEGMENT','LOB PARTITION','LOB SUBPARTITION')
UNION ALL
SELECT l.table_name, l.owner, s.bytes, 'LOB Index' as "Type"
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, "Type"
HAVING SUM(bytes)/1024/1024 > 10 /* Ignore really small tables */
ORDER BY SUM(bytes) desc;
select segment_name,segment_type,bytes/1024/1024 MB
from dba_segments
where segment_name='TABLENAME' and owner ='OWNERNAME' order by mb desc;
Коррекция для секционированных таблиц:
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 in ('LOBSEGMENT', 'LOB PARTITION', 'LOB SUBPARTITION') 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 ;
Зависит от того, что вы подразумеваете под "размером таблицы". Таблица не относится к определенному файлу в файловой системе. Таблица будет находиться в табличном пространстве (возможно, несколько табличных пространств, если они разделены, и, возможно, несколько табличных пространств, если вы также хотите учесть индексы в таблице). В табличном пространстве часто есть несколько таблиц и может распространяться по нескольким файлам.
Если вы оцениваете, сколько места вам понадобится для роста в будущем, то avg_row_len, умноженное на количество строк в таблице (или количество строк, которые вы ожидаете в таблице), будет хорошим руководством. Но Oracle оставит свободное пространство на каждом блоке, отчасти для того, чтобы строки могли "расти", если они обновлены, отчасти потому, что может оказаться невозможным установить еще одну целую строку на этом блоке (например, блок 8K будет соответствовать только двум строкам из 3K, хотя это было бы крайним примером, поскольку 3K намного больше, чем большинство размеров строк). Поэтому BLOCKS (в USER_TABLES) может быть лучшим руководством.
Но если бы у вас было 200 000 строк в таблице, удалил бы половину из них, тогда таблица все равно "владела" тем же числом блоков. Он не выделяет их для использования других таблиц. Кроме того, блоки не добавляются в таблицу отдельно, а в группах, называемых "степенью". Таким образом, в таблице обычно будет EMPTY_BLOCKS (также в USER_TABLES).
Я изменил запрос, чтобы получить размер схемы для табличного пространства.
SELECT owner,
tablespace_name,
TRUNC (SUM (bytes) / 1024 / 1024) Meg,
ROUND (ratio_to_report (SUM (bytes)) OVER () * 100) Percent
FROM (SELECT tablespace_name, owner, bytes
FROM dba_segments
WHERE segment_type IN
('TABLE', 'TABLE PARTITION', 'TABLE SUBPARTITION')
UNION ALL
SELECT i.tablespace_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.tablespace_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 IN ('LOBSEGMENT', 'LOB PARTITION')
UNION ALL
SELECT l.tablespace_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 owner, tablespace_name
--HAVING SUM(bytes)/1024/1024 > 10 /* Ignore really small tables */
ORDER BY tablespace_name -- desc
;
есть еще один параметр, который позволяет получить размер "выбрать" с помощью объединений, а также размер таблицы в качестве опции
-- 1
EXPLAIN PLAN
FOR
SELECT
Scheme.Table_name.table_column1 AS "column1",
Scheme.Table_name.table_column2 AS "column2",
Scheme.Table_name.table_column3 AS "column3",
FROM Scheme.Table_name
WHERE ;
SELECT * FROM TABLE (DBMS_XPLAN.display);
Я нашел это немного более точным:
SELECT
owner, table_name, TRUNC(sum(bytes)/1024/1024/1024) GB
FROM
(SELECT segment_name table_name, owner, bytes
FROM dba_segments
WHERE segment_type in ('TABLE','TABLE PARTITION')
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')
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 IN ('LOBSEGMENT','LOB PARTITION')
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
select segment_name as tablename, sum(bytes/ (1024 * 1024 * 1024)) as tablesize_in_GB
From dba_segments /* if looking at tables not owned by you else use user_segments */
where segment_name = 'TABLE_WHOSE_SIZE_I_WANT_TO_KNOW'
and OWNER = 'WHO OWNS THAT TABLE' /* if user_segments is used delete this line */
group by segment_name ;
Простой выбор, который возвращает необработанные размеры таблиц на основе размера блока, также включает размер с индексом
выберите table_name, (nvl (( выберите сумму (блоки) из dba_indexes a, dba_segments b где a.index_name = b.segment_name и a.table_name = dba_tables.table_name ), 0) + блоков) * 8192/1024 TotalSize, blocks * 8 tableSize от dba_tables порядок на 3
Я имею тот же вариант, что и последние, которые вычисляют сегменты табличных данных, индексов таблиц и полей blob:
CREATE OR REPLACE FUNCTION
SYS.RAZMER_TABLICY_RAW(pNazvanie in varchar, pOwner in varchar2)
return number
is
val number(16);
sz number(16);
begin
sz := 0;
--Calculate size of table data segments
select
sum(t.bytes) into val
from
sys.dba_segments t
where
t.segment_name = upper(pNazvanie)
and
t.owner = upper(pOwner);
sz := sz + nvl(val,0);
--Calculate size of table indexes segments
select
sum(s.bytes) into val
from
all_indexes t
inner join
dba_segments s
on
t.index_name = s.segment_name
where
t.table_name = upper(pNazvanie)
and
t.owner = upper(pOwner);
sz := sz + nvl(val,0);
--Calculate size of table blob segments
select
sum(s.bytes) into val
from
all_lobs t
inner join
dba_segments s on t.segment_name = s.segment_name
where
t.table_name = upper(pNazvanie)
and
t.owner = upper(pOwner);
sz := sz + nvl(val,0);
return sz;
end razmer_tablicy_raw;