Oracle: Query to find the 10 Largest Objects in DB

SELECT * FROM
(
select
    SEGMENT_NAME,
    SEGMENT_TYPE,
    BYTES/1024/1024/1024 GB,
    TABLESPACE_NAME
from
    dba_segments
order by 3 desc 
) WHERE
ROWNUM <= 10


Related Links:
- Oracle DBA Daily Checklist
- Database Routine Checklist Queries
- Total Size of Database

6 comments:

  1. Awesome blog. Thanks for providing all the useful queries on your blog. Its a great pleasure for me to visit your blog and to learn so many queries that are a great help to me. I will note this query also to use it whenever I have to find the largest objects from my database.

    ReplyDelete
  2. thanks for considering the efforts !!!

    ReplyDelete
  3. Hi Nimish,

    Tray this query..

    Show the ten largest objects in the database

    col owner format a15
    col segment_name format a30
    col segment_type format a15
    col mb format 999,999,999
    select owner
    , segment_name
    , segment_type
    , mb
    from (
    select owner
    , segment_name
    , segment_type
    , bytes / 1024 / 1024 "MB"
    from dba_segments
    order by bytes desc
    )
    where rownum < 11
    /

    Ranvir Meshram
    +91-9890673586

    ReplyDelete
  4. This comment has been removed by a blog administrator.

    ReplyDelete
  5. These ɑre in fact wonderful ideas in concerning
    blοgging. Yоu ɦave touched some pleasant thіngs here.
    Any waү keеp up wrinting.

    ReplyDelete
  6. -- I like to convert to human readable form.
    -- Also avoid two ORDER BY clauses use DENSE_RANK


    COLUMN segment_nm FORMAT A45
    COLUMN used_size FORMAT A16

    SELECT
    segment_nm,
    segment_type,
    LPAD( CASE
    WHEN bytes < 1024
    THEN ROUND( bytes, 2 ) || ' B'
    WHEN bytes < POWER( 1024, 2 )
    THEN ROUND( ( bytes / 1024 ), 2 ) || ' KB'
    WHEN bytes < POWER( 1024, 3)
    THEN ROUND( ( bytes / 1024 / 1024 ), 2 ) || ' MB'
    WHEN bytes < POWER( 1024, 4 )
    THEN ROUND( ( bytes / 1024 / 1024 / 1024 ), 2 ) || ' GB'
    ELSE ROUND( ( bytes / 1024 / 1024 / 1024 / 1024 ), 2 ) || ' TB'
    END, 15 ) AS used_size,
    tablespace_name
    FROM
    (
    SELECT
    owner || '.' || LOWER( segment_name ) AS segment_nm,
    segment_type,
    bytes,
    tablespace_name,
    DENSE_RANK() OVER ( ORDER BY bytes DESC ) AS dr
    FROM
    dba_segments
    ) A
    WHERE
    dr <= 10 /* top-10 may have more then 10 */
    ORDER BY /* lots of ordering in cases of ties */
    bytes DESC,
    dr ASC,
    segment_nm ASC;

    ReplyDelete