Hi Gurus,
Greetings.
I am looking for small on analytical function.
I have collection table called "DBA_SEGMENTS_HISTORY" . Here I am looking for Top 10 segments in tablespace wise. I have 69 tablespaces.
Rank is not showing 1,2....10 and 1,2....10 for second tablespace. But it show 690 ranks.
Could you kindly help me out to fix this.
Requirement : TOP 10 segments in each tablespace for the given date range from my collection table.
SELECT a.*, ROWNUM as " RANK "
FROM ( SELECT SEGMENT_NAME,TABLESPACE_NAME,
ROUND (MAX ("Size in GB")) " Top Size of TS",
TO_CHAR (COLLECTION_DATE, 'MM/DD/YYYY')
FROM ( SELECT /*+ parallel */
SEGMENT_NAME,TABLESPACE_NAME, /*+ ROWID(DBA_SEGMENTS_HISTORY) */
SUM (BYTES / 1024 / 1024 / 1024) "Size in GB",
COLLECTION_DATE,
ROW_NUMBER ()
OVER (
PARTITION BY TABLESPACE_NAME
ORDER BY
SUM (BYTES / 1024 / 1024 / 1024) DESC NULLS LAST, SEGMENT_NAME)
dr
FROM DBA_SEGMENTS_HISTORY
WHERE COLLECTION_DATE BETWEEN TO_DATE ('08/13/2015',
'MM/DD/YYYY')
AND TO_DATE ('12/15/2015',
'MM/DD/YYYY')
GROUP BY SEGMENT_NAME,TABLESPACE_NAME, COLLECTION_DATE
ORDER BY "Size in GB" DESC)
WHERE dr <= 10
GROUP BY SEGMENT_NAME,tablespace_name, TO_CHAR (COLLECTION_DATE, 'MM/DD/YYYY')
ORDER BY 3 DESC)a
WHERE ROWNUM <= 690;
Thanks
Raj