Oracle Database Administration

Hi, Here i am going to write the queries related to tablespace usage. We can find the free space,used space and its percentage of tablespace and datafiles separately. If your tablespace is not auto extendable, you can use the below query given in 1 a) and 1 b) to get space usage of tablespace 1 a) Combine query with sm$ts_avail,sm$ts_used,sm$ts_free: ===================================================== set pages 1000 break on report compute sum of Total_MB on report compute sum of Used_MB on report compute sum of Free_MB on report select a.TABLESPACE_NAME, round(Curr_Size,1) Total_MB,round(Used,1) Used_MB,round(Free,1) Free_MB, round(100*(1-free/Curr_Size),1) Usage from (select TABLESPACE_NAME,BYTES/(1024*1024) Curr_Size from sm$ts_avail) a ,(select TABLESPACE_NAME,BYTES/(1024*1024) Used from sm$ts_used) b, (select TABLESPACE_NAME,BYTES/(1024*1024) Free from sm$ts_free) c where a.TABLESPACE_NAME=b.TABLESPACE_NAME(+) and a.TABLESPACE_NAME=c.TABLESPACE_NAME order by 1