Scripts

Top n largest segments per tablespace (rank function)

User Rating:  / 0
PoorBest 
Parent Category: Articles
Created on Monday, 22 March 2010 20:07
Last Updated on Thursday, 04 April 2013 12:30
Published on Monday, 22 March 2010 20:07
Hits: 4354

  

prompt which database;
define topn=5;
define minblocks=10
select name from v$database;
set pagesize 100
set linesize 100
column tablespace_name format a20
break on tablespace_name
colum segment_name format a30
column owner format a20
column RANK format 99

select * from
( select tablespace_name,owner,segment_name,blocks,rank()
over (partition by tablespace_name order by blocks desc) RANK from dba_segments )
where tablespace_name in (select tablespace_name from dba_tablespaces where contents = 'PERMANENT')
and blocks > &minblocks and rank <= &topn;

 

TABLESPACE_NAME      OWNER		  SEGMENT_NAME			     BLOCKS	  RANK
-------------------- -------------------- ------------------------------ ---------- ----------
PM PM T_EXCHANGERATE_ER 9216 1
PM IDX_ER_PM_ER_RATE 6528 2
PM SYS_C009652 2688 3
PM BIX_JOIN 40 4
SYSAUX XDB SYS_LOB0000055068C00025$$ 10240 1
SYS SYS_LOB0000073117C00004$$ 2448 2
MDSYS SYS_LOB0000060273C00006$$ 2304 3
XDB XDB$RESOURCE 1792 4
SYS SYS_LOB0000005981C00038$$ 1152 5
SYSTEM SYS IDL_UB1$ 32768 1
SYS SOURCE$ 17408 2
SYS C_TOID_VERSION# 4992 3
SYS IDL_UB2$ 4224 4
SYS SYS_LOB0000000515C00002$$ 3200 5
TS_APEX APEX_TEST SYS_LOB0000079085C00007$$ 16 1
USERS STUDENT1 TRANSACTIONS 90112 1
STUDENT1 BROKERAGES 384 2
PM T_SEC_PICTURE_SECFILE 16 3