If you ever wanted to know how what's taking space in an Oracle database, or how large is the table you're working on, here's a script which answers these questions. It shows the size of all the database objects large than 10 Mb in a particular database schema.
The following columns are returned:
- Owner schema.
- Object name and type (INDEX, TABLE, etc.).
- Name of the table this object is associated with. E.g. indexes are associated with their parent tables.
- Database space occupied by the object in megabytes.
- Tablespace this object is in.
- Number of extents allocated for the object.
- Size of the initial extent in bytes.
- Total database size occupied by the parent table. E.g. for indexes it will be the size of the parent table plus sizes of all the indexes on that table.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 | -- Find the size of all tables in an Oracle schema -- Script by Sergey Stadnik, http://blog.dbtuning.net -- Licensed under cc-wiki with attribution required -- Based on Stackoverflow.com discussion -- http://stackoverflow.com/questions/264914/how-do-i-calculate-tables-size-in-oracle DEFINE schema_name = 'replace_with_your_schema_name' 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 in UPPER('&schema_name') ) WHERE total_table_meg > 10 ORDER BY total_table_meg DESC, meg DESC / |
This script is based on the Stackoverflow.com discussion.
No comments:
Post a Comment