Oracle Database Discussions

Hello All,

Need help with below:

I need to fetch top 10 tablespaces by size from each of the production databases we have. For this, we built customized table (DBA_MAINT.prod_tbsp_info). TARGET_NAME is the DB name

SQL> desc dba_maint.prod_tbsp_info
Name Null? Type
----------------------------------------------------------------------------------- -------- --------------------------------------------------------
TARGET_NAME NOT NULL VARCHAR2(9)
COLLECTION_TS NOT NULL DATE
TBSP_NAME NOT NULL VARCHAR2(50)
AUTOEXT NOT NULL VARCHAR2(5)
BIGFILE NOT NULL VARCHAR2(5)
BLOCK_SIZE NUMBER
DBF_COUNT NUMBER
SIZE_MB NUMBER
USED_MB NUMBER
FREE_MB NUMBER
MAXEXT_MB NUMBER
PCT_FREE NUMBER

The below query gives me complete list of tablespaces from each database. However, I need only top 10 TS by size. I need to use rownum, but not sure how the syntax would be to fetch TOP 10 TS from each database.

select TARGET_NAME, TBSP_NAME, SIZE_MB from dba_maint.prod_tbsp_info
where TARGET_NAME in (select distinct TARGET_NAME from dba_maint.prod_tbsp_info)
AND COLLECTION_TS = trunc(sysdate) - 2
order by TARGET_NAME;

Greatly appreciate your help

Thanks
Shanthan