AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |
Back to Blog
Totalspaces vs.4/29/2023 ![]() ![]() ![]() HAVING SUM(bytes)/1024/1024 > 10 /* Ignore really small tables */Ī table doesn't relate to a specific file on the file system. SELECT l.tablespace_name, l.owner, s.bytes ![]() ('INDEX', 'INDEX PARTITION', 'INDEX SUBPARTITION') SELECT i.tablespace_name, i.owner, s.bytes ('TABLE', 'TABLE PARTITION', 'TABLE SUBPARTITION') ROUND (ratio_to_report (SUM (bytes)) OVER () * 100) PercentįROM (SELECT tablespace_name, owner, bytes I modified the query to get the schema size per tablespace. SELECT l.table_name, l.owner, s.bytes, 'LOB Index' as "Type" SELECT l.table_name, l.owner, s.bytes, 'LOB' as "Type"ĪND s.segment_type IN ('LOBSEGMENT','LOB PARTITION','LOB SUBPARTITION') SELECT i.table_name, i.owner, s.bytes, 'Index' as "Type"ĪND s.segment_type in ('INDEX','INDEX PARTITION','INDEX SUBPARTITION') WHERE segment_type in ('TABLE','TABLE PARTITION','TABLE SUBPARTITION') ( SELECT segment_name table_name, owner, bytes, 'Table' as "Type" 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 SELECT SELECT l.owner, l.column_name AS object_name, 'LOB_INDEX' AS object_type, SELECT l.owner, l.column_name AS object_name, 'LOB_COLUMN' AS object_type, S.tablespace_name, s.extents, s.initial_extent SELECT i.owner, i.index_name AS object_name, 'INDEX' AS object_type, SELECT owner, segment_name AS object_name, 'TABLE' AS object_type, ROUND(Sum(bytes/1024/1024) OVER (PARTITION BY table_name)) AS total_table_meg Tablespace_name, extents, initial_extent, Owner, object_name, object_type, table_name, ROUND(bytes)/1024/1024 AS meg, I modified the WW's query to provide more detailed information: SELECT * FROM ( Note: Changes made to the above after reading this AskTom thread Select table_name, blocks, empty_blocks, num_freelist_blocks To do this for all tables in a schema at once: beginĭbms_stats.gather_schema_stats ('MYSCHEMA') Multiply the number of blocks by the block size in use (usually 8KB) to get the space consumed - e.g. The total number of blocks allocated to the table is blocks + empty_blocks + num_freelist_blocks.īlocks is the number of blocks that actually contain data. Then find the number of blocks occupied by the table from the generated stats: select blocks, empty_blocks, num_freelist_blocks WARNING: As Justin says in his answer, gathering optimiser stats affects query optimisation and should not be done without due care and consideration! 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.įirst, gather optimiser stats on the table (if you haven't already): beginĭbms_stats.gather_table_stats('MYSCHEMA','MYTABLE') Second, the closest equivalent to the SQL Server sp_spaceused procedure is likely Oracle's DBMS_SPACE package. If your statistics are not accurate, there is probably a reason for that and you don't want to disturb the status quo. If your statistics are accurate, you can query USER_TABLES (or ALL_TABLES or DBA_TABLES) directly without calling GATHER_TABLE_STATS. 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. 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. HAVING SUM(bytes)/1024/1024 > 10 /* Ignore really small tables */įirst off, I would generally caution that gathering table statistics in order to do space analysis is a potentially dangerous thing to do. WHERE segment_type IN ('TABLE', 'TABLE PARTITION', 'TABLE SUBPARTITION')ĪND s.segment_type IN ('INDEX', 'INDEX PARTITION', 'INDEX SUBPARTITION')ĪND s.segment_type IN ('LOBSEGMENT', 'LOB PARTITION') (SELECT segment_name table_name, owner, bytes ROUND( ratio_to_report( sum(bytes) ) over () * 100) Percent Note that this requires access to the DBA_* views. Often you are interested to know "How much spaces the the Purchase Order table take, including any indexes" rather than just the table itself. It tells you how much space is allocated for each table taking into account the indexes and any LOBs on the table. ![]()
0 Comments
Read More
Leave a Reply. |