诊断和解决row chained and migrated
来源:互联网 发布:天涯明月刀ol数据 编辑:程序博客网 时间:2024/06/05 04:12
Listing Chained Rows of Tables and Clusters
1.execute the UTLCHAIN.SQL or UTLCHN1.SQL script.
脚本位于:@oracle_home\rdbms\admin\
该脚本内容:
rem
rem $Header: utlchain.sql 07-may-96.19:40:01 sbasu Exp $
rem
Rem Copyright (c) 1990, 1995, 1996, 1998 by Oracle Corporation
Rem NAME
REM UTLCHAIN.SQL
Rem FUNCTION
Rem Creates the default table for storing the output of the
Rem analyze list chained rows command
Rem NOTES
Rem MODIFIED
Rem syeung 06/17/98 - add subpartition_name
Rem mmonajje 05/21/96 - Replace timestamp col name with analyze_timestam
Rem sbasu 05/07/96 - Remove echo setting
Rem ssamu 08/14/95 - merge PTI with Objects
Rem ssamu 07/24/95 - add field for partition name
Rem glumpkin 10/19/92 - Renamed from CHAINROW.SQL
Rem ggatlin 03/09/92 - add set echo on
Rem rlim 04/29/91 - change char to varchar2
Rem Klein 01/10/91 - add owner name for chained rows
Rem Klein 12/04/90 - Creation
Rem
create table CHAINED_ROWS (
owner_name varchar2(30),
table_name varchar2(30),
cluster_name varchar2(30),
partition_name varchar2(30),
subpartition_name varchar2(30),
head_rowid rowid,
analyze_timestamp date
);
2、执行分析
ANALYZE CLUSTER emp_dept LIST CHAINED ROWS INTO CHAINED_ROWS;
ANALYZE TABLE order_hist LIST CHAINED ROWS;
3.查询分析结果。
SELECT *
FROM CHAINED_ROWS
WHERE TABLE_NAME = 'ORDER_HIST';
OWNER_NAME TABLE_NAME CLUST... HEAD_ROWID TIMESTAMP
---------- ---------- -----... ------------------ ---------
SCOTT ORDER_HIST ... AAAAluAAHAAAAA1AAA 04-MAR-96
SCOTT ORDER_HIST ... AAAAluAAHAAAAA1AAB 04-MAR-96
SCOTT ORDER_HIST ... AAAAluAAHAAAAA1AAC 04-MAR-96
The output lists all rows that are either migrated or chained.
4.创建一张中介表,临时存储 migrated or chained 的行数据
Create an intermediate table with the same columns as the existing table to hold
the migrated and chained rows:
CREATE TABLE int_order_hist
AS SELECT *
FROM order_hist
WHERE ROWID IN
(SELECT HEAD_ROWID
FROM CHAINED_ROWS
WHERE TABLE_NAME = 'ORDER_HIST');
5. 将原表中的migrated and chained rows 删除
Delete the migrated and chained rows from the existing table:
DELETE FROM order_hist
WHERE ROWID IN
(SELECT HEAD_ROWID
FROM CHAINED_ROWS
WHERE TABLE_NAME = 'ORDER_HIST');
6.将中介表中的行再次插入原表。
Insert the rows of the intermedia te table into the existing table:
INSERT INTO order_hist
SELECT *
FROM int_order_hist;
7.干掉中介表.
Drop the intermediate table:
DROP TABLE int_order_history;
8.删除掉chained_rows表中信息。
Delete the information collected in step 1 from the output table:
DELETE FROM CHAINED_ROWS
WHERE TABLE_NAME = 'ORDER_HIST';
--------如果要想彻底解决chained rows ,需要增大data block size 。
row chained 在大多数情况下太可能避免,当表有long or large CHAR or VARCHAR2 字段时几乎不可能避免。
- 诊断和解决row chained and migrated
- Chained row
- 笔记(060623),chained row,speed of long raw data retriving
- Ask Tom 'Chained Rows and Autotrace'
- 诊断和解决ORA-04031问题【转】
- 如何诊断和解决db2问题
- 如何诊断和解决db2问题
- 诊断和解决错误ORA-04031
- 如何诊断和解决high version count
- row migeration and row chain
- Row Chaining and Row Migration
- Row Chaining and Row Migration
- [Migrated]榜样
- Searching: Chained Hash Table Search And Insertion-2
- Searching: Chained Hash Table Search And Insertion-1
- Router 和 SMTP 宕机问题的诊断和解决
- 14.9 InnoDB Row Storage and Row Formats InnoDB 行存储和行格式:
- Oracle诊断案例---如何诊断和解决CPU高度消耗(100%)的数据库问题
- 机房收费系统合作版总结
- Android Settings 源码研究---导入eclipse
- ie的第三方cookies跟踪,p3p协议
- java在线预览txt、word、ppt、execel,pdf代码
- yii 杂谈
- 诊断和解决row chained and migrated
- 利用map和cod文件查出崩溃代码行
- stray '\161' in program
- 未在本地计算机上注册“microsoft.ACE.oledb.12.0”提供程序
- textview隐蔽
- c/c++随机取值
- JS 时间差计算
- oracle 行转列
- PDB文件:每个开发人员都必须知道的