Q：oracle表空间满了怎么办？

查询表空间使用率：

--查看表空间使用率

SELECT a.tablespace_name as "TABLESPACE_NAME",
DECODE(TRUNC(total),0,REPLACE(TO_CHAR(total),'.', '0.'),TO_CHAR(total)) as "MAX_SIZE(G)",
DECODE(TRUNC(total - free),0,REPLACE(TO_CHAR(total - free),'.', '0.'),TO_CHAR(total - free)) as "USEAGE_SIZE(G)",
DECODE(TRUNC(free),0,REPLACE(TO_CHAR(free),'.', '0.'),TO_CHAR(free)) as "FREE_SIZE(G)",
DECODE(TRUNC(round((total-free)/total,6)*100),0,REPLACE(TO_CHAR(round((total-free)/total,6)*100),'.', '0.'),TO_CHAR(round((total-free)/total,6)*100)) AS "USEAGE_RATE(%)"
FROM (SELECT tablespace_name,ROUND(SUM(bytes)/(1024*1024*1024),6) free
FROM dba_free_space
GROUP BY tablespace_name) a,
(SELECT tablespace_name, ROUND(SUM(bytes)/(1024*1024*1024),6) total
FROM dba_data_files
GROUP BY tablespace_name) b
WHERE a.tablespace_name = b.tablespace_name
order by a.tablespace_name desc;

 

--各个表空间占用磁盘空间大小
select tablespace_name,sum(bytes)/1024/1024 "Size(MB)" from dba_data_files group by tablespace_name;

 

查询具体表使用率：

--各个表占所有段的存储信息

SELECT SEGMENT_NAME, ROUND(SUM(BYTES / 1024 / 1024 / 1024), 2) G，OWNER
FROM DBA_SEGMENTS
--WHERE SEGMENT_NAME IN
--(SELECT TABLE_NAME FROM USER_TAB_COLUMNS WHERE DATA_TYPE = 'CLOB')
GROUP BY SEGMENT_NAME,OWNER
ORDER BY 2 DESC;

SYS_LOB：数据库中varchar2只能值为4000，PL/SQL中 VARCHAR2 变量类型，字节长度为32767，针对 VARCHAR2 满足不了我们的需要时，Oracle就提出了大数据类型LOB( Large Object，大对象)。
LOB类型分为BLOB和CLOB两种：BLOB即二进制大型对象(Binary Large Object)，适用于存贮非文本的字节流数据(如程序、图象、影音等)。

--查询某个sys_lob（红标字体）的存储大小

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_LOB0000029275C00015$$'
HAVING ROUND(SUM(A.BYTES / 1024 / 1024 / 1024), 2) >1
GROUP BY B.TABLE_NAME, B.COLUMN_NAME, A.SEGMENT_NAME,a.SEGMENT_TYPE;

--各个表占用当前用户的表空间大小
select Segment_Name,Sum(bytes)/1024/1024 as "SIZE(MB)" From User_Extents Group By Segment_Name order by Sum(bytes) desc;

 

处理方法：

--增加表空间（开启omf直接改表空间名即可）
alter tablespace 表空间名 add datafile size 32700m;
alter tablespace TBS_HIP_CDR add datafile size 32700m;
--未开启omf
alter tablespace oracle_data add datafile '/u01/app/oracle/oradata/ybzjk/oracle_data1.dbf' size 100m autoextend on next 100m maxsize 1024m;

--HIS数据库清楚审计日志（12c以后用存储过程清理，清理表不管用）
BEGIN
DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,container => DBMS_AUDIT_MGMT.CONTAINER_CURRENT,use_last_arch_timestamp => FALSE);
END;
/