Site icon ScriptDBA.com

Oracle Objects Query

Query to identify the 10 largest objects in the Oracle database.

With the article Oracle Objects Query, we can quickly identify the objects that occupy the most space.

As explained in the article, Query compilazione OGGETTI INVALIDI, the objects in the database include all application structures, such as tables, indexes, etc.

To maintain high database performance, it’s important to monitor the sizes of these objects, as large objects may create difficulties in data retrieval.

Therefore, when an object reaches a large size, it may be advisable to consider a different logic for the object, such as partitioning.

In the case where we need to identify which objects occupy the most space in the database, the following article describes in detail the selected fields.

The selected fields are:

TABLESPACE_NAME – Name of the tablespace OWNER – Owner of the object SEGMENT_NAME – Name of the segment SEGMENT_TYPE – Type of the segment PARTITION_NAME – Name of the partition BYTES – Size of the segment converted to MB

The information is retrieved from the table DBA_SEGMENTS and filtered for the top 10 rows of the largest objects.

10 Largest Objects in a Schema

set lines 400
col tablespace_name for a20
col partition_name for a16
col owner format a15
col segment_name format a32
col segment_type format a15
select tablespace_name, owner, segment_name, segment_type, partition_name, mb
from (
select tablespace_name, owner
, segment_name
, segment_type
, partition_name 
, bytes / 1024 / 1024 "MB"
from dba_segments
order by bytes desc)
where rownum < 10
/

Per visionare le mie guide sui Comandi CRS clicca qui!
A questo LINK, invece, troverai le query SQL

Resta aggiornato sulle nostre attività entrando a far parte della nostra community:
Facebook
Youtube
Linkedin

Exit mobile version