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
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.
ReplyDeletethanks for considering the efforts !!!
ReplyDeleteHi Nimish,
ReplyDeleteTray 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
This comment has been removed by a blog administrator.
ReplyDeleteThese ɑre in fact wonderful ideas in concerning
ReplyDeleteblοgging. Yоu ɦave touched some pleasant thіngs here.
Any waү keеp up wrinting.
-- I like to convert to human readable form.
ReplyDelete-- 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;