sysaux tablespace fragmentation issue

Feb 23, 2016 3:32PM

Hello,

I have 12c DB. I have this sysaux tablespace which I am trying to save some space from the OS level.

select substr(A.tablespace_name,1,16) "Tablespace",b.file_name,

2 (SUM(B.BYTES)*COUNT(DISTINCT B.FILE_ID)/COUNT(B.FILE_ID)/1024/1024)-(ROUND(SUM(C.BYTES)/1024/1024/COUNT(DISTINCT B.FILE_ID))) "USED SIZE(Mb)",

TO_CHAR(100-(SUM(C.BLOCKS)*100*COUNT(B.FILE_ID)/(SUM(B.BLOCKS)*COUNT(DISTINCT B.FILE_ID)))/COUNT(DISTINCT B.FILE_ID),'999.99')||'%' USED_USAGE,

ROUND(SUM(C.BYTES)/1024/1024/COUNT(DISTINCT B.FILE_ID)) "FREE SIZE(MB)",

3 4 5 TO_CHAR((SUM(C.BLOCKS)*100*COUNT(B.FILE_ID)/(SUM(B.BLOCKS)*COUNT(DISTINCT B.FILE_ID)))/COUNT(DISTINCT B.FILE_ID),'999.99')||'%' FREE_USAGE,

Database Administration (MOSC)