| user_segments and tablesize:some thing wrong [message #131608] |
Mon, 08 August 2005 10:32  |
rkl1
Messages: 97
Registered: June 2005
|
Member |
|
|
Good Morning:
I got into a very strange and confusing situation which happens often due to my very limited knowledge base and you guys have been helping out all the times. I am expecting some help and here the problem is:
we have recently upgraded to 9.2.0.6 from the previous 9.2.0.5 edition. Everything went fine for both test and prod databases. The test database is of same size as the prod database. Everything looks ok however one table which is around 11GB is not a partition table and it has around 12 million rows and use around 400,000 blocks.However when we run the table size calculation something like:
select segment_name, segment_type, sum(bytes)/1024/1024/1024 "GB" from user_segments where segment_type like 'TABLE%' group by segment_name, segment_type order by 3 desc;
we see this particular table is around 400GB however the same table in the test database shows as 12GB. All the other table show whether in prod or test show the same size. We took statistics on this table many times however it is showing the same high value only in the prod database. I validated the table using also analyze validate structure and does not seem to have any corruption. Is it possible user_segments got corrupt or the database update put some bugs on prod dbase.
|
|
|
|
| Re: user_segments and tablesize:some thing wrong [message #131612 is a reply to message #131608] |
Mon, 08 August 2005 11:09  |
JSI2001
Messages: 1016
Registered: March 2005
Location: Scotland
|
Senior Member |
|
|
Hi
I'm pretty much guessing here but
have you checked the chain_cnt?
If you have a high chain count, then move the table:
alter table table_name move.
analyze the table again
check the chain count again, if it has dropped significantly then you would appear to have suffered from migrated rows.
run the query you included again, the size should have dropped.
HTH
Jim
|
|
|
|
|
|
| Re: user_segments and tablesize:some thing wrong [message #131619 is a reply to message #131618] |
Mon, 08 August 2005 12:03 |
rkl1
Messages: 97
Registered: June 2005
|
Member |
|
|
|
Regarding the storage parameter, both test and prod are based on locally manged tablespace with uniform extent allocation of 2 MB. Recently I was told that, oracle 9.2.6.0 has a bug which corrupts the dba_extents/dba_segments if the table has been truncated.It has been pubished under Metalink Bug 4142932. Regarding the move of table as suggested, unfortunately we dont have the approval from the client to do that on the production box. However, thanks for your kind attention .
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
