当前位置:操作系统 > Unix/Linux >>

获取row chain and row Migration

获取row chain and row Migration
 
获取row chain and row Migration
1.使用易做图yze对相应的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  易做图yze_timestamp  DATE );
用于存储链接行的信息
 
eg:
SQL> ANALYZE TABLE oe.orders LIST CHAINED ROWS;
Table 易做图yzed.
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
 
CopyRight © 2022 站长资源库 编程知识问答 zzzyk.com All Rights Reserved
部分文章来自网络,