Посмотреть размер базы oracle
Привыкнув к MSSQL (и потенциально испорченный им), мне интересно, как я могу достичь размера таблиц в Oracle 10g. Я погуглил, поэтому теперь понимаю, что у меня может быть не такой простой вариант, как sp_spaceused. Тем не менее потенциальные ответы, которые я получил, в большинстве случаев устарели или не работают. Вероятно, потому что я не администратор баз данных по схеме, с которой работаю.
Есть ли у кого-нибудь решения или рекомендации?
если процедура выдачи ответа испорчена, тогда возьмите ответы, которые вы получили отсюда, и оберните их в процедуру и назовите ее . dun dun duh . sp_spaceused. В этом действительно мало волшебства. @MarkBrady Может быть, и не магия, но требуется тонна тайных знаний.Возможно, вам будет интересен этот запрос. Он сообщает вам, сколько места выделено для каждой таблицы, с учетом индексов и любых больших объектов в таблице. Часто вас интересует, «Сколько места занимает таблица заказа на закупку, включая любые индексы», а не только сама таблица. Вы всегда можете вникнуть в детали. Обратите внимание, что для этого требуется доступ к представлениям DBA_ *.
Обратите внимание, что в этом ответе учитываются сегменты, при этом не проводится различие между пространством, которое используется в настоящее время, и пространством, которое использовалось ранее. Очевидно, что как только сегмент назначается таблице, он всегда назначается таблице, даже если пространство освобождено. Смотрите здесь . Я полагаю, вам нужно спуститься до уровня экстента, чтобы увидеть, сколько места фактически используется ?Эти статистические данные могут быть 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 . Если ваша статистика неточна, вероятно, для этого есть причина, и вы не хотите нарушать статус-кво.
Во-вторых, наиболее близким эквивалентом sp_spaceused процедуры SQL Server, вероятно, является DBMS_SPACE пакет Oracle . У Тома Кайта есть хорошая show_space процедура, которая обеспечивает простой интерфейс для этого пакета и выводит на печать информацию, аналогичную той, что sp_spaceused выводится.
Сначала соберите статистику оптимизатора в таблице (если вы еще этого не сделали):
ВНИМАНИЕ: как сказал Джастин в своем ответе, сбор статистики оптимизатора влияет на оптимизацию запросов и не должен выполняться без должной осторожности и внимания !
Затем найдите количество блоков, занятых таблицей, из сгенерированной статистики:
Общее количество блоков, выделенных для таблицы, составляет блоки + empty_blocks + num_freelist_blocks.
блоки - это количество блоков, которые фактически содержат данные.
Умножьте количество блоков на размер используемого блока (обычно 8 КБ), чтобы получить занимаемое пространство - например, 17 блоков x 8 КБ = 136 КБ.
Чтобы сделать это сразу для всех таблиц в схеме:
Примечание: изменения, внесенные в приведенное выше после прочтения этой ветки AskTom
Я изменил запрос WW, чтобы предоставить более подробную информацию:
Для суб-секционированных таблиц и индексов мы можем использовать следующий запрос
IIRC, вам нужны таблицы DBA_TABLES, DBA_EXTENTS или DBA_SEGMENTS и DBA_DATA_FILES. Существуют также версии USER_ и ALL_ для таблиц, которые вы можете увидеть, если у вас нет прав администратора на машине.
Вот вариант ответа WW, он включает в себя разделы и подразделы, как предлагали другие выше, плюс столбец для отображения ТИПА: Таблица / Индекс / LOB и т. Д.
Я изменил запрос, чтобы получить размер схемы для каждого табличного пространства.
Зависит от того, что вы подразумеваете под «размером стола». Таблица не относится к конкретному файлу в файловой системе. Таблица будет находиться в табличном пространстве (возможно, в нескольких табличных пространствах, если она разбита на разделы, и, возможно, в нескольких табличных пространствах, если вы также хотите учитывать индексы в таблице). Табличное пространство часто будет содержать несколько таблиц и может быть распределено по нескольким файлам.
Если вы оцениваете, сколько места вам понадобится для будущего роста таблицы, тогда avg_row_len, умноженный на количество строк в таблице (или количество строк, которые вы ожидаете в таблице), будет хорошим ориентиром. Но Oracle оставит некоторое пространство свободным в каждом блоке, отчасти для того, чтобы строки могли `` расти '' при их обновлении, отчасти потому, что может оказаться невозможным уместить еще одну целую строку в этом блоке (например, блок 8K поместится только в 2 строки 3 КБ, хотя это был бы крайний пример, поскольку 3 КБ намного больше, чем большинство размеров строк). Так что BLOCKS (в USER_TABLES) может быть лучшим руководством.
Но если бы у вас было 200000 строк в таблице, вы удалили половину из них, тогда таблица все равно «владеет» тем же количеством блоков. Это не освобождает их для использования другими таблицами. Кроме того, блоки добавляются в таблицу не по отдельности, а в группах, называемых «экстентом». Таким образом, в таблице обычно будет EMPTY_BLOCKS (также в USER_TABLES).
Способ просмотра размера табличного пространства Oracle
Метод просмотра размера табличного пространства Oracle должен быть тем знанием, которое нам всем необходимо освоить. Ниже приводится подробное введение в метод просмотра размера табличного пространства Oracle для вашей справки.
В управлении базой данных проблема с диском является проблемой, с которой сталкивается администратор баз данных, и эта проблема является относительно распространенной.
--1 Просмотреть размер табличного пространства Oracle - процент использования.
«Сумма МБ» обозначает общее дисковое пространство, занимаемое всеми файлами данных в табличном пространстве в операционной системе.
Например: тестовое табличное пространство содержит 2 файла данных, datafile1 - 300 МБ, datafile2 - 400 МБ, тогда «Сумма МБ» тестового табличного пространства составляет 700 МБ.
«Userd MB» указывает, сколько табличного пространства было использовано
«Свободный МБ» указывает, сколько табличного пространства осталось
«Percent_user» указывает используемый процент
--2 Например, из 1, чтобы увидеть, что процент использования табличного пространства MLOG_NORM_SPACE достиг более 90%, вы можете проверить, сколько файлов данных содержится в табличном пространстве, автоматически ли каждый файл данных расширяется, а также максимальное значение, которое может быть автоматически расширено.
--3 Например, текущий размер табличного пространства MLOG_NORM_SPACE составляет 19 ГБ, но максимальный размер каждого файла данных составляет всего 20 ГБ. Файл данных собирается заполниться, и файл данных табличного пространства может быть увеличен.
Используйте команду df -g в операционной системе UNIX и Linux (проверьте доступное дисковое пространство)
Получите инструкцию для создания табличного пространства:
--4 Убедитесь, что на диске достаточно места, добавьте файл данных
--5 Убедитесь, что файл данных был добавлен
--6 Если вы удалите файл данных табличного пространства, выполните следующие действия:
Совсем недавно на работе стало необходимым контролировать рост размера базы данных Oracle. Связано это было с тем, что места осталось не так много, что -то около 100 Гб, а в двух компаниях (компании крупные, страховые) базы стремительно растут.
Для начала мы создадим таблицу DB_TABLESPACE_SIZE, в которой будем хранить данные о каждом дне.
-- Create table
create table DB_TABLESPACE_SIZE
(
DB_TABLESPACE_NAME VARCHAR2(30),
TIME_SNAPSHOT DATE ,
FREE_SPACE NUMBER(20),
MAX_LIMIT NUMBER(20),
CURRENT_SIZE NUMBER(20),
AUTOEXTEND_ON NUMBER(20),
AVAILABLE_SIZE NUMBER(20),
USED_FOR_DATA NUMBER(20),
UNUSED_FOR_DATA NUMBER(20),
FILES_COUNT NUMBER(5),
MIN_UNALLOCATED NUMBER(20),
MAX_UNALLOCATED NUMBER(20),
MIN_AVAILABLE NUMBER(20),
MAX_AVAILABLE NUMBER(20),
MIN_USED NUMBER(20),
MAX_USED NUMBER(20),
MIN_UNUSED NUMBER(20),
MAX_UNUSED NUMBER(20)
)
tablespace USERS
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
-- Add comments to the columns
comment on column DB_TABLESPACE_SIZE.FREE_SPACE
is 'размер, освобожденный внутри файлов табличного пространства (дырки). UNUSED, в отличие от него ― это место, которое никогда не было занято.' ;
comment on column DB_TABLESPACE_SIZE.MAX_LIMIT
is 'предельно возможный размер. (с учетом AUTOEXTEND)' ;
comment on column DB_TABLESPACE_SIZE.CURRENT_SIZE
is 'текущий размер' ;
comment on column DB_TABLESPACE_SIZE.AVAILABLE_SIZE
is 'размер, доступный для новых данных UNUSED+autoextend_on' ;
comment on column DB_TABLESPACE_SIZE.USED_FOR_DATA
is 'размер, занятый под данные. (нечто вроде "high watermark")' ;
comment on column DB_TABLESPACE_SIZE.UNUSED_FOR_DATA
is 'размер, не занятый данными. Т.е. когда файл расширился, но расширенное место еще не успело заполниться данными, появляется UNUSED. ' ;
* This source code was highlighted with Source Code Highlighter .
Далее создадим вью current_tablespace_size там будем хранить текущие данные о табличных пространствах и суммированные данные.
create or replace view current_tablespace_size
(db_tablespace_name, time_snapshot, max limit on gb, current_size on gb, autoextend_on on gb, available_size on gb, used_for_data on gb, unused_for_data, free_space, files_count, min_unallocated, max_unallocated, min_available, max_available, min_used, max_used, min_unused, max_unused)
as
select DB_TABLESPACE_NAME, TIME_SNAPSHOT,
MAX_LIMIT/1024/1024/1024 ,
CURRENT_SIZE/1024/1024/1024,
AUTOEXTEND_ON/1024/1024/1024,
AVAILABLE_SIZE/1024/1024/1024 "AVAILABLE_SIZE",
USED_FOR_DATA/1024/1024/1024 "USED_FOR_DATA",
UNUSED_FOR_DATA/1024/1024 "UNUSED_FOR_DATA",
FREE_SPACE/1024/1024 "FREE_SPACE", "FILES_COUNT",
MIN_UNALLOCATED/1024/1024 "MIN_UNALLOCATED",
MAX_UNALLOCATED/1024/1024 "MAX_UNALLOCATED",
MIN_AVAILABLE/1024/1024 "MIN_AVAILABLE",
MAX_AVAILABLE/1024/1024 "MAX_AVAILABLE",
MIN_USED/1024/1024 "MIN_USED",
MAX_USED/1024/1024 "MAX_USED",
MIN_UNUSED/1024/1024 "MIN_UNUSED",
MAX_UNUSED/1024/1024 "MAX_UNUSED"
-- sum (CURRENT_SIZE)
from db_TABLESPACE_SIZE
where TIME_SNAPSHOT = ( select MAX (TIME_SNAPSHOT) from db_TABLESPACE_SIZE)
union select 'TOTAL' ,( select max (s.time_snapshot) from db_TABLESPACE_SIZE s ), null ,
( select sum (s.current_size/1024/1024/1024) from db_TABLESPACE_SIZE s where s.time_snapshot=( select max (s.time_snapshot) from db_TABLESPACE_SIZE s )), null , null , null , null , null , null , null , null , null , null , null , null , null , null from dual;
* This source code was highlighted with Source Code Highlighter .
Далее создадим таблицу db_tablespace_size_by_date для хранения каждодневного изменения размера базы данных
-- Create table
create table DB_TABLESPACE_SIZE_BY_DATE
(
DB_TABLESPACE_NAME VARCHAR2(30),
TIME_SNAPSHOT DATE ,
DEFF_SIZE NUMBER
)
tablespace USERS
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
* This source code was highlighted with Source Code Highlighter .
Ну и последним действием напишем job для сбора информации каждый день и заполнения наших таблиц.
begin
sys.dbms_job.submit(job => :job,
what => 'insert into db_TABLESPACE_SIZE( "DB_TABLESPACE_NAME", "TIME_SNAPSHOT", "MAX_LIMIT", "CURRENT_SIZE",
"AUTOEXTEND_ON", "AVAILABLE_SIZE", "USED_FOR_DATA", "UNUSED_FOR_DATA", "FREE_SPACE", "FILES_COUNT","MIN_UNALLOCATED", "MAX_UNALLOCATED",
"MIN_AVAILABLE", "MAX_AVAILABLE","MIN_USED", "MAX_USED", "MIN_UNUSED", "MAX_UNUSED")
select F."TABLESPACE_NAME",
F."TIME",
F."LIMIT",
F."SIZE",
F."UNALLOCATED",
F."AVAILABLE",
F."USED",
F."UNUSED",
nvl(S.TOTAL_BYTES, 0) "FREE_SPACE",
F.FILES,
F.MIN_UNALLOCATED,
F.MAX_UNALLOCATED,
F.MIN_AVAILABLE,
F.MAX_AVAILABLE,
F.MIN_USED,
F.MAX_USED,
F.MIN_UNUSED,
F.MAX_UNUSED
from
( select tablespace_name,
sysdate "TIME",
SUM( CASE WHEN AUTOEXTENSIBLE=' 'YES' ' THEN MAXBYTES ELSE BYTES END ) "LIMIT",
SUM( BYTES ) "SIZE",
SUM( CASE WHEN AUTOEXTENSIBLE=' 'YES' ' THEN MAXBYTES - BYTES ELSE 0 END ) "UNALLOCATED",
SUM( CASE WHEN AUTOEXTENSIBLE=' 'YES' ' THEN MAXBYTES ELSE BYTES END - USER_BYTES ) "AVAILABLE",
SUM( USER_BYTES ) "USED",
SUM( BYTES - USER_BYTES ) "UNUSED",
COUNT( FILE_NAME ) "FILES",
MIN( CASE WHEN AUTOEXTENSIBLE=' 'YES' ' THEN MAXBYTES - BYTES ELSE null END ) "MIN_UNALLOCATED",
MAX( CASE WHEN AUTOEXTENSIBLE=' 'YES' ' THEN MAXBYTES ELSE BYTES END - BYTES ) "MAX_UNALLOCATED",
MIN( CASE WHEN AUTOEXTENSIBLE=' 'YES' ' THEN MAXBYTES ELSE BYTES END - USER_BYTES) "MIN_AVAILABLE",
MAX( CASE WHEN AUTOEXTENSIBLE=' 'YES' ' THEN MAXBYTES ELSE BYTES END - USER_BYTES) "MAX_AVAILABLE",
MIN( USER_BYTES ) "MIN_USED",
MAX( USER_BYTES ) "MAX_USED",
MIN( BYTES - USER_BYTES ) "MIN_UNUSED",
MAX( BYTES - USER_BYTES ) "MAX_UNUSED"
from dba_data_files
group by tablespace_name
) F left join dba_free_space_coalesced S on (F.TABLESPACE_NAME = S.TABLESPACE_NAME);
insert into db_tablespace_size_by_date ("DB_TABLESPACE_NAME","TIME_SNAPSHOT","DEFF_SIZE")
SELECT nvl(t1.db_tablespace_name, ' 'TOTAL' '),
MAX(t1.time_snapshot),
(SUM(t1.current_size / 1024 / 1024 / 1024) -
SUM(t1.free_space / 1024 / 1024 / 1024)) -
(SUM(t3.current_size / 1024 / 1024 / 1024) -
SUM(t3.free_space / 1024 / 1024 / 1024))
FROM db_tablespace_size t1, db_tablespace_size t3
WHERE t1.time_snapshot =
(SELECT MAX(t2.time_snapshot)
FROM db_tablespace_size t2
WHERE trunc(t2.time_snapshot) = trunc(SYSDATE))
AND t3.time_snapshot =
(SELECT MIN(t2.time_snapshot)
FROM db_tablespace_size t2
WHERE trunc(t2.time_snapshot) = trunc(SYSDATE-1))
AND t1.db_tablespace_name = t3.db_tablespace_name
GROUP BY CUBE(t1.db_tablespace_name);
commit;' ,
next_date => to_date( '15-02-2012 05:00:00' , 'dd-mm-yyyy hh24:mi:ss' ),
interval => 'trunc(SYSDATE,' 'hh' ')+1' );
commit ;
end ;
/
* This source code was highlighted with Source Code Highlighter .
Немного о коде: Таблица db_TABLESPACE_SIZE заполняется из dba_data_files и dba_free_space_coalesced. В первой хранятся данные о размерах файлов табличных пространств, во второй размер освобожденный внутри файлов табличного пространства.
В таблицу db_tablespace_size_by_date ложиться разница между size и free_size, то есть, по сути, получаем фактическое занятое место.
Вот и все, теперь каждый день собирается информация о размеры базы данных и об изменениях.
Пакет DBMS_SPACE удобен для проверки того, сколько места занято и сколько свободного пространства осталось в различных сегментах, таких как сегменты таблиц, индексов и кластеров. Напомним, что представление словаря данных DBA_FREE_SPACE позволяет находить информацию о свободном месте в табличных пространствах и файлах данных, но не в объектах базы данных. Если пакет DBMS_SPACE не используется, сложно будет узнать, сколько свободного места есть в сегментах, выделенных различным объектам базы данных. Пакет DBMS_SPACE позволяет ответить на следующие вопросы:
- Сколько свободного пространства я могу использовать перед тем, как понадобится следующий экстент?
- Сколько блоков данных находятся выше маркера максимального уровня заполнения (high-water mark — HWM)?
Представления словаря данных DBA_EXTENTS и DBA_SEGMENTS предоставляют массу информации о месте, выделенном объектам, таким как таблицы и индексы, но вы не сможете узнать из них о том, сколько пространства занято и сколько осталось свободного. Если проанализировать таблицы, то столбец BLOCKS даст HWM — максимальный размер, которого достигала таблица. Однако если таблица подвергалась большому количеству операций вставки и удаления, то HWM не будет показательным индикатором реального использованного пространства. Пакет DBMS_SPACE идеален для нахождения использованного и свободного пространства, оставшегося в объектах.
Пакет DBMS_SPACE включает в себя три основных процедуры: процедура UNUSED_SPACE даст информацию о неиспользованном пространстве в сегменте объекта,процедура FREE_BLOCKS — информацию о количестве свободных блоков в сегменте, а процедура SPACE_USED — подробности об использованном пространстве в блоках.
Давайте внимательнее рассмотрим процедуру UNUSED_SPACE и посмотрим, как ее применять для получения детальной информации о неиспользованном пространстве.Процедура имеет три входных (IN) параметра (четвертый — параметр по умолчанию) и семь выходных (OUT) параметров. В листинге ниже показан вывод, полученный при выполнении процедуры UNUSED_SPACE.
Читайте также: