查看表空间利用率脚本-扩表空间大小

 

查看表空间利用率脚本

select t.*

  from (SELECT D.TABLESPACE_NAME,

               SPACE "SUM_SPACE(M)",

               BLOCKS SUM_BLOCKS,

               SPACE - NVL(FREE_SPACE, 0) "USED_SPACE(M)",

               ROUND((1 - NVL(FREE_SPACE, 0) / SPACE) * 100, 2) "USED_RATE(%)",

               FREE_SPACE "FREE_SPACE(M)"

          FROM (SELECT TABLESPACE_NAME,

                       ROUND(SUM(BYTES) / (1024 * 1024), 2) SPACE,

                       SUM(BLOCKS) BLOCKS

                  FROM DBA_DATA_FILES

                 GROUP BY TABLESPACE_NAME) D,

               (SELECT TABLESPACE_NAME,

                       ROUND(SUM(BYTES) / (1024 * 1024), 2) FREE_SPACE

                  FROM DBA_FREE_SPACE

                 GROUP BY TABLESPACE_NAME) F

         WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)

        UNION ALL --if have tempfile

        SELECT D.TABLESPACE_NAME,

               SPACE "SUM_SPACE(M)",

               BLOCKS SUM_BLOCKS,

               USED_SPACE "USED_SPACE(M)",

               ROUND(NVL(USED_SPACE, 0) / SPACE * 100, 2) "USED_RATE(%)",

               SPACE - USED_SPACE "FREE_SPACE(M)"

          FROM (SELECT TABLESPACE_NAME,

                       ROUND(SUM(BYTES) / (1024 * 1024), 2) SPACE,

                       SUM(BLOCKS) BLOCKS

                  FROM DBA_TEMP_FILES

                 GROUP BY TABLESPACE_NAME) D,

               (SELECT TABLESPACE,

                       ROUND(SUM(BLOCKS * 8192) / (1024 * 1024), 2) USED_SPACE

                  FROM V$SORT_USAGE

                 GROUP BY TABLESPACE) F

         WHERE D.TABLESPACE_NAME = F.TABLESPACE(+)) t

 order by "USED_RATE(%)" desc;

 

1 查询要修改的表空间的数据文件名：

select file_name,blocks,tablespace_name from dba_data_files a where a.tablespace_name='D_1024_IDX'

 

2 将对应表空间的dpf文件路径记下来。比如'/data/teleport/userdata/d_1024_idx.dbf'

 

3 最后修改表空间数据文件：

ALTER DATABASE DATAFILE '/data/teleport/userdata/d_1024_idx.dbf' RESIZE 20000m;大小请根据前端实际情况定，需先确认操作系统中是否有空间