Hello readers! My name is Bruno Kremer, this is my first post from a series, and I will be talking about how we can identify the top segments of the database.

Introduction

It’s well known that we can create automated tasks to collect and save the space used/allocated by the database objects, such as saving snapshots of the DBA_SEGMENTS view. But what if this is your first contact with a specific database and you need to identify the top segments, estimate their growth ratio, check the history of space allocated, or even to perform some kind of capacity planning? There are some alternatives to answer these questions, but on this post I will share the starting point. Please feel free to customize the scripts to your own need.

Checking the top sized segments


select
s.owner,
s.segment_name,
s.segment_type,
round(sum(nvl(s.bytes,0))/1024/1024) size_mb
from dba_segments s
where s.bytes > 1048576 -- higher than 1MB
group by s.owner, s.segment_name, s.segment_type
order by size_mb desc
fetch first &TOP rows only;


Input values: &TOP – limit the number of rows returned.

Filters you might want to use: and s.segment_type in ('&OBJECT_TYPE') – ‘TABLE’, ‘TABLE PARTITION’, ‘INDEX’…

Return example:

Note: the scripts used in this series were tested on 12.1.0.2 databases. Some of these use the “FETCH FIRST” clause to limit the number of rows returned, but if you are using older versions of Oracle Database, you can still use the old fashion like “ROWNUM”.

Example:


select * from (
select s.owner, s.segment_name, s.segment_type, round(sum(nvl(s.bytes,0))/1024/1024) size_mb
from dba_segments s
where s.bytes > 1048576 -- higher than 1MB
group by s.owner, s.segment_name, s.segment_type
order by size_mb desc
) where rownum <= &TOP;


Now that you already have an idea regarding the size of the largest database segments, you might want to check the top growing segments… On next publications we will talk about how we can use AWR data dictionary views and some DBMS_SPACE procedures to estimate space usage history and top growing segments.