| avoiding chaining & migration [message #62528] |
Mon, 26 July 2004 22:07  |
Prasad
Messages: 104
Registered: October 2000
|
Senior Member |
|
|
i done analyze and found some chained rows so how to prevent it by setting storage parameter.i am using oracle 8i on solaris.Oralce recomends to keep uniform extent intial = next and pctincrease =0.If
i set this pctincrease to 0 whether i have to coalsece the tablespace manually or smon will do it.i had confusiona about it.I had
1.5 gb table found chaining if i change the storage parameters any effect or any problems
thnks in advance
prasad
|
|
|
|
|
|
|
|
| Re: avoiding chaining & migration [message #62534 is a reply to message #62530] |
Tue, 27 July 2004 09:15  |
andrew again
Messages: 2577
Registered: March 2000
|
Senior Member |
|
|
Harish is partially right. PCTFREE reserves space in data blocks to allow existing rows to increase in length (when variable length fields increases in content or null fields are assigned values). The default PCTFREE=10% means that 10% of space is reserved in the blocks for subsequent updates. If there is insufficient space in the block for an existing row to increase in length when it's updated, then row migration or row chaining can occur. If an individual row is bigger than the space in a block then row chaining is unavoidable (e.g. a large CLOB or BLOB). For large rown like this, large blocks may help.
Don't worry if you only have a small % of chained rows, you probably won't gain much by getting rid of them.
|
|
|
|
|
|
| Re: avoiding chaining & migration [message #62621 is a reply to message #62528] |
Tue, 03 August 2004 21:30 |
clio
Messages: 2
Registered: August 2004
|
Junior Member |
|
|
To prevent an ORA-1495 (specified chained row table not found), run the $ORACLE_HOME/rdbms/admin/utlchain.sql script.
TRUNCATE TABLE CHAINED_ROWS:
ANALYZE TABLE
LIST CHAINED ROWS;
2) List the Migrated or Chained rows.
From SQL*Plus:
col owner_name format a10
col table_name format a20
col head_rowid format a20
select owner_name, table_name, head_rowid from chained_rows;
3) You can now eliminate the Migrated or Chained rows by Create Table as Select (CTAS), exporting and then importing the table or by following the next steps:
A) Create an empty copy of the table that has the Migrated or Chained rows.
CREATE TABLE <temporary table name> AS SELECT * FROM
WHERE ROWID IN (SELECT HEAD_ROWID FROM CHAINED_ROWS WHERE TABLE_NAME='
');
B) Now delete the Migrated and Chained rows from the table.
DELETE FROM
WHERE ROWID IN (SELECT HEAD_ROWID FROM CHAINED_ROWS WHERE TABLE_NAME='
');
C) Insert the rows back to the table.
INSERT INTO
SELECT * FROM <temporary table name>;
Truncate the chained_rows table and drop the temporary tabl
Hope that helps,
clio_usa - OCP 8/8i/9i DBA
Oracle DBA Resources
Oracle DBA Forums
USENET Oracle Newsgroups
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
