Re: Looking for Scripts for Database Month Growth / Tablespace Growth.
- From: Sheldon Quinny <sheldonquinny@xxxxxxxxx>
- To: Maureen English <maureen.english@xxxxxxxxxx>
- Date: Thu, 20 Jan 2011 09:03:07 +0300
Hi Maureen,
This script makes sense...but Now what I am looking for something ...sort
of selecting the total size of the tablespace and putting it in a table
monthly..
so I would want this script to execute every month.
End of the Year I have Growth Statistics for Each Month for Each
Tablespaces..
Thanks..
Regards,
Sheldon.
On Wed, Jan 19, 2011 at 9:46 PM, Maureen English <maureen.english@xxxxxxxxxx
> wrote:
> Here's what we use to monitor tablespace usage:
>
> set pages 2000 lines 256 trimspool on
>>
>> column tablespace_name format a18
>> column file_name format a40
>> column percent_used format '999.99' head '% USED'
>> column current_size format '999,999' head 'CURRENT|SIZE'
>> column segment_count format '999,999' head 'SEGMENT|COUNT'
>> column used format '999,999' head 'CURRENT|USED'
>> column biggest_next format '9,999.00' head 'LARGEST|NEXT EXT'
>> column biggest_block format '9,999.00' head 'LARGEST|AVAIL BLOCK'
>> column possible_size format '999,999' head 'POSSIBLE|SIZE'
>> column possible_multiple format '999,999.99' head 'UNUSED AS|MULTIPLE
>> OF|LARGEST NEXT'
>> column files format '99999'
>>
>> ttitle left "Tablespace Usage - possible size and unused as multiple both
>> reflect ability to autoextend" skip 1 -
>> left "(all sizes in MB)" skip 2;
>>
>> spool ${ORACLE_BASE}/local/spool/PDtablespace_usage${ORACLE_SID}.lis
>>
>> select x.tablespace_name, current_size, used,
>> round(used/(current_size + sum(nvl(can_extend,0)))*100,2) percent_used,
>> biggest_next,
>> nvl(biggest_block,0) biggest_block,
>> current_size + sum(nvl(can_extend,0)) possible_size,
>> round((nvl(current_size,0) +
>> sum(nvl(can_extend,0))-nvl(used,0))/decode(biggest_next,null,1,biggest_next),2)
>> possible_multiple,
>> segment_count
>> from (
>> select b.tablespace_name,
>> round(current_size/1024/1024,2) current_size,
>> round((current_size - nvl(free_space,0))/1024/1024,2) used,
>> file_name,
>> round(nvl(biggest_next,initial_extent)/1024/1024,2)
>> biggest_next,
>> autoextensible,
>> decode(can_extend,null,null,round(can_extend/1024/1024,2))
>> can_extend,
>> nvl(segment_count,0) segment_count
>> from (select sum(bytes) used, max(next_extent)
>> biggest_next, count(*) segment_count,
>> tablespace_name from dba_segments group by tablespace_name) a,
>> (select sum(bytes) current_size, tablespace_name from
>> dba_data_files group by tablespace_name) b,
>> (select tablespace_name, file_name, AUTOEXTENSIBLE,
>> decode(autoextensible,'YES', MAXBYTES - BYTES, null)
>> can_extend
>> from dba_data_files group
>> by tablespace_name, file_name, AUTOEXTENSIBLE, MAXBYTES - BYTES ) c,
>> (select sum(bytes) free_space, tablespace_name from
>> dba_free_space group by tablespace_name) d,
>> (select initial_extent, tablespace_name from
>> dba_tablespaces) e
>> where e.tablespace_name = b.tablespace_name
>> and b.tablespace_name = a.tablespace_name (+)
>> and b.tablespace_name = c.tablespace_name
>> and c.tablespace_name = d.tablespace_name (+)
>> ) x,
>> ( select nvl(round(max(bytes)/1024/1024,2),0) biggest_block,
>> tablespace_name from dba_free_space group by tablespace_name
>> ) y
>> where x.tablespace_name = y.tablespace_name (+)
>> group by x.tablespace_name, current_size, used, biggest_next,
>> biggest_block, segment_count
>> order by percent_used desc, tablespace_name asc
>> /
>>
>> spool off
>>
>> ttitle off
>>
>
>
> Sheldon Quinny wrote:
>
>> Hi Alexey,
>>
>> Thanks for your reply..
>>
>> I was looking for something sort of a sql query which can be executed
>> every day or month for Tablespaces through a Batch File Schedule in the
>> Windows Scheduler..
>>
>> Most of the scripts I run are thru the windows scheduler , so just wanted
>> to keep a count on how big are the tablespaces getting during a month or
>> so...
>>
>> Thanks..
>>
>>
>> 2011/1/18 Alexey B. Danchenkov <dabron@xxxxxxxxxxx <mailto:
>> dabron@xxxxxxxxxxx>>
>>
>>
>> Hi,
>> You can use DBA_HIST_TBSPC_SPACE_USAGE AWR view to monitor the
>> growth trends of the tablespaces and databases. Then you can build
>> visual charts to visualize growth trends based on this data using
>> for example dbTrends tool. see it here www.spviewer.com
>> <http://www.spviewer.com>
>>
>>
>> With kind regards,
>> Alexey B. Danchenkov
>>
>>
>>
>> ------------------------------------------------------------------------
>> Date: Tue, 18 Jan 2011 15:08:07 +0300
>> Subject: Looking for Scripts for Database Month Growth / Tablespace
>> Growth.
>> From: sheldonquinny@xxxxxxxxx <mailto:sheldonquinny@xxxxxxxxx>
>> To: oracle-l@xxxxxxxxxxxxx <mailto:oracle-l@xxxxxxxxxxxxx>
>>
>>
>>
>> Hi Everyone...
>>
>> I take care of 4 - Oracle 10GR2 Database on Windows Platform.
>>
>> I am looking for scripts or ways which can be implemented to monitor
>> the growth of the tablespaces and database..[Monthly,Yearly so on..]
>>
>> Please can someone share with me the Growth Rate Monitoring Methods
>> so I can get it working out here.
>>
>>
>> Thank you,
>> Sheldon..
>>
>>
>>
>>
Other related posts:
