OraFAQ Forum: Server Administration » how to find fastest growing object

Home » RDBMS Server » Server Administration » how to find fastest growing object (9i/10g)

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

how to find fastest growing object [message #379061]

Sun, 04 January 2009 05:12

oradbaexp
Messages: 16
Registered: January 2009

Junior Member

Hi

How can we find the object (table/indexes etc) which is causing the tablespace growth? or in other words How to find the fastest growing segment/segments in a tablespace?

The case:(though hypothetical, but close to Real)

Db size : around 1 TB
Tablespace size: 200 GB

In two days we added 10 datafiles of aroung 1.5 GB each because of space shortage.
We found the largest segments from dba_segments and dba_data_files, but the question is :just the largest segment may not be the segment/segments that are consuming spaces.There can be many small sized objects which are getting created and consuming space.

Thanks.. in advance.

Regards...

Report message to a moderator

Re: how to find fastest growing object [message #379062 is a reply to message #379061]

Sun, 04 January 2009 05:50

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Query dba_segments each day and compare the sizes.

Regards
Michel

Report message to a moderator

Re: how to find fastest growing object [message #379067 is a reply to message #379062]

Sun, 04 January 2009 06:33

oradbaexp
Messages: 16
Registered: January 2009

Junior Member

thanks...

But i said that we found. But when there is constant eating away of space by some segemnts/processes which we dont know,people cannot wait to compare data for 2 -3 days.
Is there any other way?

Like any tool, query, procedure?

Thanks..
Regards..

Report message to a moderator

Re: how to find fastest growing object [message #379070 is a reply to message #379067]

Sun, 04 January 2009 07:11

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

Senior Member
Account Moderator

http://download-east.oracle.com/docs/cd/B13789_01/server.101/b10755/statviews_2143.htm

Look into the delta columns between any two dates (intervals).
Information available is dependent on statistics and after the last instance startup.

Report message to a moderator

Re: how to find fastest growing object [message #379073 is a reply to message #379070]

Sun, 04 January 2009 08:13

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Note that DBA_HIST views can only be queried if you had paid for Diagnostics Pack.

Regards
Michel

[Updated on: Sun, 04 January 2009 08:13]

Report message to a moderator

Re: how to find fastest growing object [message #379281 is a reply to message #379073]

Mon, 05 January 2009 22:29

oradbaexp
Messages: 16
Registered: January 2009

Junior Member

I tried to use dba_tab_modifications table, but it gives only result fromlast analyze.

Regards...

Report message to a moderator

Re: how to find fastest growing object [message #380982 is a reply to message #379281]

Wed, 14 January 2009 11:58

JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...

Senior Member

Can you not look at Dba_data_files to get the file_id's of the files that you've added, and then look at DBA_EXTENTS to see which Segments have the most extents in those files?

Report message to a moderator

Previous Topic:	Oracle9i (9.0.1.0.1) Installation
Next Topic:	Server patch for 10.2.0.1.0

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Nov 29 16:03:37 CST 2024