Home » RDBMS Server » Performance Tuning » user_segments and tablesize:some thing wrong
user_segments and tablesize:some thing wrong [message #131608] Mon, 08 August 2005 10:32 Go to next message
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 Go to previous messageGo to next message
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 #131618 is a reply to message #131608] Mon, 08 August 2005 11:57 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10702
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Are the tablespace / table storage definitions still remaining same?
Re: user_segments and tablesize:some thing wrong [message #131619 is a reply to message #131618] Mon, 08 August 2005 12:03 Go to previous message
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 .
Previous Topic: 8.1.6 control file too large, how to shrink?
Next Topic: how to split the query using rowid
Goto Forum:
  


Current Time: Fri May 27 17:42:09 CDT 2022