| How do you cleanup chained rows! [message #123970] |
Wed, 15 June 2005 15:07  |
jake374
Messages: 4
Registered: June 2005
|
Junior Member |
|
|
I had a table with chained rows so I was going to rebuild it to clear them. I copied the rows off to another table and then truncated the original table. I then did an analyze on the table and it came back with 0 rows but now had a huge percentage of chained rows! How did this happen and how do I clean them up?
thanks
jake
|
|
|
|
| Re: How do you cleanup chained rows! [message #123974 is a reply to message #123970] |
Wed, 15 June 2005 15:23  |
macdba
Messages: 27
Registered: May 2005
Location: US
|
Junior Member |
|
|
1. Identify the chained rows in a table
analyze table table_name list chained rows;
2. select the output
select head_rowid from chained_rows where table_name = 'table_name';
3. insert the chained record in a temp table, delete it from the base table and again insert it back to the base table.
insert into temp_table select * from table_name where rowid = head_rowid;
delete from table_name where rowid = head_rowid;
insert into table_name select * from temp_table;
|
|
|
|
| Re: How do you cleanup chained rows! [message #123976 is a reply to message #123974] |
Wed, 15 June 2005 15:26 |
macdba
Messages: 27
Registered: May 2005
Location: US
|
Junior Member |
|
|
my earlier reply was the 1st step...but it seems u have already done it.
Check the db_block_size, it might be happening that row_size is bigger than block_size. Thats why record is unable to fit in the block
|
|
|
|
|
|
|
|
| Re: How do you cleanup chained rows! [message #123981 is a reply to message #123978] |
Wed, 15 June 2005 15:34 |
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
|
Senior Member |
|
|
| macdba wrote on Wed, 15 June 2005 16:29 |
if it is the case and u r using 9i, u can have a diff block size at the tablespace level and move that table in a new tablespace.
|
Excellent point.
|
|
|
|
|
|
| Re: How do you cleanup chained rows! [message #123987 is a reply to message #123970] |
Wed, 15 June 2005 15:58 |
jake374
Messages: 4
Registered: June 2005
|
Junior Member |
|
|
thank you for all of your replys,
I am running 9.2.0.1.0
my tablespace is set to a blocksize of 8192.
my average row length is 38
the table description is:
SERIAL NOT NULL NUMBER(16)
STARTDATE NOT NULL DATE
ENDDATE DATE
SEVERITY NUMBER(16)
STATE NUMBER(16)
SERVERNAME NOT NULL VARCHAR2(64)
SERVERSERIAL NOT NULL NUMBER(16)
where I'm confused is that this table now does not have any data in it yet it shows. 1,700,176 continued row count
this was from the statistics tab in enterprise manager
regards,
ed
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
