It is very common as time passes the database to constantly grow. You can calculate the tables and indexes space occupied with the following script.
SELECT SUBSTR(segment_type, 0, 18) SEGMENT, SUM(size_mb) size_mb,
ROUND((RATIO_TO_REPORT(SUM(size_mb)) OVER ())*100, 2)|| '%' PERC
FROM (
SELECT segment_type, ROUND(SUM(size_mb)) size_mb
FROM (SELECT owner, segment_name, segment_type, partition_name,
ROUND(bytes/(1024*1024),2)
SIZE_MB, tablespace_name
FROM DBA_SEGMENTS
WHERE SEGMENT_TYPE IN ('LOBINDEX', 'INDEX PARTITION',
'INDEX SUBPARTITION', 'INDEX',
'TABLE SUBPARTITION', 'TABLE PARTITION', 'LOB PARTITION',
'LOBSEGMENT', 'TABLE', 'LOB SUBPARTITION')
ORDER BY bytes DESC) GROUP BY segment_type ORDER BY size_mb DESC) GROUP BY SUBSTR(segment_type, 0, 18); SEGMENT SIZE_MB PERC ------------------ ---------- -------- TABLE SUBPARTITION 1980567 17,18% INDEX PARTITION 1333269 11,56% LOBINDEX 298 0% TABLE PARTITION 1806063 15,67% LOB PARTITION 12080 ,1% INDEX SUBPARTITION 2240245 19,43% LOBSEGMENT 157713 1,37% TABLE 3275990 28,42% INDEX 722279 6,27% 9 rows selected.If someone wants to have a better grouping like tables against indexes and lob space can use the following.
SELECT SUBSTR(segment_type, 0, 3) SEGMENT, SUM(size_mb) size_mb,
ROUND((RATIO_TO_REPORT(SUM(size_mb)) OVER ())*100, 2)|| '%' PERCFROM (SELECT segment_type, ROUND(SUM(size_mb)) size_mb
FROM (SELECT owner, segment_name, segment_type, partition_name, ROUND(bytes/(1024*1024),2) SIZE_MB, tablespace_name
FROM DBA_SEGMENTS
WHERE SEGMENT_TYPE IN ('LOBINDEX', 'INDEX PARTITION', 'INDEX SUBPARTITION', 'INDEX',
'TABLE SUBPARTITION', 'TABLE PARTITION', 'LOB PARTITION', 'LOBSEGMENT', 'TABLE', 'LOB SUBPARTITION')
ORDER BY bytes DESC)
GROUP BY segment_type
ORDER BY size_mb DESC)
GROUP BY SUBSTR(segment_type, 0, 3); SEG SIZE_MB PERC--- ---------- ----------TAB 7062620 61,26%LOB 170091 1,48%IND 4295793 37,26%
No comments:
Post a Comment