OraFAQ Forum: Performance Tuning » user_segments and tablesize:some thing wrong

Home » RDBMS Server » Performance Tuning » user_segments and tablesize:some thing wrong

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

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.

Report message to a moderator

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

Report message to a moderator

Re: user_segments and tablesize:some thing wrong [message #131618 is a reply to message #131608]

Mon, 08 August 2005 11:57

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

Are the tablespace / table storage definitions still remaining same?

Report message to a moderator

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 .

Report message to a moderator

Previous Topic:	8.1.6 control file too large, how to shrink?
Next Topic:	how to split the query using rowid

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 17 00:31:58 CDT 2025