Home » SQL & PL/SQL » Client Tools » Check historical data/Monitor Temp tablespace usage (10g/11g)
Check historical data/Monitor Temp tablespace usage [message #616368] Mon, 16 June 2014 04:17 Go to next message
ledo60
Messages: 63
Registered: May 2014
Member
Hi,
Please assist for geting a script to read the historical data usage for TEMP tablespace .
Actualy i need the value of the MAX usage/day for the previous days i tested this code but it's not the same as toad when comparing.
Please advice

select  thedate,
 gbsize,
 prev_gbsize,
 gbsize-prev_gbsize diff
from        (
 select  thedate,
 gbsize,
 lag(gbsize,1) over (order by r) prev_gbsize
 from    (
 select  rownum r,
 thedate,
 gbsize
 from    (
 select  trunc(thedate) thedate,
 max(gbsize) gbsize
 from    (
 select  to_date(to_char(snapshot.begin_interval_time,'YYYY-MON-DD HH24:MI:SS'),'YYYY-MON-DD HH24:MI:SS') thedate,
 round((usage.tablespace_usedsize*block_size.value)/1024/1024/1024,2) gbsize
 from    dba_hist_tbspc_space_usage usage,
 v$tablespace               tablespace,
 dba_hist_snapshot          snapshot,
 v$parameter                block_size
 where   usage.snap_id       = snapshot.snap_id
 and     usage.tablespace_id = tablespace.ts#
 and     tablespace.name     = '&tablespace'
 and     block_size.name     = 'db_block_size'
 )
 group by
 trunc(thedate)
 order by
 trunc(thedate)
 )
 )
 );





CM: fixed end code tag. It needs to start with a /

[Updated on: Mon, 16 June 2014 04:27] by Moderator

Report message to a moderator

Re: Check historical data/Monitor Temp tablespace usage [message #616386 is a reply to message #616368] Mon, 16 June 2014 08:35 Go to previous messageGo to next message
BlackSwan
Messages: 22809
Registered: January 2009
Senior Member
When you start with the wrong question, no matter how good an answer you get, it won't matter very much.

> but it's not the same as toad when comparing.
how do you know/determine which value is CORRECT?
Re: Check historical data/Monitor Temp tablespace usage [message #616392 is a reply to message #616368] Mon, 16 June 2014 08:55 Go to previous messageGo to next message
Michel Cadot
Messages: 59203
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
e but it's not the same as toad


TOAD returns b... with TEMP tablespace.
This is at the image of the product.
Use TOAD just for development; forget it for any administration thing.

Re: Check historical data/Monitor Temp tablespace usage [message #616444 is a reply to message #616392] Tue, 17 June 2014 01:15 Go to previous message
ledo60
Messages: 63
Registered: May 2014
Member
Actualy i mean my question:
1-Query Historical data for TEMP TS sizing.
2-Monitor it's growth .

Why toad return inaccurate data , am comparing toad value at the same time of execute the script .
* can i depend on above script or you have another suggestion
Previous Topic: SQLDeveloper working, SQLPlus does not work
Next Topic: Exporting data with Scheduled job
Goto Forum:
  


Current Time: Tue Sep 23 19:40:49 CDT 2014

Total time taken to generate the page: 0.09875 seconds