测试记录(lob 字段碎片查看)

--生产环境模拟:创建两个表
drop table testdba.xml_lob_table6;
CREATE TABLE testdba.xml_lob_table6 (
    id          NUMBER PRIMARY KEY,         -- 主键列
    xml_content XMLTYPE                     -- XMLType 列
) 
XMLTYPE xml_content STORE AS  basicfile CLOB (         -- 明确指定存储为 CLOB
    TABLESPACE testdba                                 -- 可选：指定表空间
    enable STORAGE IN ROW                              -- 可选：禁用行内存储（大对象独立存储）
    CHUNK 8192                                         -- 可选：设置 LOB 块大小
    CACHE                                              -- 可选：启用缓存
 ) tablespace testdba ;

BEGIN
  FOR i IN 1..200000 LOOP
    INSERT INTO testdba.xml_lob_table6 (id, xml_content)
    VALUES (
      i,
      XMLTYPE(
        '<record>
           <id>' || i || '</id>
           <name>User_' || i || '</name>
           <timestamp>' || TO_CHAR(SYSTIMESTAMP, 'YYYY-MM-DD HH24:MI:SS.FF6') || '</timestamp>
         </record>'
      )
    );
  END LOOP;
  COMMIT;
END;
/
drop table testdba.xml_lob_table7;
CREATE TABLE testdba.xml_lob_table7 (
    id          NUMBER PRIMARY KEY,                    -- 主键列
    xml_content XMLTYPE                                -- XMLType 列
) 
XMLTYPE xml_content STORE AS  basicfile CLOB (         -- 明确指定存储为 CLOB
    TABLESPACE testdba                                 -- 可选：指定表空间
    disable STORAGE IN ROW                             -- 可选：禁用行内存储（大对象独立存储）
    CHUNK 8192                                         -- 可选：设置 LOB 块大小
    CACHE                                              -- 可选：启用缓存
 ) tablespace testdba ;

BEGIN
  FOR i IN 1..200000 LOOP
    INSERT INTO testdba.xml_lob_table7 (id, xml_content)
    VALUES (
      i,
      XMLTYPE(
        '<record>
           <id>' || i || '</id>
           <name>User_' || i || '</name>
           <timestamp>' || TO_CHAR(SYSTIMESTAMP, 'YYYY-MM-DD HH24:MI:SS.FF6') || '</timestamp>
         </record>'
      )
    );
  END LOOP;
  COMMIT;
END;
/

drop table testdba.xml_lob_table9 ;
CREATE TABLE testdba.xml_lob_table9 (
    id          NUMBER PRIMARY KEY,                    -- 主键列
    xml_content XMLTYPE        ,                       -- XMLType 列
    xml_content1 XMLTYPE 
) 
XMLTYPE xml_content STORE AS  basicfile CLOB (         -- 明确指定存储为 CLOB
    TABLESPACE testdba                                 -- 可选：指定表空间
    disable STORAGE IN ROW                             -- 可选：禁用行内存储（大对象独立存储）
    CHUNK 8192                                         -- 可选：设置 LOB 块大小
    CACHE                                              -- 可选：启用缓存
 )  
XMLTYPE xml_content1 STORE AS  basicfile CLOB (         -- 明确指定存储为 CLOB
    TABLESPACE testdba                                  -- 可选：指定表空间
    disable STORAGE IN ROW                              -- 可选：禁用行内存储（大对象独立存储）
    CHUNK 8192                                          -- 可选：设置 LOB 块大小
    CACHE                                               -- 可选：启用缓存
 ) tablespace testdba ; 

BEGIN
  FOR i IN 1..200000 LOOP
    INSERT INTO testdba.xml_lob_table9 (id, xml_content,xml_content1)
    VALUES (
      i,
      XMLTYPE(
        '<record>
           <id>' || i || '</id>
           <name>User_' || i || '</name>
           <timestamp>' || TO_CHAR(SYSTIMESTAMP, 'YYYY-MM-DD HH24:MI:SS.FF6') || '</timestamp>
         </record>'
      ),
      XMLTYPE(
        '<record>
           <id>' || i || '</id>
           <name>User_' || i || '</name>
           <timestamp>' || TO_CHAR(SYSTIMESTAMP, 'YYYY-MM-DD HH24:MI:SS.FF6') || '</timestamp>
         </record>'
      )
    );
  END LOOP;
  COMMIT;
END;
/

begin
   dbms_stats.gather_table_stats('TESTDBA',upper('xml_lob_table6'));
end;
/
begin
   dbms_stats.gather_table_stats('TESTDBA',upper('xml_lob_table7'));
end;
/
begin
   dbms_stats.gather_table_stats('TESTDBA',upper('xml_lob_table9'));
end;
/

--创建临时表
drop table suipian;
create table suipian as 
select a.owner table_owner,
       a.table_name,a.tablespace_name,
       b.segment_type,
       b.segment_subtype tab_segment_subtype,
       trunc(b.bytes/1024/1024,3) tab_size_m,
       trunc(a.num_rows*a.avg_row_len/1024/1024) tab_size_m1,
       c.segment_name lob_segment_name,
       c.in_row,
       d.segment_type lob_segment_type,d.segment_subtype lob_segment_subtype,trunc(d.bytes/1024/1024,3) lob_size_m
  from dba_tables a, dba_segments b, dba_lobs c,dba_segments d
 where a.owner = b.owner
   and a.owner = c.owner
   and a.table_name in ('XML_LOB_TABLE6','XML_LOB_TABLE7')
   and a.table_name = c.table_name(+)
   and a.table_name = b.segment_name(+)
   and c.segment_name=d.segment_name;
   
--碎片获取 
set serveroutput on
begin
   --普通表的碎片统计！
   for cur in (select * from suipian where segment_type='TABLE' AND TAB_SEGMENT_SUBTYPE='ASSM') loop
        declare
        v_unformatted_blocks number;
        v_unformatted_bytes number;
        v_fs1_blocks number;
        v_fs1_bytes number;
        v_fs2_blocks number;
        v_fs2_bytes number;
        v_fs3_blocks number;
        v_fs3_bytes number;
        v_fs4_blocks number;
        v_fs4_bytes number;
        v_full_blocks number;
        v_full_bytes number;
        begin
        dbms_space.space_usage (cur.table_owner, cur.table_name, 'TABLE', v_unformatted_blocks,
        v_unformatted_bytes, v_fs1_blocks, v_fs1_bytes, v_fs2_blocks, v_fs2_bytes,
        v_fs3_blocks, v_fs3_bytes, v_fs4_blocks, v_fs4_bytes, v_full_blocks, v_full_bytes);
        --dbms_output.put_line(cur.table_owner||'.'||cur.table_name||' Unformatted Blocks = '||v_unformatted_blocks ||' FS1 Blocks = '||v_fs1_blocks||' FS2 Blocks = '||v_fs2_blocks||' FS3 Blocks = '||v_fs3_blocks||' FS4 Blocks = '||v_fs4_blocks||' Full Blocks = '||v_full_blocks);
        dbms_output.put_line(cur.table_owner||'.'||cur.table_name||' # '||v_unformatted_blocks ||' # '||v_fs1_blocks||' # '||v_fs2_blocks||' # '||v_fs3_blocks||' # '||v_fs4_blocks||' # '||v_full_blocks);
        --dbms_output.put_line('FS1 Blocks = '||v_fs1_blocks);
        --dbms_output.put_line('FS2 Blocks = '||v_fs2_blocks);
        --dbms_output.put_line('FS3 Blocks = '||v_fs3_blocks);
        --dbms_output.put_line('FS4 Blocks = '||v_fs4_blocks);
        --dbms_output.put_line('Full Blocks = '||v_full_blocks);
        end;
   end loop;
   --lob字段的碎片梳理
   for cur1 in (select * from suipian a where a.lob_segment_subtype='ASSM' and a.lob_segment_name is not null) loop
        declare
        v_unformatted_blocks number;
        v_unformatted_bytes number;
        v_fs1_blocks number;
        v_fs1_bytes number;
        v_fs2_blocks number;
        v_fs2_bytes number;
        v_fs3_blocks number;
        v_fs3_bytes number;
        v_fs4_blocks number;
        v_fs4_bytes number;
        v_full_blocks number;
        v_full_bytes number;
        begin
        dbms_space.space_usage (cur1.table_owner, cur1.lob_segment_name, 'LOB', v_unformatted_blocks,
        v_unformatted_bytes, v_fs1_blocks, v_fs1_bytes, v_fs2_blocks, v_fs2_bytes,
        v_fs3_blocks, v_fs3_bytes, v_fs4_blocks, v_fs4_bytes, v_full_blocks, v_full_bytes);
        --dbms_output.put_line(cur1.table_owner||'.'||cur1.table_name||' '||cur1.lob_segment_name ||' Unformatted Blocks = '||v_unformatted_blocks ||' FS1 Blocks = '||v_fs1_blocks||' FS2 Blocks = '||v_fs2_blocks||' FS3 Blocks = '||v_fs3_blocks||' FS4 Blocks = '||v_fs4_blocks||' Full Blocks = '||v_full_blocks);
        dbms_output.put_line(cur1.table_owner||'.'||cur1.table_name||' '||cur1.lob_segment_name ||' # '||v_unformatted_blocks ||' # '||v_fs1_blocks||' # '||v_fs2_blocks||' # '||v_fs3_blocks||' # '||v_fs4_blocks||' # '||v_full_blocks);
        --dbms_output.put_line('Unformatted Blocks = '||v_unformatted_blocks);
        --dbms_output.put_line('FS1 Blocks = '||v_fs1_blocks);
        --dbms_output.put_line('FS2 Blocks = '||v_fs2_blocks);
        --dbms_output.put_line('FS3 Blocks = '||v_fs3_blocks);
        --dbms_output.put_line('FS4 Blocks = '||v_fs4_blocks);
        --dbms_output.put_line('Full Blocks = '||v_full_blocks);
        end;
   end loop;
end;
/ 


--其他: 下面的可参考，但某些时候不准确
SELECT table_name,
       ROUND((blocks * 8), 2) / 1024 "High Water Mark (HWM)  M",
       ROUND((num_rows * avg_row_len / 1024), 2) / 1024 "Allocated Space M",
       ROUND((blocks * 10 / 100) * 8, 2) / 1024 "(pctfree) M",
       ROUND( (blocks * 8- (num_rows * avg_row_len / 1024) - blocks * 8 * 10 / 100), 2) / 1024 "Wasted Space M"
  FROM dba_tables
 WHERE table_name = upper('xml_lob_table1')
 ORDER BY 5 DESC;