Скрипт определения размера таблиц в oracle и очистка данных
Часта ситуация когда в Oracle размер таблиц достигает больших величин и мы оказываемся в положении когда прикладное приложение просто не может записать что-либо в базу данных. Пришлось столкнуться с такой проблемой в Oracle XE, когда размер уперся в лицензионное ограничение этой бесплатной версии oracle.
Все операции будем производить через консоль линукс-сервера, на котором установлена наша СУБД.
Для начала логинимся под root и переключаемся на пользователя под которым работает демон оракла, у меня это пользователь oracle.
Find Table Size & Schema Size and Database Size in Oracle
I will explain How to Find Table Size & Schema Size and Database Size in Oracle in this post.
Oracle Table Size Check
You can find out the Table size using the dba_segments views as follows.
If you don’t have DBA rights, you can query it using user_segments view as follows.
If your table is partitioned, you can find out the partition size as follows.
Find Top Tables by Size in Oracle
You want to learn which table is biggest in database or which schema is biggest in database.
You can learn Top segment of database and Table size with following script.
Find Top Schemas by Size in Oracle
You can learn Top Schema of database and Schema size with following script.
You can check the size of schema using the following script.
Oracle Database Size
You can check the size of oracle database from segment size and datafile size. Datafile size is total size of physical database size. But Segment size is net size of database which is called total data size.
Как определить размер таблиц в Oracle?
Сегодня мы покажем, как определить размер таблиц в Oracle. В любой базе данных, в том числе и в базах данных Oracle, таблицы являются основными инструментами для сохранения информации. Удобство таблиц неоспоримо: они состоят из столбцов и строк, что обеспечивает удобное чтение располагаемой информации. Однако любая таблица может быть ограничена в размерах, поэтому иногда важно понимать размер таблицы, чтобы ориентироваться на вероятный объем сохраненной информации.
Не зная размер таблицы, может сложиться такая ситуация, что приложение, которое использует ее для записи, перестает нормально функционировать, потому что просто не может ничего записать. С базами Oracle — это довольно частая ситуация, особенно когда используется бесплатная версия этой базы данных. А в бесплатной версии размер таблиц ограничен лицензионным соглашением, а это значит, что рано или поздно, но все равно можно « у переться в потолок».
Как определить размер таблицы Oracle?
-
с таблицами нужно работать с правами администратора;
-
при вычислении размера таблиц нужно использовать пользователя, под которым работает «демон» Oracle;
-
в самом скрипте нужно обязательно указывать владельца таблицы.
Заключение
Узнать размер базы данных Oracle непросто, но возможно. То есть специальной какой-то кнопки или функции нет, поэтому приходится пользоваться большими массивными скриптами. Мы представили 2 скрипта на обозрение. Если по каким-то причинам они вам не подходят, тогда можно поискать в сети другие. На специализированных форумах администраторы баз данных Oracle делятся такими скриптами.
Мы будем очень благодарны
если под понравившемся материалом Вы нажмёте одну из кнопок социальных сетей и поделитесь с друзьями.
How do I calculate tables size in Oracle
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?
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: These are estimates, made more accurate with gather 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.