Tablespace
/*
*************************************************************************
----Author-SACHIN K SINGH
----DETAILS:This script is used for find tablespace details
----DATE : 18TH MARCH 2019
----Purpose of Script : TO Avoiding Database Downtime
*************************************************************************
*/
SELECT
     tablespace_name
    ,auto_extend
    ,ROUND ((total_space/1024/1024),2) AS total_space
    ,ROUND ((total_free_space/1024/1024),2) AS total_free
    ,ROUND (((total_space-total_free_space)/1024/1024),2) AS used_space
    ,TO_CHAR (NVL(ROUND((100*sum_free_blocks/sum_alloc_blocks),2),0)) || '%' AS percent_free
FROM
    (SELECT
          tablespace_name
         ,MAX (autoextensible) AS auto_extend
         ,SUM (blocks) AS sum_alloc_blocks
         ,SUM (bytes) AS total_space
    FROM
         dba_data_files
    GROUP BY
         tablespace_name)
     ,(SELECT
            dfs.tablespace_name AS freespc_tabspc_name
           ,NVL (SUM (bytes),0) AS total_free_space
           ,SUM (blocks) AS sum_free_blocks
      FROM dba_free_space dfs,
           dba_tablespaces dt
      WHERE
           dfs.tablespace_name(+) =dt.tablespace_name
      GROUP BY
           dfs.tablespace_name,dt.status)
      WHERE
            tablespace_name = freespc_tabspc_name
       UNION ALL
       SELECT
             dta.tablespace_name
            ,sfg.auto_extend
            ,ROUND ((sfg.bytes/1024/1024),2)
            ,ROUND ((sfg.bytes/1024/1024)-(NVL(sft.bytes,0)/1024/1024),2)
            ,ROUND (NVL (sft.bytes,0)/1024/1024,2)
            ,TO_CHAR (100-(nvl(sft.bytes/sfg.bytes*100,0)),'990.00')
        FROM
            dba_tablespaces dta,
                    (SELECT
                           tablespace_name
                          ,MAX (autoextensible) AS auto_extend
                          ,SUM (bytes) bytes
                     FROM
                          dba_temp_files
                     GROUP BY 
                           tablespace_name) sfg
                    ,(SELECT
                            tablespace_name
                           ,SUM (bytes_cached) AS bytes
                      FROM
                          v_$temp_extent_pool
                      GROUP BY
                          tablespace_name) sft
                WHERE
                      dta.tablespace_name = sfg.tablespace_name (+)
                  AND dta.tablespace_name = sft.tablespace_name (+)
                  AND dta.extent_management like 'LOCAL'
                  AND dta.contents like 'TEMPORARY'
                ORDER BY 1;