oracle 碎片清理测试

准备测环境
oem134db1> alter session set container=TEST1;
Session altered.
oem134db1> select name from v$tablespace;

NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
UNDO_2
ODM_TEMP
ODM_DATA
TBS_CUR_DAT
8 rows selected.


--创建测试表空间
oem134db1> create tablespace testdba datafile '+datadg';
Tablespace created.
oem134db1> 

--创建测试用户
create  user testdba identified by testdba default tablespace testdba;
grant dba to testdba



--生产环境模拟:创建两个表
drop table TESTDBA.XML_LOB_TABLE9;
CREATE TABLE testdba.xml_lob_table9 (
    id          NUMBER PRIMARY KEY,        
    content     clob                        
) 
tablespace testdba 
lob (content) STORE AS  basicfile  (      
    TABLESPACE testdba                      
    enable STORAGE IN ROW                   
    CHUNK 8192                              
    CACHE                                   
 );



BEGIN
  FOR i IN 1..200000 LOOP
    INSERT INTO testdba.xml_lob_table9 (id, content)
    VALUES (
      i,
      rpad('a',40000,'b')
    );
  END LOOP;
  COMMIT;
END;
/

exec dbms_stats.gather_tabLE_stats('TESTDBA','XML_LOB_TABLE9');

drop table suipian;
create table suipian as
select /*+ rule */ 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_TABLE9') --生产环境该条件可用去掉，选择表！！！！！！！！！！！
and a.table_name = c.table_name(+)
and a.table_name = b.segment_name(+)
and c.segment_name=d.segment_name;


col TABLE_OWNER for a30
col TABLE_NAME for a20
col LOB_SEGMENT_NAME for a30
set linesize 300 pagesize 300
select * from suipian;

 

set serveroutput on
begin

--普通表的碎片统计！
for cur in (select * from suipian where segment_type='TABLE' AND TAB_SEGMENT_SUBTYPE='ASSM' and table_name='XML_LOB_TABLE9') 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;

set serveroutput on
begin

--普通表的碎片统计！
for cur in (select * from suipian where segment_type='TABLE' AND TAB_SEGMENT_SUBTYPE='ASSM' and table_name='XML_LOB_TABLE9') 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;

--fs1_blocks : Number of blocks having at least 0 to 25% free space
--fs2_blocks : Number of blocks having at least 25 to 50% free space
--fs3_blocks : Number of blocks having at least 50 to 75% free space
--fs4_blocks : Number of blocks having at least 75 to 100% free space
--ful1_blocks : Total number of blocks full in the segment


--刚刚创建表：
TESTDBA.XML_LOB_TABLE9 # 62 # 1 # 0 # 0 # 35 # 1406
TESTDBA.XML_LOB_TABLE9 SYS_LOB0000024789C00002$$ # 4489 # 0 # 0 # 0 # 0 # 200000


--清理一半数据
delete from TESTDBA.XML_LOB_TABLE9 where rownum<100000;
commit;


TESTDBA.XML_LOB_TABLE9 # 62 # 1 # 4 # 4 # 734 # 699
TESTDBA.XML_LOB_TABLE9 SYS_LOB0000024789C00002$$ # 4489 # 0 # 0 # 0 # 0 # 200000


--碎片整理
alter table TESTDBA.XML_LOB_TABLE9 move online;

TESTDBA.XML_LOB_TABLE9 # 0 # 0 # 0 # 0 # 0 # 710
TESTDBA.XML_LOB_TABLE9 SYS_LOB0000024789C00002$$ # 868 # 0 # 0 # 0 # 0 # 100001

 

alter table TESTDBA.XML_LOB_TABLE9 move online lob(content) STORE AS basicfile (TABLESPACE testdba) ;

 

TESTDBA.XML_LOB_TABLE9 # 0 # 0 # 0 # 0 # 0 # 710
TESTDBA.XML_LOB_TABLE9 SYS_LOB0000024789C00002$$ # 868 # 0 # 0 # 0 # 0 # 100001