Home » RDBMS Server » Server Administration » how to find fastest growing object (9i/10g)
how to find fastest growing object [message #379061] Sun, 04 January 2009 05:12 Go to next message
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...
Re: how to find fastest growing object [message #379062 is a reply to message #379061] Sun, 04 January 2009 05:50 Go to previous messageGo to next message
Michel Cadot
Messages: 64145
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Query dba_segments each day and compare the sizes.

Regards
Michel
Re: how to find fastest growing object [message #379067 is a reply to message #379062] Sun, 04 January 2009 06:33 Go to previous messageGo to next message
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..
Re: how to find fastest growing object [message #379070 is a reply to message #379067] Sun, 04 January 2009 07:11 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
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.
Re: how to find fastest growing object [message #379073 is a reply to message #379070] Sun, 04 January 2009 08:13 Go to previous messageGo to next message
Michel Cadot
Messages: 64145
Registered: March 2007
Location: Nanterre, France, http://...
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 Go to previous messageGo to next message
oradbaexp
Messages: 16
Registered: January 2009
Junior Member
I tried to use dba_tab_modifications table, but it gives only result fromlast analyze.

Regards...

Re: how to find fastest growing object [message #380982 is a reply to message #379281] Wed, 14 January 2009 11:58 Go to previous message
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?

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


Current Time: Fri Dec 09 12:03:27 CST 2016

Total time taken to generate the page: 0.20312 seconds