Oracle表空间占用过大

以下操作均需要sys权限的用户

查寻表空间使用情况

SELECT a.tablespace_name "表空间名",a.bytes / 1024 / 1024 "表空间大小(M)",(a.bytes - b.bytes) / 1024 / 1024 "已使用空间(M)",b.bytes / 1024 / 1024 "空闲空间(M)",round(((a.bytes - b.bytes) / a.bytes) * 100, 2) "使用比"FROM (SELECT tablespace_name, sum(bytes) bytes FROM dba_data_files GROUP BY tablespace_name) a,(SELECT tablespace_name, sum(bytes) bytes, max(bytes) largest FROM dba_free_space GROUP BY tablespace_name) b WHERE a.tablespace_name = b.tablespace_name ORDER BY ((a.bytes - b.bytes) / a.bytes) DESC

查看表空间文件是否自动扩展

SELECT file_id, file_name, tablespace_name, autoextensible, increment_by FROM dba_data_files WHERE tablespace_name = 'BILL_PRE_WS' ORDER BY file_id desc;

查看”autoextensible”对应的值是YES还是NO，若是NO，说明表空间的自动扩展功能没有开，改成YES就可以了。

查询所有的schema所占空间大小

SELECT *
  FROM (SELECT OWNER,
               SEGMENT_NAME,
               SEGMENT_TYPE,
               ROUND(BYTES / 1024 / 1024 / 1024, 2) AS G
          FROM DBA_SEGMENTS
         WHERE TABLESPACE_NAME = 'BILL_PRE_WS'
        
         ORDER BY BYTES DESC)
 WHERE ROWNUM <= 30;

分析LOBSEGMENT类型占用的字段

经查看，BGW_FLOW_CONDITION表这一列FLOW_DATA建的clob,其中大对象单独存放在SYS_LOB0000093441C00002$$ 这个段中，LOBSEGMENT保存了lob列的真正数据，会非常大30G，并且独立于原始表存在。

 SELECT B.TABLE_NAME,

       B.COLUMN_NAME,

       A.SEGMENT_NAME,

       a.SEGMENT_TYPE,

       ROUND(SUM(A.BYTES / 1024 / 1024 / 1024), 2) G

  FROM DBA_SEGMENTS A

  LEFT JOIN DBA_LOBS B

    ON A.OWNER = B.OWNER

   AND A.SEGMENT_NAME = B.SEGMENT_NAME
   
 WHERE B.SEGMENT_NAME = 'SYS_LOB0000114026C00009$$'

 HAVING ROUND(SUM(A.BYTES / 1024 / 1024 / 1024), 2) >1

 GROUP BY B.TABLE_NAME, B.COLUMN_NAME, A.SEGMENT_NAME,a.SEGMENT_TYPE;

清理建的lob列的表

如果需要清理，可以truncate 该BGW_FLOW_CONDITION表，或者drop不需要的分区（如果是分区表）。可以看到清空后表空间内存降下来了