记录：Oracle服务器磁盘空间紧张的解决(使用在线重定义将LOB转换为SecureFile存储＜压缩+去重＞)

• 老鼠搬家，转移历史数据（跑数据插入bak表，然后备份到移动硬盘上）

CREATE TABLE "XXX"."TB_XXX_BAK"
   (  "PK_XXX" NUMBER NOT NULL ENABLE,
  "X1" CLOB,
  "DT" DATE DEFAULT sysdate,
   CONSTRAINT "PK_XXX_BAK" PRIMARY KEY ("PK_XXX") USING INDEX
  TABLESPACE "USERS"  ENABLE
   )
 LOB ("X1") STORE AS SECUREFILE (COMPRESS DEDUPLICATE LOB)
  PARTITION BY RANGE ("DT")
 (PARTITION p_min VALUES LESS THAN (TO_DATE('2019-01-01', 'yyyy-mm-dd')) TABLESPACE "USERS",
  PARTITION P_2019_01 VALUES LESS THAN (TO_DATE('2019-02-01', 'yyyy-mm-dd')) TABLESPACE "USERS"
  ) NOCOMPRESS LOGGING TABLESPACE "USERS";
----
declare
  v_count number;
begin 
  v_count := 0;
  for p in (select PK_XXX from XXX.TB_XXX where dt between to_date('20190101 00:00:00','YYYYMMDD HH24:MI:SS') and to_date('20190101 23:59:59','YYYYMMDD HH24:MI:SS')) loop
    insert into XXX.TB_XXX_BAK(select * from XXX.TB_XXX t1 where t1.PK_XXX= p.PK_XXX);
    v_count := v_count + 1;
    if mod(v_count,500) = 0 then
      commit;
    end if;
  end loop;
  commit;
  dbms_output.put_line(to_char(v_count));
end;

• 重复运行上述步骤，不停的导出历史数据腾地方，直到有足够的可用空间进行重定义
• 最后走一遍重定义就好了
• 关键点：

-- 1. LOB数据做分区移动(带上lob子句，否则lob段不移动)
alter table "XXX"."TB_XXX_BAK" move partition P_2019_01 tablespace ts_data1 
LOB ("X1")  store as SECUREFILE (COMPRESS DEDUPLICATE LOB tablespace ts_lob1);
-- 2. 表或表分区经过‘move’操作后，需重建索引
alter index XXX.PK_XXX_BAK rebuild online;
-- 3. 表数据delete后用shrink释放占用空间（shrink跑起来是真慢）
alter table XXX.TB_XXX enable row movement ; 
alter table XXX.TB_XXX shrink space cascade; 
-- 4. 转移前判断容量，且关闭表空间自动增长（防止自动扩展把Server顶死）
alter database datafile 'XXXXX' autoextend off;
-- 5. SecureFile的CLOB压缩比，这次均值是47%