查看表空间使用率

set linesize 160

set pagesize 999

col TABLESPACE_NAME for a18

col TBS_TOTAL_MB for 9999999

col TBS_USED_MB for 9999999

col TBS_FREE_MB for 9999999

col TBS_RATE for a11             

col EXTEND_MAX_MB for 9999999999999

col EXTEND_FREE_MB for 9999999999999

col EXTEND_RATE for a11

select a.tablespace_name,

       round(current_size / 1024 / 1024, 1) TBS_TOTAL_MB,

       round((current_size - b.free_bytes) / 1024 / 1024, 1) TBS_USED_MB,

       round(b.free_bytes / 1024 / 1024, 1) TBS_FREE_MB,

       round(((current_size - b.free_bytes) / current_size) * 100, 1) || '%' TBS_RATE,

       round(a.max_size / 1024 / 1024, 1) EXTEND_MAX_MB,

       round((a.max_size - (current_size - b.free_bytes)) / 1024 / 1024, 1) EXTEND_FREE_MB,

       round(((current_size - b.free_bytes) / a.max_size) * 100, 1) || '%' EXTEND_RATE

  from (select tablespace_name,

               sum(ddf.bytes) current_size,

               sum(case

                     when ddf.autoextensible = 'YES' THEN

                      DDF.MAXBYTES

                     ELSE

                      DDF.BYTES

                   END) max_size

          from dba_data_files ddf

         group by tablespace_name

        union

        select tablespace_name,

               sum(ddf.bytes) current_size,

               sum(case

                     when ddf.autoextensible = 'YES' THEN

                      DDF.MAXBYTES

                     ELSE

                      DDF.BYTES

                   END) max_size

          from dba_temp_files ddf

         group by tablespace_name) a,

       (select dfs.tablespace_name, sum(dfs.bytes) free_bytes

          from dba_free_space dfs

         group by dfs.tablespace_name

        union

        select tfs.tablespace_name, sum(tfs.BYTES_FREE) free_bytes

          from v$TEMP_SPACE_HEADER tfs

         group by tfs.tablespace_name) b

 where a.tablespace_name = b.tablespace_name(+);

转载于:https://www.cnblogs.com/liang545621/p/9410698.html