Embedded Files
VALIDATE STRUCTURE
VALIDATE STRUCTURE
ANALYZE TABLE mytable VALIDATE STRUCTURE;
CHAINED ROWS
CHAINED ROWS
To identify chained rows in a table...
ANALYZE TABLE mytable LIST CHAINED ROWS INTO chained_rows;
SELECT *
FROM chained_rows;
(Create chained_rows using UTLCHAIN.SQL)@$ORACLE_HOME/rdbms/admin/UTLCHAIN.SQL
To count the number of chained rows accessed since database startup...
SELECT SUBSTR(st.name,1,40),
sy.value,
sy.statistic#
FROM v$statname st,
v$sysstat sy
WHERE st.statistic# = sy.statistic#
AND st.name = 'table fetch continued row';
Eliminate Chained Rows
Eliminate Chained Rows
NOTE: this is old documentation that I wrote last century and cut & paste here... there may be better ways of dealing with this now. TODO
If the number of chained rows in a table is small compared to the total number of rows in the table:
Move the rows of data into an intermediate table with the same column structure as the original table by using the HEAD_ROWID in the CHAINED_ROWS table.
DELETE the moved rows of data again by using the HEAD_ROWID in the CHAINED_ROWS table.
INSERT the rows from the intermediate table back into the original table.
DROP the intermediate table.
DELETE the record in the CHAINED_ROWS table.
If the number of chained rows in a table is the majority of rows in the table then one of the two options below is true:
DB_BLOCK_SIZE is smaller than the row length (Database Chaining)
Database Chaining requires rebuilding the entire database with a larger block size if possible - or changing the design of the table(s) involved (and all programs, scripts etc that are dependant on them).
PCTFREE is incorrect for the table, in which case...
Determine a better percent free factor for the table
Export the entire table with all its dependencies (i.e. indexes, grants, constraints)
DROP the original table
Re-create it with the new specification.
Import the data with its dependencies.
Page updated
Google Sites
Report abuse