10 Largest Tables in Oracle with Size Greater than 1GB

Query to find out size of the 10 Largest Tables sitting inside an Oracle database which is greater than 1GB in size


COLUMN segment_nm FORMAT A45
COLUMN used_size FORMAT A16
Set Lines 999
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;

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s