create or replace procedure show_space(v_segment_name in varchar2,
v_segment_owner in varchar2 default user,
v_segment_type in varchar2 default 'TABLE',
p_analyzed in varchar2 default 'Y',
p_partition_name in varchar2 default null) as
p_segment_name varchar2(30);
p_segment_owner varchar2(30);
p_segment_type varchar2(30);
p_space varchar2(30);
l_unformatted_blocks number;
l_unformatted_bytes number;
l_fs1_blocks number;
l_fs1_bytes number;
l_fs2_blocks number;
l_fs2_bytes number;
l_fs3_blocks number;
l_fs3_bytes number;
l_fs4_blocks number;
l_fs4_bytes number;
l_full_blocks number;
l_full_bytes number;
l_free_blks number;
l_total_blocks number;
l_total_bytes number;
l_unused_blocks number;
l_unused_bytes number;
l_lastusedextfileid number;
l_lastusedextblockid number;
l_last_used_block number;
procedure p(p_label in varchar2, p_num in number) is
begin
dbms_output.put_line(rpad(p_label, 40, '.') || p_num);
end;
begin
p_segment_name := upper(v_segment_name);
p_segment_owner := upper(v_segment_owner);
p_segment_type := upper(v_segment_type);
if (p_segment_type = 'I' or p_segment_type = 'INDEX') then
p_segment_type := 'INDEX';
elsif (p_segment_type = 'T' or p_segment_type = 'TABLE') then
p_segment_type := 'TABLE';
elsif (p_segment_type = 'C' or p_segment_type = 'CLUSTER') then
p_segment_type := 'CLUSTER';
end if;
execute immediate 'select ts.segment_space_management from dba_segments seg, dba_tablespaces ts where seg.segment_name = :p_segname and (:p_partition is null or seg.partition_name = :p_partition) and seg.owner = :p_owner and seg.tablespace_name = ts.tablespace_name'
into p_space
using p_segment_name, p_partition_name, p_partition_name, p_segment_owner;
dbms_space.unused_space(segment_owner => p_segment_owner,
segment_name => p_segment_name,
segment_type => p_segment_type,
total_blocks => l_total_blocks,
total_bytes => l_total_bytes,
unused_blocks => l_unused_blocks,
unused_bytes => l_unused_bytes,
last_used_extent_file_id => l_lastusedextfileid,
last_used_extent_block_id => l_lastusedextblockid,
last_used_block => l_last_used_block,
partition_name => p_partition_name);
p('Total Blocks ', l_total_blocks);
p('Total Bytes ', l_total_bytes);
p('Total MBytes ', l_total_bytes / 1024 / 1024);
p('Unused Blocks ', l_unused_blocks);
p('Unused Bytes ', l_unused_bytes);
p('Unused KBytes ', l_unused_bytes / 1024);
p('Used Blocks ', l_total_blocks - l_unused_blocks);
p('Used Bytes ', l_total_bytes - l_unused_bytes);
p('Used KBytes ', (l_total_bytes - l_unused_bytes) / 1024);
p('Last Used Ext FileId', l_lastusedextfileid);
p('Last Used Ext BlockId', l_lastusedextblockid);
p('Last Used Block', l_last_used_block);
if p_analyzed = 'Y' then
if p_space = 'AUTO' then
dbms_space.space_usage(segment_owner => p_segment_owner,
segment_name => p_segment_name,
segment_type => p_segment_type,
unformatted_blocks => l_unformatted_blocks,
unformatted_bytes => l_unformatted_bytes,
fs1_blocks => l_fs1_blocks,
fs1_bytes => l_fs1_bytes,
fs2_blocks => l_fs2_blocks,
fs2_bytes => l_fs2_bytes,
fs3_blocks => l_fs3_blocks,
fs3_bytes => l_fs3_bytes,
fs4_blocks => l_fs4_blocks,
fs4_bytes => l_fs4_bytes,
full_blocks => l_full_blocks,
full_bytes => l_full_bytes,
partition_name => p_partition_name);
dbms_output.put_line('');
dbms_output.put_line('The segment is analyzed below');
p('FS1 Blocks (0-25) ', l_fs1_blocks);
p('FS2 Blocks (25-50) ', l_fs2_blocks);
p('FS3 Blocks (50-75) ', l_fs3_blocks);
p('FS4 Blocks (75-100) ', l_fs4_blocks);
p('Unformatted Blocks ', l_unformatted_blocks);
p('Full Blocks ', l_full_blocks);
else
dbms_space.free_blocks(segment_owner => p_segment_owner,
segment_name => p_segment_name,
segment_type => p_segment_type,
freelist_group_id => 0,
free_blks => l_free_blks);
p('Free Blocks', l_free_blks);
end if;
end if;
end;
一. 测试准备
select count(*) from MESSAGEIN;
--978619
SQL> desc MESSAGEIN
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NOT NULL NUMBER
MSGID VARCHAR2(100)
STATUS NUMBER
PROCCNT NUMBER
ACCEPTTIME TIMESTAMP(6)
MSGBODY CLOB
QUEUE VARCHAR2(20)
SQL> select max(accepttime),min(accepttime) from MESSAGEIN;
MAX(ACCEPTTIME)
---------------------------------------------------------------------------
MIN(ACCEPTTIME)
---------------------------------------------------------------------------
20-OCT-13 08.52.00.461000 PM
25-JUN-10 05.01.55.745000 PM
SQL> select segment_name,bytes/1024/1024 from user_segments where segment_name in (
select TABLE_NAME from user_lobs where table_name='MESSAGEIN'
union all select SEGMENT_NAME from user_lobs where table_name='MESSAGEIN'
union all select index_name from user_lobs where table_name='MESSAGEIN') 2 3 4
5 ;
SEGMENT_NAME BYTES/1024/1024
------------------------------ ---------------
MESSAGEIN 1280
SYS_IL0000209895C00006$$ 14
SYS_LOB0000209895C00006$$ 4335
SQL> set serveroutput on
SQL> exec show_space('MESSAGEIN','MBFE','TABLE');
Total Blocks ..........................163840
Total Bytes ..........................1342177280
Total MBytes ..........................1280
Unused Blocks ..........................1711
Unused Bytes ..........................14016512
Unused KBytes ..........................13688
Used Blocks ..........................162129
Used Bytes ..........................1328160768
Used KBytes ..........................1297032
Last Used Ext FileId....................36
Last Used Ext BlockId...................155904
Last Used Block.........................6481
The segment is analyzed below
FS1 Blocks (0-25) ....................0
FS2 Blocks (25-50) ....................0
FS3 Blocks (50-75) ....................0
FS4 Blocks (75-100) ....................0
Unformatted Blocks ....................0
Full Blocks ....................161607
PL/SQL procedure successfully completed.
二.清理数据
DECLARE
CURSOR cur IS
select rowid as ROW_ID from MESSAGEIN a
where accepttime <= date'2012-12-31'
order by rowid;
V_COUNTER NUMBER;
BEGIN
V_COUNTER := 0;
FOR row IN cur LOOP
delete from MESSAGEIN
WHERE ROWID = row.ROW_ID;
V_COUNTER := V_COUNTER + 1;
IF (V_COUNTER >= 1000) THEN
COMMIT;
V_COUNTER := 0;
END IF;
END LOOP;
COMMIT;
END;
三:对比结果
SQL> select count(*) from MESSAGEIN;
COUNT(*)
----------
206887
SQL> select max(accepttime),min(accepttime) from MESSAGEIN;
MAX(ACCEPTTIME)
---------------------------------------------------------------------------
MIN(ACCEPTTIME)
---------------------------------------------------------------------------
20-OCT-13 08.52.00.461000 PM
31-DEC-12 07.57.41.941000 AM
SQL> set serveroutput on
SQL> exec show_space('MESSAGEIN','MBFE','TABLE');
Total Blocks ..........................163840
Total Bytes ..........................1342177280
Total MBytes ..........................1280
Unused Blocks ..........................1711
Unused Bytes ..........................14016512
Unused KBytes ..........................13688
Used Blocks ..........................162129
Used Bytes ..........................1328160768
Used KBytes ..........................1297032
Last Used Ext FileId....................36
Last Used Ext BlockId...................155904
Last Used Block.........................6481
The segment is analyzed below
FS1 Blocks (0-25) ....................0
FS2 Blocks (25-50) ....................67
FS3 Blocks (50-75) ....................163
FS4 Blocks (75-100) ....................127808
Unformatted Blocks ....................0
Full Blocks ....................33569
PL/SQL procedure successfully completed.
SQL> select segment_name,bytes/1024/1024 from user_segments where segment_name in (
select TABLE_NAME from user_lobs where table_name='MESSAGEIN'
union all select SEGMENT_NAME from user_lobs where table_name='MESSAGEIN'
union all select index_name from user_lobs where table_name='MESSAGEIN') 2 3 4 ;
SEGMENT_NAME BYTES/1024/1024
--------------------------------------------------------------------------------- ---------------
MESSAGEIN 1280
SYS_IL0000209895C00006$$ 32
SYS_LOB0000209895C00006$$ 4335
表和lob字段的空间没释放:
四 开始清理:
SQL> select index_name,status from user_indexes where table_name='MESSAGEIN';
INDEX_NAME STATUS
------------------------------ --------
SYS_IL0000209895C00006$$ VALID
MESSAGEIN_PK VALID
INDEXMSGIN_1 VALID
SQL> alter table MESSAGEIN move;
Table altered.
SQL> select index_name,status from user_indexes where table_name='MESSAGEIN';
INDEX_NAME STATUS
------------------------------ --------
SYS_IL0000209895C00006$$ VALID
MESSAGEIN_PK UNUSABLE
INDEXMSGIN_1 UNUSABLE
索引失效 需要重建索引：
SQL> set serveroutput on
SQL> exec show_space('MESSAGEIN','MBFE','TABLE');
Total Blocks ..........................163840
Total Bytes ..........................1342177280
Total MBytes ..........................1280
Unused Blocks ..........................129961
Unused Bytes ..........................1064640512
Unused KBytes ..........................1039688
Used Blocks ..........................33879
Used Bytes ..........................277536768
Used KBytes ..........................271032
Last Used Ext FileId....................36
Last Used Ext BlockId...................1467520
Last Used Block.........................1111
The segment is analyzed below
FS1 Blocks (0-25) ....................0
FS2 Blocks (25-50) ....................0
FS3 Blocks (50-75) ....................0
FS4 Blocks (75-100) ....................0
Unformatted Blocks ....................0
Full Blocks ....................33717
PL/SQL procedure successfully completed.
move 后 数据块回收:
同时清理Lob字段:
SQL> alter table MESSAGEIN modify lob(MSGBODY) (shrink space);
Table altered.
SQL> select segment_name,bytes/1024/1024 from user_segments where segment_name in (
select TABLE_NAME from user_lobs where table_name='MESSAGEIN'
union all select SEGMENT_NAME from user_lobs where table_name='MESSAGEIN'
union all select index_name from user_lobs where table_name='MESSAGEIN') 2 3 4 ;
SEGMENT_NAME BYTES/1024/1024
--------------------------------------------------------------------------------- ---------------
MESSAGEIN 1280
SYS_IL0000209895C00006$$ 32
SYS_LOB0000209895C00006$$ 952.625
此时lob空间回收
重建索引:
SQL> alter index MESSAGEIN_PK rebuild online;
Index altered.
SQL> alter index INDEXMSGIN_1 rebuild online;
Index altered.
SQL> select index_name,status from user_indexes where table_name='MESSAGEIN';
INDEX_NAME STATUS
------------------------------ --------
SYS_IL0000209895C00006$$ VALID
MESSAGEIN_PK VALID
INDEXMSGIN_1 VALID