清理CLOB
来源：互联网 发布：淘宝直通车图片 编辑：程序博客网 时间：2024/05/24 15:39
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;--978619SQL> 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 PM25-JUN-10 05.01.55.745000 PMSQL> 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 1280SYS_IL0000209895C00006$$ 14SYS_LOB0000209895C00006$$ 4335SQL> set serveroutput onSQL> exec show_space('MESSAGEIN','MBFE','TABLE');Total Blocks ..........................163840Total Bytes ..........................1342177280Total MBytes ..........................1280Unused Blocks ..........................1711Unused Bytes ..........................14016512Unused KBytes ..........................13688Used Blocks ..........................162129Used Bytes ..........................1328160768Used KBytes ..........................1297032Last Used Ext FileId....................36Last Used Ext BlockId...................155904Last Used Block.........................6481The segment is analyzed belowFS1 Blocks (0-25) ....................0FS2 Blocks (25-50) ....................0FS3 Blocks (50-75) ....................0FS4 Blocks (75-100) ....................0Unformatted Blocks ....................0Full Blocks ....................161607PL/SQL procedure successfully completed.二.清理数据DECLARE CURSOR cur ISselect rowid as ROW_ID from MESSAGEIN awhere accepttime <= date'2012-12-31'order by rowid; V_COUNTER NUMBER;BEGIN V_COUNTER := 0; FOR row IN cur LOOPdelete 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(*)---------- 206887SQL> select max(accepttime),min(accepttime) from MESSAGEIN;MAX(ACCEPTTIME)---------------------------------------------------------------------------MIN(ACCEPTTIME)---------------------------------------------------------------------------20-OCT-13 08.52.00.461000 PM31-DEC-12 07.57.41.941000 AMSQL> set serveroutput onSQL> exec show_space('MESSAGEIN','MBFE','TABLE');Total Blocks ..........................163840Total Bytes ..........................1342177280Total MBytes ..........................1280Unused Blocks ..........................1711Unused Bytes ..........................14016512Unused KBytes ..........................13688Used Blocks ..........................162129Used Bytes ..........................1328160768Used KBytes ..........................1297032Last Used Ext FileId....................36Last Used Ext BlockId...................155904Last Used Block.........................6481The segment is analyzed belowFS1 Blocks (0-25) ....................0FS2 Blocks (25-50) ....................67FS3 Blocks (50-75) ....................163FS4 Blocks (75-100) ....................127808Unformatted Blocks ....................0Full Blocks ....................33569PL/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 1280SYS_IL0000209895C00006$$ 32SYS_LOB0000209895C00006$$ 4335表和lob字段的空间没释放:四 开始清理:SQL> select index_name,status from user_indexes where table_name='MESSAGEIN';INDEX_NAME STATUS------------------------------ --------SYS_IL0000209895C00006$$ VALIDMESSAGEIN_PK VALIDINDEXMSGIN_1 VALIDSQL> alter table MESSAGEIN move;Table altered.SQL> select index_name,status from user_indexes where table_name='MESSAGEIN';INDEX_NAME STATUS------------------------------ --------SYS_IL0000209895C00006$$ VALIDMESSAGEIN_PK UNUSABLEINDEXMSGIN_1 UNUSABLE索引失效 需要重建索引：SQL> set serveroutput onSQL> exec show_space('MESSAGEIN','MBFE','TABLE');Total Blocks ..........................163840Total Bytes ..........................1342177280Total MBytes ..........................1280Unused Blocks ..........................129961Unused Bytes ..........................1064640512Unused KBytes ..........................1039688Used Blocks ..........................33879Used Bytes ..........................277536768Used KBytes ..........................271032Last Used Ext FileId....................36Last Used Ext BlockId...................1467520Last Used Block.........................1111The segment is analyzed belowFS1 Blocks (0-25) ....................0FS2 Blocks (25-50) ....................0FS3 Blocks (50-75) ....................0FS4 Blocks (75-100) ....................0Unformatted Blocks ....................0Full Blocks ....................33717PL/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 1280SYS_IL0000209895C00006$$ 32SYS_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$$ VALIDMESSAGEIN_PK VALIDINDEXMSGIN_1 VALID 0 0
- 清理CLOB
- CLOB
- CLOB
- 清理
- 清理
- 清理
- oracle clob
- Hibernate - Clob
- ibatis Clob
- clob整理
- 存CLOB
- oracle clob
- 截取CLOB
- hibernate Clob
- clob类型
- Clob转换
- Clob类型
- clob 、blob
- 骨牌铺方格
- Nodejs处理GET请求中参数和查询参数的不同
- Concrete Mathematics(2nd Edition) - A Note on Notation
- 在PHP语言中使用JSON
- java与c++中的对象序列化 分析。
- 清理CLOB
- jquery点击空白处隐藏窗口与弹出窗口
- 黑马程序员——OC基础语法—点语法和成员变量作用域
- hadoop常用命令
- java适配器模式
- 中断详解（二）——中断描述符、任务门、中断门、陷阱门
- [iOS]一行代码给中文数组添加索引
- 【学习笔记之Android】【一】搭建Android开发环境——Windows篇
- MySQL 快速清空Magento 订单、客户、 产品 表信息
