-
Hi,
1. How do you know whether a row is chained or migrated? I mean either of the two.
2. Given that a table has a lot of chained/migrated rows, how can one determine the cause.
I have a table with hardly any varchar2 fields, last columns not null and it still has a lot of chained rows.
Kailash Pareek
-
Row chaining usually happens due to incorrect PCTFREE setting. There are lots of scripts available to find out
what tables have this problem.
Once found out you need to reorganise the table.
-
You should do several steps:
1. exec script utlchain.sql from
-- for UNIX - $ORACLE_HOME/rdbms/admin/
-- for NT - $ORACLE_HOME/rdbms8x/admin/
in you schema
2. execute
analize table main_table list chained rows;
3. in table CHAINED_ROWS oracle write all chained and migrating rows
4. then you can recreate all bad rows
(if length of row less then DB blocksize)
create table temp_main_table as
select *
from main_table m
where exists (select 1 from CHAINED_ROWS c
where c.owner_name = 'OOOOO' and
c.table_tame = 'MAIN_TABLE'
c.head_rowid = m.rowid);
check all rows in temp_main_table
delete from main_table m
where exists (select 1 from CHAINED_ROWS c
where c.owner_name = 'OOOOO' and
c.table_tame = 'MAIN_TABLE'
c.head_rowid = m.rowid);
insert into main_table select * from temp_main_table;
good luck.
-
Chaining and Migration are different, but both are called CHAINING in the database. Migration CAN be fixed and as prafful and Shestakov have pointed out, Chaining on the other hand indicates the DB Block size isn't large enough to accompidate a row. DB_BLOCKSIZE can only be specified at DB creation and IS NOT dynamic, so if your DB contains MOSTLY chaining and NOT migration, you may have to consider a database recreation and re-import the data. How do you tell if it CHAINING and NOT migration... after you have corrected the migrated records as seen via Shestakov's script, and you reanalyze the table and there are STILL chained rows... then these rows are CHAINED and NOT MIGRATED, and can only be resolved by recreating the database and re-importing that data.
Cheers
OCP 8i, 9i DBA
Brisbane Australia
-
if you're running 8i, then check out for the column chain_cnt in dba_tables.
Once identified, you can either adopt the method given by grjohnson or do an export/import.
Vinit
-
Originally posted by vinit:
if you're running 8i, then check out for the column chain_cnt in dba_tables
After analyzing the table(s) !
-
Originally posted by Raminder
Originally posted by vinit:
if you're running 8i, then check out for the column chain_cnt in dba_tables
After analyzing the table(s) !
ofcourse after analyze..!!
Vinit
-
If need list of tables with chained(migration) row you can use something like this:
1. analize table ... compute statistics;
2. execute query -->
!! you chould change (4096 - 110) for DB blksize and average length of block header from you database
col Prc_free format 999.99 heading '% Free' justify right ;
col len format 99999 heading 'Avg|length|record' justify right ;
col Rec_Blk format 9999.99 heading 'Records|in|blocks' justify right ;
col own format a20 heading 'Schema' justify left
;
col tbl format a20 heading 'Table|name' justify left
;
col count_ext format 9999 heading 'Total|extents' justify right ;
col nrow format 99999999 heading 'Total|records' justify right ;
col chain format 99999 heading 'Chained|records' justify right ;
col blk format 999999 heading 'Total|blocks' justify right ;
col emp format 999999 heading 'Empty|blocks' justify right ;
select a.OWNER own,
a.TABLE_NAME tbl,
a.AVG_ROW_LEN len,
a.blocks blk,
a.EMPTY_BLOCKS emp,
decode(a.blocks,0,100,(a.EMPTY_BLOCKS/(a.EMPTY_BLOCKS+a.BLOCKS))*100) Prc_free,
a.NUM_ROWS nrow,
a.CHAIN_CNT chain,
decode(a.AVG_ROW_LEN,0,0,((4096 - 110)/a.AVG_ROW_LEN)) Rec_Blk,
count(d.extent_id) count_ext
from all_all_tables a,
dba_extents d
where a.TABLE_NAME = d.segment_name and
a.OWNER = d.owner and
a.owner not in ('SYS','SYSTEM' ) and
d.segment_type = 'TABLE'
group by a.OWNER ,
a.TABLE_NAME ,
a.AVG_ROW_LEN ,
a.blocks ,
a.EMPTY_BLOCKS ,
decode(a.blocks,0,100,(a.EMPTY_BLOCKS/(a.EMPTY_BLOCKS+a.BLOCKS))*100),
a.NUM_ROWS ,
a.CHAIN_CNT ,
decode(a.NUM_ROWS,0,0,(a.CHAIN_CNT/a.NUM_ROWS)*100) ,
decode(a.AVG_ROW_LEN,0,0,((4096 - 110)/a.AVG_ROW_LEN))
having count(d.extent_id) >= 5 or a.CHAIN_CNT > 0
order by 1,2
;
col Prc_free clear
col len clear
col Rec_Blk clear
col own clear
col tbl clear
col count_ext clear
col nrow clear
col chain clear
col blk clear
col emp clear
-
Row chaining & migration
Hi,
Thanks to all those replied. I already knew that all.
I wanted to know whether one can find whether the specific problem is chaining or migration. Oracle support says that Oracle engine treats them same and there is no way to find it.
Second, having the problem, how can one find why it happened. I have following table.
CREATE TABLE vip_effort_pricing (
eitem_find_num VARCHAR2(20) NOT NULL,
effort_num NUMBER(10,0) NOT NULL,
base_um_brkpt_qty NUMBER(9,4) NOT NULL,
adv_um_brkpt_qty NUMBER(9,4) NOT NULL,
adv_um VARCHAR2(2),
adv_price NUMBER(9,2),
last_modified_dt DATE,
source_archive_dt DATE,
source_control_dt DATE
)
PCTFREE 05
PCTUSED 40
TABLESPACE modstbs01
LOGGING;
The block size is 16K. The avg_row_len, num_rows &chain_cnt for this table are 46,2263419, 137741. There is no update to first col which is part of composite keyl. The last col also always have a value. The other columns prior to it are initially blanks & updated later. But oracle is supposed to allocate space for them as the last column has value for it.
The chained rows are about 6% of total. What % is OK?
Kailash Pareek
-
6% in this table is high value.
I thing you problem is:
- PCTFREE value is very high for updatable table (95%)
- you have a lot of UPDATE statments with increase row lenght ~20-30% of initial lenght
- you should set PCTFREE ~ 80% or little bit less.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|