获取row chain and row migration
获取row chain and row migration
1.使用analyze对相应的object分析
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
2.也可是使用以下方法获取migrated rows：
analyze table … list chained rows ------不会覆盖当前统计信息
在使用以上命令时需要执行utlchain.sql这个脚本，也可以手工执行：
sql> create table chained_rows (
2 owner_name varchar2(30),
3 table_name varchar2(30),
4 cluster_name varchar2(30),
5 partition_name varchar2(30),
6 head_rowid rowid,
7 analyze_timestamp date );
用于存储链接行的信息
eg：
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
...
消除行迁移：
? export/import:
– export the table.
– drop or truncate the table.
– import the table.
? move table command:
– alter table employees move
所有index在操作后需要rebuilt
move table command is faster than export and impor t.
但是前提是有足够的空间。
? online table redefinition
使用 dbms_redefinition 包需要足够空间。
? copy migrated rows:
– find migrated rows by using analyze.
– copy migrated rows to a new table.
– delete migrated rows from the original table.
– copy rows from the new table to the original table.
注意，是否需要禁用相应的外键约束，trigger ，row-level security, and auditing.
script：
/* clean up from last execution */
set echo off
drop table migrated_rows;
drop table chained_rows;
/* create the chained_rows table */
@?/rdbms/admin/utlchain
set echo on
spool fix_mig
/* list the chained & migrated rows */
analyze table &table_name list chained rows;
/* copy the chained/migrated rows to another table */
create table migrated_rows as
select orig.*
from &table_name orig, chained_rows cr
where orig.rowid = cr.head_rowid
and cr.table_name = upper('&table_name');
/* delete the chained/migrated rows from the original table */
delete from &table_name
where rowid in (
select head_rowid
from chained_rows);
/* copy the chained/migrated rows back into the original table */
insert into &table_name
select *
from migrated_rows;
spool off