Посмотреть размер таблицы в oracle
Известно, что память в табличных пространствах выдается объектам, хранимым в БД, экстентами. Место, которое занимает объект (в типичном случае - таблица или индекс) на диске, можно определить из системной таблицы USER_EXTENTS, содержащей перечень всех экстентов всех сегментов пользователя. Однако картина, выдаваемая подобным запросом, может оказаться слишком общей. За списком экстентов таблицы может скрываться:
- неполнота последнего экстента
- отсутствие полных блоков в экстенте
DBA_TABLES
Представление DBA_TABLES содержит информацию обо всех реляционных таблицах базы данных. Представление DBA_TABLES — основной справочник для нахождения информации о хранении, количестве строк в таблице, состоянии протоколирования, информации буферного пула и многих других деталях. Ниже приведен простой пример запроса представления DBA_TABLES:
На заметку! Представление DBA_ALL_TABLES содержит информацию обо всех объектных и реляционных таблицах в базе данных, в то время как представление DBA_TABLES ограничено только реляционными таблицами.
Представление DBA_TABLES служит для нахождения таких вещей, как включено ли сжатие и отслеживание зависимостей на уровне строки, и была ли таблица уничтожена и помещена в корзину (Recycle Bin).
TRUNCATE / DELETE / DROP или как очистить таблицу
Вывести объем, на который можно уменьшить ТП:
dba_data_files.file_id,
dba_data_files.tablespace_name,
ceil((nvl(hwm, 1) * db_block_size) / 1024 / 1024) smallest,
ceil(blocks * db_block_size / 1024 / 1024) currsize,
ceil(blocks * db_block_size / 1024 / 1024) -
ceil((nvl(hwm, 1) * db_block_size) / 1024 / 1024) savings
from dba_data_files,
(select file_id,
max(block_id + blocks - 1) hwm
from dba_extents
group by file_id) b,
(select value db_block_size from v$parameter where name = 'db_block_size') c
where dba_data_files.file_id = b.file_id(+);
- Получить ссылку
- Электронная почта
- Другие приложения
DBA_PART_TABLES
Представление DBA_PART_TABLES содержит информацию о типе схемы раздела и прочих параметрах хранения разделов и подразделов. Узнать тип каждого раздела каждой секционированной таблицы можно с помощью следующего запроса:
DBA_TAB_COLUMNS
Предположим, вы нужно узнать среднюю длину каждой строки таблицы или значение по умолчанию каждого столбца (если таковое есть). Представление DBA_TAB_COLUMNS — отличный способ быстро получить всю детальную информацию о столбцах таблиц схемы, как показано в листинге ниже.
DBA_OBJECTS
Представление DBA_OBJECTS содержит информацию обо всех объектах базы данных, включая таблицы, индексы, пакеты, процедуры, функции, измерения, материализованные представления, планы ресурсов, типы, последовательности, синонимы, триггеры, представления и разделы таблиц (оно же секционирование). Как несложно догадаться, это представления удобно, когда нужно знать общую информацию относительно любого объекта базы данных. В листинге ниже показан запрос, предназначенный для нахождения времени создания и времени последней модификации объекта (LAST_DDL_TIME). Этот тип запроса поможет идентифицировать время модификации определенного объекта, что часто используется в процессе аудита.
17 Answers 17
You might be interested in this query. It tells you how much space is allocated for each table taking into account the indexes and any LOBs on the table. Often you are interested to know "How much spaces the the Purchase Order table take, including any indexes" rather than just the table itself. You can always delve into the details. Note that this requires access to the DBA_* views.
Note that this answer counts segments, which doesn't distinguish between space that is currently in use vs. space that was previously in use. Apparently, once a segment is assigned to a table, it's always assigned to a table, even if the space is freed. See here. I guess you have to go down to the extent level to see how much space is actually used?
Note: These are estimates, made more accurate with gather statistics:
These statistics may be null ( num_rows , avg_row_len ), you need to make some analysis before via the following statement ANALYZE TABLE your_table COMPUTE STATISTICS
First off, I would generally caution that gathering table statistics in order to do space analysis is a potentially dangerous thing to do. Gathering statistics may change query plans, particularly if the DBA has configured a statistics gathering job that uses non-default parameters that your call is not using, and will cause Oracle to re-parse queries that utilize the table in question which can be a performance hit. If the DBA has intentionally left some tables without statistics (common if your OPTIMIZER_MODE is CHOOSE), gathering statistics can cause Oracle to stop using the rule-based optimizer and start using the cost-based optimizer for a set of queries which can be a major performance headache if it is done unexpectedly in production. If your statistics are accurate, you can query USER_TABLES (or ALL_TABLES or DBA_TABLES ) directly without calling GATHER_TABLE_STATS . If your statistics are not accurate, there is probably a reason for that and you don't want to disturb the status quo.
Second, the closest equivalent to the SQL Server sp_spaceused procedure is likely Oracle's DBMS_SPACE package. Tom Kyte has a nice show_space procedure that provides a simple interface to this package and prints out information similar to what sp_spaceused prints out.
First, gather optimiser stats on the table (if you haven't already):
WARNING: As Justin says in his answer, gathering optimiser stats affects query optimisation and should not be done without due care and consideration!
Then find the number of blocks occupied by the table from the generated stats:
The total number of blocks allocated to the table is blocks + empty_blocks + num_freelist_blocks.
blocks is the number of blocks that actually contain data.
Multiply the number of blocks by the block size in use (usually 8KB) to get the space consumed - e.g. 17 blocks x 8KB = 136KB.
To do this for all tables in a schema at once:
Note: Changes made to the above after reading this AskTom thread
I modified the WW's query to provide more detailed information:
IIRC the tables you need are DBA_TABLES, DBA_EXTENTS or DBA_SEGMENTS and DBA_DATA_FILES. There are also USER_ and ALL_ versions of these for tables you can see if you don't have administration permissions on the machine.
For sub partitioned tables and indexes we can use the following query
Heres a variant on WWs answer, it includes partitions and sub-partitions as others above have suggested, plus a column to show the TYPE: Table/Index/LOB etc
I modified the query to get the schema size per tablespace ..
Depends what you mean by "table's size". A table doesn't relate to a specific file on the file system. A table will reside on a tablespace (possibly multiple tablespaces if it is partitioned, and possibly multiple tablespaces if you also want to take into account indexes on the table). A tablespace will often have multiple tables in it, and may be spread across multiple files.
If you are estimating how much space you'll need for the table's future growth, then avg_row_len multiplied by the number of rows in the table (or number of rows you expect in the table) will be a good guide. But Oracle will leave some space free on each block, partly to allow for rows to 'grow' if they are updated, partly because it may not be possible to fit another entire row on that block (eg an 8K block would only fit 2 rows of 3K, though that would be an extreme example as 3K is a lot bigger than most row sizes). So BLOCKS (in USER_TABLES) might be a better guide.
But if you had 200,000 rows in a table, deleted half of them, then the table would still 'own' the same number of blocks. It doesn't release them up for other tables to use. Also, blocks are not added to a table individually, but in groups called an 'extent'. So there are generally going to be EMPTY_BLOCKS (also in USER_TABLES) in a table.
dba_data_files.file_id,
dba_data_files.tablespace_name,
ceil((nvl(hwm, 1) * db_block_size) / 1024 / 1024) smallest,
ceil(blocks * db_block_size / 1024 / 1024) currsize,
ceil(blocks * db_block_size / 1024 / 1024) -
ceil((nvl(hwm, 1) * db_block_size) / 1024 / 1024) savings
from dba_data_files,
(select file_id,
max(block_id + blocks - 1) hwm
from dba_extents
group by file_id) b,
(select value db_block_size from v$parameter where name = 'db_block_size') c
where dba_data_files.file_id = b.file_id(+);
INDEX_STATS
Представление INDEX_STATS полезно для того, чтобы узнать, насколько эффективно индекс использует свое пространство. Крупные индексы имеют тенденцию со временем становиться несбалансированными, если происходит много удалений данных таблицы (а, следовательно, и индекса). Ваша цель — не упускать из виду эти крупные индексы,чтобы сохранять их сбалансированными.
Обратите внимание, что представление INDEX_STATS наполняется, только когда таблица подвергается анализу с помощью команды ANALYZE, как показано ниже:
Запрос из листинга ниже, использующий представление INDEX_STATS, помогает определить необходимость в перестройке индекса. Чтобы определить, следует ли перестраивать индекс, в запросе необходимо сосредоточиться на перечисленных ниже столбцах представления INDEX_STATS.
DBA_VIEWS
Как известно, представления — это результаты запросов к некоторым таблицам базы данных. Представление словаря данных DBA_VIEWS позволяет увидеть SQL-запросы, лежащие в основе представлений. В листинге ниже показано, как получить текст представления OS_CUSTOMERS, принадлежащего пользователю oe.
Совет. Чтобы обеспечить полное отображение текста при использовании представления DBA_VIEWS, установите большое значение переменной long (например, SET LONG 2000). В противном случае вы увидите лишь несколько первых строк определения представления.
Комментарии
DBA_INDEXES
Представление словаря DBA_INDEXES служит для того, чтобы узнать все необходимое об индексах в базе данных, включая имя индекса, его тип, таблицу и табличное пространство, к которому он относится. Определенные столбцы, наподобие BLEVEL (сообщает уровень B-дерева индекса) и DISTINCT_KEYS (количество разных значений ключа индекс), наполняются, только если собрана статистика по индексу с использованием пакета DBMS_STATS.
DBA_IND_COLUMNS
Представления DBA_IND_COLUMNS по структуре подобно представлению DBA_CONS_COLUMNS и содержит информацию обо всех проиндексированных столбцах каждой таблицы. Эта информация важна при настройке производительности, когда вы замечаете,что запрос использует индекс, но вы не знаете точно, на каких столбцах этот индекс определен. Запрос, приведенный в листинге ниже, показывает, что таблица имеет индексы, определенные на неверных столбцах.
Совет. Взглянув на столбец INDEX_NAME, можно легко идентифицировать составные ключи. Если одно и то же вхождение INDEX_NAME появляется больше одного раза, значит, это составной ключ; и столбцы, являющиеся его частью, показаны в столбце COLUMN_NAME. Например,INVENTORY_PK — первичный ключ таблицы INVENTORIES, определенный на двух столбцах:PRODUCT_ID и WAREHOUSE_ID. Порядок столбцов в определении составного ключа можно узнать с помощью столбца COLUMN_POSITION.
Таблицы в Oracle. Количество записей, размер таблицы
-- **************************************************************** -- выводим количество записей по таблицам -- **************************************************************** -- Требуется выполнить сначала скрипт с Set , после чего отдельно выполнить Declare Set serveroutput on format wraped; Declare Cou Integer; Begin For Rec in (select a.Table_Name from all_tables a where (a.OWNER = '<Имя_схемы>') ) loop Execute immediate('Select count(*) from '||Rec.Table_name) Into Cou; -- ************************************* dbms_output.put_line(' <Имя_схемы>'||Rec.Table_Name||';'||Cou); End Loop; End; Для того чтобы узнать размер таблицы в БД, требуется выполнить скрипт: select segment_name table_name, ceil(sum(bytes) / 1024) table_size from dba_segments where owner = ' <Имя_схемы>' aИмя_схемы>
DBA_TAB_MODIFICATIONS
Представление DBA_TAB_MODIFICATIONS показывает все изменения DML в таблице,произошедшие с момента последнего сбора статистики по этой таблице. Вот запрос к этому представлению:
База данных не обновляет представление DBA_TAB_MODIFICATIONS в реальном времени. Следовательно, вы можете и не увидеть изменений в различных таблицах, немедленно отраженных в этом представлении.
DBA_TAB_PARTITIONS
Представление DBA_TAB_PARTITIONS подобно представлению DBA_TABLES, но содержит детальную информацию о разделах таблиц. Благодаря DBA_TAB_PARTITIONS, можно узнать имя раздела, его максимальные значения, информацию о хранении раздела,статистику по разделу, а также прочую информацию, которая доступна в представлении DBA_TABLES. В листинге ниже показан простой запрос, использующий представление DBA_TAB_PARTITIONS.
4 Answers 4
The following query can be used to detemine tablespace and other params:
For your case if you want to know the partition name and it's size just run this query:
One of the way is Using below sql queries
One way to check the tablespace size in oracle is to run this query
SELECT b.tablespace_name, tbs_size SizeGb, a.free_space FreeGb FROM ( SELECT tablespace_name, ROUND (SUM (bytes) / 1024 / 1024 / 1024, 2) AS free_space FROM dba_free_space GROUP BY tablespace_name) a, ( SELECT tablespace_name, SUM (bytes) / 1024 / 1024 / 1024 AS tbs_size FROM dba_data_files GROUP BY tablespace_name) b WHERE a.tablespace_name(+) = b.tablespace_name ORDER BY 1
Увеличить размер ТП. Добавить dbf:
Если не указать параметр MAXSIZE, то табличное пространство увеличивается до максимального размера датафайла = 32Гб, но не всегда, так как размер tablespace зависит от размера блока
ALTER TABLESPACE USERS ADD DATAFILE '/mnt/r02tb/app/oracle/oradata/ORACL11B/users51.dbf' SIZE 1000M AUTOEXTEND ON;
DBA_MVIEWS
Представление словаря DBA_MVIEWS сообщает все о материализованных представлениях в базе данных, в том числе информацию, включено ли для них средство переписывания запросов. В листинге ниже демонстрируется использования этого представления.
DBA_EXTERNAL_TABLES
Представление DBA_EXTERNAL_TABLES показывает подробности о любой внешней таблице в базе данных, включая их тип доступа, параметры доступа и информацию о каталоге.
Пакет DBMS_SPACE
Посмотреть длину списка свободных блоков (дальше будем считать, что для таблиц заведено по одному такому списку, что делается системой по умолчанию и представляет собой самый распространенный случай; а вообще-то, их можно заводить и больше) можно процедурой DBMS_SPACE.FREE_BLOCKS:
68 блоков - это большой список. Большие списки свободных блоков сами по себе не криминал. Но это индикатор того, что 'слева' от high watermark у вас могло образоваться много свободного места. Дальнейшее исследование можно проводить с помощью ANALYZE.
Узнать расположение самой верхней границы high watermark можно с помощью процедуры DBMS_SPACE.UNUSED_SPACE:
- DBMS_OUTPUT.PUT_LINE
- ('last_used_extent_file_id '||last_used_extent_file_id);
- DBMS_OUTPUT.PUT_LINE
- ('last_used_extent_block_id '||last_used_extent_block_id);
Видно, что справа от high watermark имеется 27 неиспользованных блоков. В нашем случае это чуть меньше трети размера всего сегмента с таблицей (96 блоков), то есть очень много. Сама отметка high watermark находится на пятом блоке последнего экстента в нашем сегменте, причем сам экстент находится в файле номер 3.
VNC Viewer. Использование буфера обмена между Linux и Windows
Проблема: При работе на сервере через VNC Viewer не получается копировать куски текста, кода. Поэтому приходится вводить вручную. Решение: Первое решение, которое пока нормально работает. (возможно нужно найти возможность включения какой-либо настройки, но это потом в других вариантах). Необходимо выполнить следующую команду в терминале: [oracle@dbserver38 bin]$ vncconfig -display :1 В результате откроется окно, в котором (при необходимости необходимо поставить чекбоксы) После это можно копировать текст Для удобства, запихал данную команду в sh-файл на рабочем столе - VNC_copy_file.sh
Существует несколько важных представлений словаря базы данных, которые можно использовать для нахождения детальной информации о любом из объектов базы данных, о которых говорилось в этой главе. Администраторы баз данных также интенсивно используют представления словаря данных, чтобы управлять различными объектами схемы. Здесь приводится краткий список важнейших представлений, часть из которых упоминалась выше. Полные данные о типах информации, которую можно получить от каждого из этих представлений, доступны по команде DESCRIBE (например, DESCRIBE DBA_CATALOG).
В этой статье блога будут описаны некоторые важные представления словаря данных, которые помогут управлять объектами, не хранящими данные (т.е. объектами, которые не относятся к таблицам и индексам). Ниже приведен список важнейших представлений словаря данных для просмотра объектов базы данных.
- DBA_SYNONYMS. Информация о синонимах базы данных.
- DBA_TRIGGERS. Информация о триггерах.
- DBA_SEQUENCES. Информация о созданных пользователем последовательностях.
- DBA_DB_LINKS. Информация о связях базы данных.
Как упоминалось ранее, представление DBA_OBJECTS предоставляет важную информацию обо всех перечисленных объектах, наряду с некоторыми другими типами объектов базы данных. Однако перечисленные представления содержат детальную информацию о каждом объекте, такую как исходный текст триггера, которую вы не получите из представления DBA_OBJECTS.
Управление такими объектами, как таблицы и представления, осуществляется ссылкой на представления словаря данных, наподобие DBA_TABLES и DBA_VIEWS. Существуют также отдельные представления для секционированных таблиц. Давайте рассмотрим ключевые представления словаря данных, относящиеся к таблицам и индексам.
Получение и анализ отчета DBMS_SPACE
Основная ценность процедур пакета DBMS_SPACE во-первых, в том, что они, как было сказано, дают информацию о заполнении табличного пространства, отсутствующую в словаре-справочнике, и, во-вторых, делают это быстро. Анализ заполненности экстентов, типа приведенного выше, разумно сделать регулярным, составив для этого специальный сценарий и организовав регулярный запуск этого скрипта (например, с помощью DBMS_JOB). Ниже приводится скрипт для DBMS_SPACE, который можно взять за основу. Он выдает справку для таблиц и индексов пользователя:
В этой тренировочной схеме SCOTT виден большой расход памяти впустую. Нужно помнить, что решение о реорганизации таблиц следует принимать с осторожностью: выбор разных размеров экстентов для разных объектов чреват фрагментацией табличного пространства. (Возникнет ли оно реально, зависит еще от характера использования таблиц). Увы, но это Сцилла и Харибда, которые предлагает Oracle.
Надеюсь, что приведенный выше скрипт послужит отправной точкой для вашего творчества. Например, он не выдает полезную в этом случае информацию о табличных пространствах, где происходят потери памяти. Кроме того, при большом числе объектов разумно составить сценарий, который бы выдавал, к примеру, 10 объектов с наиболее "пустым хвостом" и 10 объектов с наибольшей длиной списка свободных блоков.
I have a database with three tables. I need to move historic partitioned data to other schema Now that i´m planning to creatre the new "historic" tables. I don´t know how to measure the size of partition and subpartitions. Can u help me? please gimme some advices.
Thanxs in advance. Lou
You talk about tablespaces in your title but partitions and subpartitions in the text. Which one is it? And what have you tried? There's a lot of stuff available with a simple search, why didn't the basic stuff meet your needs?
@user2115594:dude you need to measure the tablespace size or each partition size in a particular table?
Yeah i´m sorry what i really need is to measure the subpartition size in my origin table. Each origin tables where created with partitions and subpartitions but they all insert data into 3 tablespaces. Next step is to create one tablespace for each subpartition
КБК. КВФО - Код вида финансового обеспечения (деятельности)
НПА: Приказ Минфина России от 01.12.2010 N 157н Письмо Минфина России от 18 января 2018 г. N 02-06-10/2715 В целях организации и ведения бухгалтерского учета, утверждения Рабочего плана счетов применяются следующие коды вида финансового обеспечения (деятельности): для государственных (муниципальных) учреждений, организаций, осуществляющих полномочия получателя бюджетных средств, финансовых органов соответствующих бюджетов и органов, осуществляющих их кассовое обслуживание: 1 - деятельность, осуществляемая за счет средств соответствующего бюджета бюджетной системы Российской Федерации (бюджетная деятельность); 2 - приносящая доход деятельность (собственные доходы учреждения); 3 - средства во временном распоряжении; 4 - субсидии на выполнение государственного (муниципального) задания; 5 - субсидии на иные цели; 6 - субсидии на цели осуществления капитальных вложений; 7 - средства по обязательному медицинскому страхованию; для отражения органами Федерального казн
Not the answer you're looking for? Browse other questions tagged oracle11g tablespace or ask your own question.
Related
Hot Network Questions
To subscribe to this RSS feed, copy and paste this URL into your RSS reader.
Site design / logo © 2022 Stack Exchange Inc; user contributions licensed under cc by-sa. rev 2022.5.12.42094
Being used to (and potentially spoiled by) MSSQL , I'm wondering how I can get at tables size in Oracle 10g. I have googled it so I'm now aware that I may not have as easy an option as sp_spaceused . Still the potential answers I got are most of the time outdated or don't work. Probably because I'm no DBA on the schema I'm working with.
Would anyone have solutions and or recommendations?
if having an proc give the answer is being spoiled, then take the answers you got from here and wrap them into a procedure and call it. dun dun duh. sp_spaceused. There's really little magic to it.
ТФФ 33.0. Полный перечень документов альбома ТФФ (Таблица 2)
Образование фрагментации в табличном пространстве
Заполненность экстентов данными способна вызвать в памяти "швейцарский сыр", объемный снаружи, но полный пустот внутри. Проведем эксперимент. Он рассчитан на типичный размер блока 4096 байт и параметры табличного пространства, по умолчанию используемые в версии Oracle 8.1.5. Наберем
После этого семь раз подряд нажмем / и возврат каретки, а затем:
Проверим число занятых таблицей EMP1 экстентов:
Занято два экстента.
Теперь добавим 50 еще строк в таблицу:
Повторим запрос к USER_EXTENTS и увидим, что добавился новый экстент:
Очевидно, что экстент с номером 2 в сегменте таблицы EMP1 практически пуст, и реально таблица занимает не 32*3 блока, а немногим более 64. Более того, выдадим:
Осталась всего одна строка (последняя по счету), но запрос к USER_EXTENTS снова покажет три экстента, несмотря на то, что от данных пусты полностью первые два.
'Пустой хаост' 'справа' от high watermark и 'дыры' слева могут образовывать вместе значительные скрытые резервы табличного пространства (представьте себе, что рост таблицы EMP1 надолго прекратился; на это время 27 блоков останутся 'замороженными'). Кроме того, большое число 'дырок' может снижать эффективность работы с таблицей, о чем говорилось в статье 'Возвращаем таблице вторую молодость'. И в том и в другом случае следует подумать: а не стоит ли таблицу пересоздать, возможно с новыми параметрами хранения (см. указанную статью).
Схема использования хранимым объектом блоков
Общая картинка распределения памяти в табличном пространстве выглядит примерно так:
Верхняя граница заполнения в Oracle может только расти - к сожалению для разработчика. По рисунку видно, что справа и слева от нее может образоваться много (как в примере выше) свободного места, которое по словарю-справочнику не увидишь. Прикинуть размер пропадающего пространства можно с помощью команды ANALYZE, однако это будет (а) примерная оценка и (б) анализ больших таблиц или большого их числа может потребовать у СУБД много ресурсов.
Тем не менее в Oracle есть выход из создавшейся ситуации. 'Заглянуть' внутрь экстентов быстро и эффективно можно с помощью двух процедур из системного пакета DBMS_SPACE.
Читайте также: