oracle碎片清理

練習一：整理行遷移
任務  描述
一、準備測試環境  1、建立測試表
SQL> create table ora1(id number);
表已建立。
2、建立儲存過程，向表中插入10000條記錄
SQL> create or replace procedure sp_insert_ora1
2 is
3 begin
4 for i in 1..10000 loop
5 insert into ora1 values(i);
6 end loop;
7* end;
SQL> /
過程已建立。
3、執行儲存過程
SQL> exec sp_insert_ora1
PL/SQL 過程已成功完成。
4、要察看資料字典中的資訊，一定要先更新它。
SQL> analyze table ora1 estimate statistics;
表已分析。
5、察看ora1表使用了多少個塊
SQL> select blocks from dba_tables where table_name='ORA1';
BLOCKS 
----------20 
6、察看錶中有多少行，並且行遷移數量
SQL> select num_rows,chain_cnt from dba_tables where table_name ='ORA1';
NUM_ROWS CHAIN_CNT 
---------- ----------10000 0 
二、導致行遷移  1、插入一列，更新資料，產生行遷移情況。並察看相關資訊
SQL> alter table ora1 add name varchar2(100);
表已更改。
2、檢視錶結構
SQL> desc ora1
名稱  空?  型別 
----------------------------------------- ------------------------------------ID NUMBER
NAME    VARCHAR2(100)
3、執行update操作，肯定會引起行遷移
SQL> update ora1 set name =to_char(id)||'ddddddddddd';
已更新10000行。
4、沒有更新資料字典中的資訊，所以沒有變化。
SQL> select num_rows,chain_cnt from dba_tables where table_name ='ORA1';
NUM_ROWS CHAIN_CNT 
---------- ----------10000 0 
5、重新更新資料字典資訊
SQL> analyze table ora1 estimate statistics;
表已分析。
6、塊的個數增加了。
SQL> select blocks from dba_tables where table_name='ORA1';
BLOCKS 
---------- 
55 
7、發現10000條紀錄中，有9051條發生了行遷移
SQL> select num_rows,chain_cnt from dba_tables where table_name ='ORA1';
NUM_ROWS CHAIN_CNT   
---------- ----------10000 9051 
8、AVG_SPACE每個塊中的平均位元組。
SQL> select 
num_rows,blocks,empty_blocks ,avg_space,chain_cnt,avg_row_len from d
ba_tables where table_name='ORA1';
NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE CHAIN_CNT AVG_ROW_LEN
---------- ---------- ------------ ---------- ---------- -----------10000 55 0 783 9051 28
三、使用臨時表整
理行遷移
1、建立chained_rows ，使用這個表可以幫助我們察看哪些紀錄有行遷移
SQL> @?\rdbms\admin\utlchain.sql
表已建立。
2、將哪行有行遷移，寫入到CHAINED_ROWS中 
SQL> ANALYZE TABLE ORA1 LIST CHAINED ROWS;
表已分析。
3、察看哪些行有遷移情況
SQL> SELECT owner_name, table_name, head_rowid FROM chained_rows WHERE 
table_name = 'ORA1' ;
OWNER_NAME TABLE_NAME 
HEAD_ROWID 
------------------------------ ------------------------------------------------A ORA1 
AAAAzNAABAAABt8ABL
A    ORA1 
AAAAzNAABAAABt8ABM
A ORA1 
AAAAzNAABAAABt8ABN
…………
…………省略很多。
已選擇9051行。
4、將有行遷移的資料複製到其他表，然後整理行遷移
SQL> CREATE table tmp
2 as
3 select * from ora1
4 where rowid in(select HEAD_ROWID from CHAINED_ROWS);
表已建立。
5、刪除有行遷移的記錄
SQL> delete from ora1
2 where rowid in(select HEAD_ROWID from CHAINED_ROWS);
已刪除9051行。
6、將行遷移資料重新寫回表中
SQL> insert into ora1 select * from tmp;
已建立9051行。
7、提交
SQL> commit;
提交完成。
8、刪除臨時表
SQL> DROP table tmp;
9、重新察看一下，發現行遷移沒了
SQL> analyze table ora1 estimate statistics;
表已分析。
10、檢視行遷移相關資訊
SQL> select num_rows,chain_cnt from dba_tables where table_name ='ORA1';
NUM_ROWS CHAIN_CNT 
---------- ----------10000 0 
11、檢視塊的使用資訊
SQL> select blocks from dba_tables where table_name='ORA1';
BLOCKS 
----------55   
四、使用 MOVE
方法整理碎片
1、刪除表
SQL> drop table ora1;
表已丟棄。
2、建立測試表
SQL> create table ora1(id number);
表已建立。
3、執行儲存過程
SQL> exec sp_insert_ora1
PL/SQL 過程已成功完成。
4、要察看資料字典中的資訊，一定要先更新它。
SQL> analyze table ora1 estimate statistics;
表已分析。
5、察看ora1表使用了多少個塊
SQL> select blocks from dba_tables where table_name='ORA1';
BLOCKS 
---------- 
20
6、察看錶中有多少行，並且行遷移數量
SQL> select num_rows,chain_cnt from dba_tables where table_name ='ORA1';
NUM_ROWS CHAIN_CNT   
---------- ----------10000 0 
7、插入一列，更新資料，產生行遷移情況。並察看相關資訊 
SQL> alter table ora1 add name varchar2(100);
表已更改。
8、執行update操作，肯定會引起行遷移
SQL> update ora1 set name =to_char(id)||'ddddddddddd';
已更新10000行。
9、沒有更新資料字典中的資訊，所以沒有變化。
SQL> select num_rows,chain_cnt from dba_tables where table_name ='ORA1';
NUM_ROWS CHAIN_CNT 
---------- ---------- 
10000 0 
10、重新更新資料字典資訊
SQL> analyze table ora1 estimate statistics;
表已分析。
11、塊的個數增加了。
SQL> select blocks from dba_tables where table_name='ORA1';
BLOCKS 
----------55 
12、發現10000條紀錄中，有9051條發生了行遷移
SQL> select num_rows,chain_cnt from dba_tables where table_name ='ORA1';
NUM_ROWS CHAIN_CNT 
---------- ---------- 
10000 9051
13、將表移動，移動時就自動整理遷移和碎片，
SQL> alter table ora1 move ;
表已更改。
14、分析表，統計資訊
SQL> analyze table ora1 estimate statistics;
表已分析。
15、重新察看一下，發現行遷移沒了
SQL> select 
num_rows,blocks,empty_blocks ,avg_space,chain_cnt,avg_row_len from 
dba_tables where table_name='ORA1';
NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE CHAIN_CNT AVG_ROW_LEN
---------- ---------- ------------ ---------- ---------- ----------- 
10000 35 12 996 0 23
五、使用pctfree
引數，可以一定程
度減少行遷移
1、刪除表
SQL> drop table ora1;
表已丟棄。
2、建立測試表
SQL> create table ora1(id number) pctfree 40;
表已建立。
3、執行儲存過程
SQL> exec sp_insert_ora1
PL/SQL 過程已成功完成。
4、要察看資料字典中的資訊，一定要先更新它。
SQL> analyze table ora1 estimate statistics;
表已分析。
5、察看ora1表使用了多少個塊(塊的數量前面多了些)
SQL> select blocks from dba_tables where table_name='ORA1';
BLOCKS 
----------25
6、察看錶中有多少行，並且行遷移數量
SQL> select num_rows,chain_cnt from dba_tables where table_name ='ORA1';
NUM_ROWS CHAIN_CNT 
---------- ----------10000 0 
7、插入一列，更新資料，產生行遷移情況。並察看相關資訊
SQL> alter table ora1 add name varchar2(100);
表已更改。
8、執行update操作，肯定會引起行遷移
SQL> update ora1 set name =to_char(id)||'ddddddddddd';
已更新10000行。
9、沒有更新資料字典中的資訊，所以沒有變化。
SQL> select num_rows,chain_cnt from dba_tables where table_name ='ORA1';
NUM_ROWS CHAIN_CNT   
---------- ----------10000 0 
10、重新更新資料字典資訊 
SQL> analyze table ora1 estimate statistics;
表已分析。
11、塊的個數增加了。
SQL> select blocks from dba_tables where table_name='ORA1';
BLOCKS 
----------55 
12、行遷移的數量明顯少多了,原來是9051個行遷移
SQL> select num_rows,chain_cnt from dba_tables where table_name ='ORA1';
NUM_ROWS CHAIN_CNT 
---------- ----------10000    4603
練習二、整理索引碎片
任務  描述
一、準備測試資料  1、建立表、插入記錄和建立索引
SQL> create table ind (id number,name varchar2(100));
表已建立。
2、建立儲存過程，向表中插入10000條記錄
SQL> create or replace procedure sp_insert_ind
2 is
3 begin
4 for i in 1..10000 loop
5 insert into ind values(i,to_char(i)||'aaaaaaaaaa');
6 end loop;
7 end;
8 /
過程已建立。
3、執行儲存過程
SQL> exec sp_insert_ind
PL/SQL 過程已成功完成。
4、建立索引
SQL> create index ind_id_idx on ind(id);
索引已建立。
5、收集資訊，沒有更新資料字典，所以沒有資訊
SQL> select lf_rows,lf_rows_len,del_lf_rows,del_lf_rows_len from 
index_stats;
未選定行
6、更新資料字典
SQL> ANALYZE INDEX ind_id_idx VALIDATE STRUCTURE;
索引已分析
7、檢視索引中碎片情況
SQL> select lf_rowsdel_lf_rows from index_stats;
LF_ROWS DEL_LF_ROWS 
----------  ---------------10000 0 
二、產生碎片  1、更新表中1000行記錄，這時會更新索引樹
SQL> update ind set id=id+1 where id>9000;
已更新1000行。
2、分析索引資訊
SQL> ANALYZE INDEX ind_id_idx VALIDATE STRUCTURE;
索引已分析
3、檢視索引碎片資訊
--總共行的數量增加了1000行，並且標示為刪除了1000行記錄
SQL> select lf_rows,del_lf_rows from index_stats;
LF_ROWS DEL_LF_ROWS 
----------  -----------11000 1000 
三、使用 rebuild
方法重建索引
1、重建索引
SQL> alter index ind_id_idx rebuild;
索引已更改。
2、分析索引資訊
SQL> ANALYZE INDEX ind_id_idx VALIDATE STRUCTURE;
索引已分析
3、檢視索引碎片資訊，發現索引中碎片沒有了
SQL> select lf_rows,del_lf_rows from index_stats;
LF_ROWS DEL_LF_ROWS 
---------- -----------10000 0 
四、產生碎片  1、刪除表中1000行記錄，這時會更新索引樹 
SQL> delete from ind where id>9000;
已刪除1000行。
2、分析索引資訊
SQL> ANALYZE INDEX ind_id_idx VALIDATE STRUCTURE;
索引已分析
3、檢視索引碎片資訊
--總共行的數量增加了1000行，並且標示為刪除了1000行記錄
SQL> select lf_rows,del_lf_rows from index_stats;
LF_ROWS DEL_LF_ROWS 
----------  -----------10000 1000 
五、使用 coalesce
方法重建索引
1、重建索引
SQL> alter index ind_id_idx rebuild online;
索引已更改。
2、分析索引資訊
SQL> ANALYZE INDEX ind_id_idx VALIDATE STRUCTURE;
索引已分析
3、檢視索引碎片資訊，發現索引中碎片沒有了
SQL> select lf_rows,del_lf_rows from index_stats;
LF_ROWS DEL_LF_ROWS 
---------- -----------9000 0 
六、產生碎片  1、刪除表中1000行記錄，這時會更新索引樹
SQL> delete from ind where id>8000;
已刪除1000行。
2、分析索引資訊
SQL> ANALYZE INDEX ind_id_idx VALIDATE STRUCTURE;
索引已分析
3、檢視索引碎片資訊
--總共行的數量增加了1000行，並且標示為刪除了1000行記錄
SQL> select lf_rows,del_lf_rows from index_stats;
LF_ROWS DEL_LF_ROWS 
----------  -----------9000 1000 
七、使用 coalesce 1、重建索引 
方法重建索引  SQL> alter index ind_id_idx coalesce;
索引已更改。
2、分析索引資訊
SQL> ANALYZE INDEX ind_id_idx VALIDATE STRUCTURE;
索引已分析
3、檢視索引碎片資訊，發現索引中碎片沒有了
SQL> select lf_rows,del_lf_rows from index_stats;
LF_ROWS DEL_LF_ROWS 
---------- ----------- 
8000 0