Re: Maximum Usage of Temp Tablespaces Since Database Started

From: Mladen Gogala <gogala.mladen_at_gmail.com>
Date: Mon, 23 Jul 2012 16:34:02 +0000 (UTC)
Message-ID: <jujudq$gvs$3_at_solani.org>

On Mon, 23 Jul 2012 09:17:49 -0700, joel garry wrote:

> On Jul 23, 8:46 am, Mladen Gogala <gogala.mla..._at_gmail.com> wrote:

>> On Mon, 23 Jul 2012 07:34:01 -0700, basis_consultant wrote:
>> > Hi,
>>
>> > We are running a large ERP system on Oracle 10g (I know that we
>> > should not be on 10g-Will be upgrading to Oracle 11 soon).
>>
>> > There are 6 temporary tablespaces with a size of 16GB each.
>>
>> > Is there any way to find out the maximum bytes used by the temp.
>> > tablespaces since the database was started?
>>
>> > I suppose that I can approximate by having a script run "select
>> > TABLESPACE_NAME, BYTES_USED, BYTES_FREE from V$TEMP_SPACE_HEADER"
>> > regularly, but I am hoping that there is a better way.
>>
>> > Thanks,
>> > QZ
>>
>> That is simple. V$ACTIVE_SESSION_HISTORY has that information per user
>> session. You should join with the DBA_USERS, group by temporary
>> tablespace and sample_time and compute the sum for each group.
>> --http://mgogala.byethost5.com

> 
> Doesn't that default to something like a hundredth of the data he would
> need for an ERP yearly cycle?  (Assuming the "large ERP" has the
> licensing, of course.)
> 
> jg

Not necessarily. That depends on the AWR data retention period. I have no way of knowing what is that period at his site.

-- 
http://mgogala.byethost5.com

Received on Mon Jul 23 2012 - 11:34:02 CDT