Re: Segment Growth Tracking

 Thanks Mark and Andy for your comments. I even earlier added a trigger to get data from segment/lobs/index to another table but realize that it is missing the details of partition as well as Lobs. Trying to get some data from Hist Seg but not able to get the correct details which can help to identify what object and object type is all of sudden causing high growth TxSanjay

    On Tuesday, July 12, 2022 at 07:53:11 PM EDT, Mark W. Farnham <mwf_at_rsiz.com> wrote:  

 <!--#yiv8862706957 _filtered {} _filtered {}#yiv8862706957 #yiv8862706957 p.yiv8862706957MsoNormal, #yiv8862706957 li.yiv8862706957MsoNormal, #yiv8862706957 div.yiv8862706957MsoNormal {margin:0in;margin-bottom:.0001pt;font-size:12.0pt;font-family:"Times New Roman", "serif";}#yiv8862706957 a:link, #yiv8862706957 span.yiv8862706957MsoHyperlink {color:blue;text-decoration:underline;}#yiv8862706957 a:visited, #yiv8862706957 span.yiv8862706957MsoHyperlinkFollowed {color:purple;text-decoration:underline;}#yiv8862706957 span.yiv8862706957EmailStyle17 {font-family:"Calibri", "sans-serif";color:#1F497D;}#yiv8862706957 .yiv8862706957MsoChpDefault {font-family:"Calibri", "sans-serif";} _filtered {}#yiv8862706957 div.yiv8862706957WordSection1 {}--> nice. I missed they added that. So tracking the total (daily, weekly, monthly, or quarterly) into a user table should make it easy to feed into a spreadsheet to project needs, so that allocation of extents can be driven during database slack time and capacity planning can be done.

Any further information on what is unreliable for lobs? Is it just the delta is fubar, or is the total also broken?

My old tool dumped the history using actual space in use per object, but that was long before dba_hist_seg_stat existed. That is a lot of nice performance data in that object as well.

mwf

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Andy Sayer Sent: Tuesday, July 12, 2022 7:14 PM
To: mwf_at_rsiz.com
Cc: Oracle List; smishra_97_at_yahoo.com Subject: Re: Segment Growth Tracking

Dba_hist_seg_stat. My script sums up space_used_delta but it is not reliable for lobs. There’s also space_used_total, and space_allocated% versions but I don’t recall whether they’re better for lobs. 

Thanks,

Andy 

On Tue, Jul 12, 2022 at 4:08 PM, Mark W. Farnham <mwf_at_rsiz.com> wrote:

It’s passing strange that two things in two days scream for “extmon” to be updated to handle the current releases and object types of Oracle.

I am not aware of a supported tool that automatically tracks what you need.

This should be a built-in feature of Oracle. Because my customers desperately needed it I wrote it in the 1990s. Even though I am semi-retired I could be bribed to bring it up to date. If Oracle is interested, they know where to find me. I’d probably need to change the name, since limited extents is no longer an issue (and has not been for a *long* time.)

mwf

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Sanjay Mishra ("smishra_97") Sent: Tuesday, July 12, 2022 4:15 PM
To: Oracle List
Subject: Segment Growth Tracking

Hi 

I had a database where all of a sudden heavy usage of the tablespace is used by the Segments. There are 100s of tables/Indexes in 3 schemas using this tablespace and the database is over 200Tb with multiple tables daily or weekly partitioned. So need help with the following

1. If someone shares the best way to keep daily segment growth using a trigger or scheduler. The main thing is to record Table/Index/Lob segments as lots of partitioned tables also have secure files LOBS
2. Can I get the details from AWR like History Seg Stats or others which can show what segment has used lots of space in the last 1-2 days

Thanks

Sanjay   

--
http://www.freelists.org/webpage/oracle-l