查看oracle表空间使用情况，并且给空间不足的表文件进行扩容

查看表空间信息

 

语句一：

SELECT DBF.TABLESPACE_NAME "表空间名称",
       DBF.FILE_NAME "表空间文件",
       DBF.TOTALSPACE "总量(M)",
       DBF.TOTALBLOCKS AS 总块数,
       DFS.FREESPACE "剩余总量(M)",
       DFS.FREEBLOCKS "剩余块数",
       (DFS.FREESPACE / DBF.TOTALSPACE) * 100 "空闲比例"
  FROM (SELECT T.TABLESPACE_NAME,
               SUM(T.BYTES) / 1024 / 1024 TOTALSPACE,
               SUM(T.BLOCKS) TOTALBLOCKS,
               FILE_NAME
          FROM DBA_DATA_FILES T
         GROUP BY T.TABLESPACE_NAME,FILE_NAME) DBF,
       (SELECT TT.TABLESPACE_NAME,
               SUM(TT.BYTES) / 1024 / 1024 FREESPACE,
               SUM(TT.BLOCKS) FREEBLOCKS
          FROM DBA_FREE_SPACE TT
         GROUP BY TT.TABLESPACE_NAME) DFS
 WHERE TRIM(DBF.TABLESPACE_NAME) = TRIM(DFS.TABLESPACE_NAME)

 

语句二：

SELECT A.TABLESPACE_NAME "表空间名",
       B.FILE_NAME as "表空间文件",
       TOTAL / 1024 / 1024 || 'MB' 表空间大小,
       FREE / 1024 / 1024 || 'MB' 表空间剩余大小,
       (TOTAL - FREE) / 1024 / 1024 || 'MB' 表空间使用大小,
       ROUND((TOTAL - FREE) / TOTAL, 4) * 100 "使用率 %"
  FROM (SELECT TABLESPACE_NAME, SUM(BYTES) FREE
          FROM DBA_FREE_SPACE
         GROUP BY TABLESPACE_NAME) A,
       (SELECT TABLESPACE_NAME, SUM(BYTES) TOTAL, FILE_NAME
          FROM DBA_DATA_FILES
         GROUP BY TABLESPACE_NAME, FILE_NAME) B
 WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME
 order by ROUND((TOTAL - FREE) / TOTAL, 4) * 100 DESC;

 

更改表空间文件大小的语句：

ALTER DATABASE DATAFILE 'D:/WEBGISDB/HISTORY.ORA' RESIZE 5000M;

 

注：扩容的时候一定要写文件的路径，不然会提示无法找到文件