问题背景：
oracle数据库一业务的表空间突然激增，近乎一个月两T，经协商后发现存在lob大字段存储日志信息，且存在切割业务，切割业务操作为切走一条数据，还在原库记录切走了什么数据，导致切割业务越切割越大。大佬决定使用收缩空间问题暂且延缓激增的表空间问题。
操作方案：
查看表的可收缩情况报告
set linesize 260
col task_name format a30
col segname format a25
col partition format a15
col type format a15
col message format a75
col Size(G) format 999,999,999,999
SELECT *
FROM (
SELECT af.task_name
,ao.attr2 segname
,ao.attr3 PARTITION
,ao.type
,af.message
,to_number(regexp_substr((regexp_substr(af.message, 'estimated savings is [^.]*')), '[0-9]+')) / 1024 / 1024 / 1024 AS "Size(G)"
FROM dba_advisor_findings af
,dba_advisor_objects ao
WHERE ao.task_id = af.task_id
AND ao.object_id = af.object_id
AND to_number(regexp_substr((regexp_substr(af.message, 'estimated savings is [^.]*')), '[0-9]+')) / 1024 / 1024 / 1024>0
ORDER BY "Size(G)" DESC
)
WHERE rownum <= 100;两种sql看个人选择
SELECT
'Segment Advice --------------------------'|| chr(10) ||
'TABLESPACE_NAME : ' || tablespace_name || chr(10) ||
'SEGMENT_OWNER : ' || segment_owner || chr(10) ||
'SEGMENT_NAME : ' || segment_name || chr(10) ||
'ALLOCATED_SPACE(M) : ' || round(allocated_space/1024/1024) || chr(10) ||
'RECLAIMABLE_SPACE(M): ' || round(reclaimable_space/1024/1024) || chr(10) ||
'RECOMMENDATIONS : ' || recommendations || chr(10) ||
'SOLUTION 1 : ' || c1 || chr(10) ||
'SOLUTION 2 : ' || c2 || chr(10) ||
'SOLUTION 3 : ' || c3 Advice
FROM
TABLE(dbms_space.asa_recommendations('TRUE', 'TRUE', 'FALSE'))
order by reclaimable_space;执行sql后的结果分别如下
#sql 1
TASK_NAME SEGNAME PARTITION TYPE MESSAGE Size(G)
------------------------------ ------------------------- --------------- --------------- --------------------------------------------------------------------------- ----------------
SYS_AUTO_SPCADV_12002204052023 IFDFILE TABLE Perform shrink, estimated savings is 16404577 bytes. 0
SYS_AUTO_SPCADV_06002205052023 IFDFILE TABLE Perform shrink, estimated savings is 14885844 bytes.
#sql2
ADVICE
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Segment Advice --------------------------
TABLESPACE_NAME : SCIPMUSR
SEGMENT_OWNER : SCIPMUSR
SEGMENT_NAME : IFDFILE
ALLOCATED_SPACE(M) : 472
RECLAIMABLE_SPACE(M): 14
RECOMMENDATIONS : Perform shrink, estimated savings is 14885844 bytes.
SOLUTION 1 : alter table "SCIPMUSR"."IFDFILE" shrink space
SOLUTION 2 : alter table "SCIPMUSR"."IFDFILE" shrink space COMPACT
SOLUTION 3 :
针对单个表的大字段进行收缩
alter table user.tablename modify lob(字段) (shrink space cascade);若较多可进行sql拼接
select 'alter table '||a.owner||'.'||a.table_name||' modify lob('||a.column_name||') (shrink space cascade); ' sql,ROUND(b.BYTES / 1024 / 1024) total_M from dba_lobs a, dba_segments b where a.segment_name = b.segment_name and b.TABLESPACE_NAME in ('SCIPMUSR','USERS') order by total_M;执行结果
SQL> select 'alter table '||a.owner||'.'||a.table_name||' modify lob('||a.column_name||') (shrink space cascade); ' sql,ROUND(b.BYTES / 1024 / 1024) total_M from dba_lobs a, dba_segments b where a.segment_name = b.segment_name and b.TABLESPACE_NAME in ('SCIPMUSR','USERS') order by total_M;
SQL
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
TOTAL_M
----------
alter table SCIPMUSR.OPERATION modify lob(MESSAGE) (shrink space cascade);
0
alter table SCIPMUSR.CENTRALLOG modify lob(CONTENT) (shrink space cascade);
0
可设置为脚本，跑完后查看表空间使用率情况
