检测迁移和链接
使用ANALYZE 命令检测迁移和链接:
SQL> ANALYZE TABLE oe.orders COMPUTE STATISTICS;
Table Analyzed.
SQL> SELECT num_rows, avg_row_len, chain_cnt
2 FROM DBA_TABLES
3 WHERE table_name='ORDERS';
NUM_ROWS AVG_ROW_LEN CHAIN_CNT
---------- ----------- ----------
1171 67 83
使用Statspack/AWR 检测迁移和链接:
Statistic Total per Second per Trans
table fetch continued row 34,964 0.7 0.2
先执行utlchain.sql脚本来创建CHAINED_ROWS表
SQL> @D:\oracle\product\10.2.0\db_1\RDBMS\ADMIN\utlchain.sql
选择已迁移的行
SQL> ANALYZE TABLE oe.orders LIST CHAINED ROWS;
Table analyzed.
SQL> SELECT owner_name, table_name, head_rowid
2 FROM chained_rows
3 WHERE table_name = 'ORDERS';
OWNER_NAME TABLE_NAME HEAD_ROWID
---------- ---------- ------------------
SALES ORDER_HIST AAAAluAAHAAAAA1AAA
SALES ORDER_HIST AAAAluAAHAAAAA1AAB
消除已迁移的行
导出/导入:
– 导出表
– 删除或截断表
– 导入表
MOVE 表命令:
– ALTER TABLE EMPLOYEES MOVE
重新定义联机表
复制已迁移的行:
– 使用ANALYZE 查找迁移的行
– 将迁移的行复制到新表
– 从原始表删除迁移行
– 将行从新表复制到原始表
下面是采用MOVE表命令的方式
-- Created on 2012-5-24 by ADMINISTRATOR
declare
cursor lj is SELECT table_name, num_rows, avg_row_len, chain_cnt FROM DBA_TABLES a where a.owner='SSCP2'
and a.chain_cnt>0;
cursor sy(p_tablename in varchar2) is
select * from dba_indexes a where a.owner='SSCP2' and a.table_name=''||''||p_tablename||''||'';
sql_c varchar2(200):=null;
cl_flag number(2):=1;
begin
for r in lj loop
sql_c:=null;
sql_c:='alter table sscp2.'||r.table_name||' move';
begin
execute immediate ''||sql_c;
exception
when others then
dbms_output.put_line('执行表移动出错的表'||r.table_name);
cl_flag:=0;
end ;
if cl_flag=1 then
for l in sy(r.table_name) loop
dbms_output.put_line(l.index_name);
sql_c:=null;
sql_c:='alter index sscp2.'||l.index_name||' rebuild';
begin
execute immediate ''||sql_c;
exception
when others then
dbms_output.put_line('重建出错的索引'||l.index_name);
end ;
end loop;
sql_c:='analyze table sscp2.'||r.table_name||' compute statistics';
begin
execute immediate ''||sql_c;
exception
when others then
dbms_output.put_line('分析表出错'||r.table_name);
end ;
end if;
end loop;
end;
SQL> SELECT table_name, num_rows, avg_row_len, chain_cnt FROM DBA_TABLES a where a.owner='SSCP2'
2 and a.chain_cnt>0;
TABLE_NAME NUM_ROWS AVG_ROW_LEN CHAIN_CNT
------------------------------ ---------- ----------- ----------
ABSB 2479525 114 3041
ABS8 29230869 149 195428
IDS2 453402 60 132
ICSW 78087 37 37
ICS1 759167 49 5521
IC12 729809 111 9
IC10 256770 170 62
ADS3 17472005 186 2221
ADS1 544695 130 53
ACSN 39821 90 4
ACS6 1940197 82 162
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/26015009/viewspace-730801/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/26015009/viewspace-730801/