获取rowchainandrowMigration
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了获取rowchainandrowMigration，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含2808字，纯文字阅读大概需要5分钟。
内容图文
获取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_nam
获取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
内容总结
以上是互联网集市为您收集整理的获取rowchainandrowMigration全部内容，希望文章能够帮你解决获取rowchainandrowMigration所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。