Re: Tablespace Monitor

Mamta,

You can also create UNIX script doing query and put it in cron job for auto
monitoring. Put alerting also in the script if any tablespace has usage
space beyond threshold will send email to DBA.

But it's quite interesting that I have found in my instance the result
between these 2 SQL for tablespace monitoring are diferrent and not matched
each other:

1. Use dba_tablespace_usage_metrics:
select tablespace_name, used_space, tablespace_size, used_percent
fromdba_tablespace_usage_metrics
order by used_percent desc;

2, Use dba_data_files and dba_free_space:

select tablespace_name, tablespace_size-free_space_size used_space,
tablespace_size,

(tablespace_size-free_space_size)/tablespace_size*100 used_percent from

(select a.tablespace_name, sum(a.bytes/1024/1024) tablespace_size,

(select sum(b.bytes/1024/1024) from dba_free_space b
whereb.tablespace_name=a.tablespace_name)

free_space_size from dba_data_files a group by a.tablespace_name)

order by (tablespace_size-free_space_size)/tablespace_size desc;


I found SQL #2 which is using dba_free_space and dba_data_files is the
correct one.

I am using 10.1.0.4. Is it a bug ?

Please let me know. Thanks.



Regards,



Hananto


On Fri, Dec 12, 2008 at 6:41 PM, Dwyer, Brian <
Brian.Dwyer@xxxxxxxxxxxxxxxxxxxxxxxxxxxx> wrote:

>
> ***************************************************************************
>
> Internet email is not to be treated as a secure means of communication.
>
> North Yorkshire Police monitors all internet email activity and content.
>
> This communication is intended for the addressee(s) only.
>
> Please notify the sender if received in error. Unauthorised use or
>
> disclosure of the content may be unlawful. Opinions expressed in this
>
> document may not be official policy. Thank you for your co-operation.
>
> ***************************************************************************
>
>
> Mamta
>
>     The simplest way to do it is via Enterprise manager Console.
>
>     The best way to do it is to automate the process, set up some sql
> scripts to query the database on a daily basis and inform you if they go
> above a set target i.e 85%
>
>     Remember to build in logic for tablespaces with auto extend on them.
>
> Brian
>
>  ------------------------------
> *From:* ora-apps-dba-bounce@xxxxxxxxxxxxx [mailto:
> ora-apps-dba-bounce@xxxxxxxxxxxxx] *On Behalf Of *NEELI-SC, Mamta
> *Sent:* 11 December 2008 03:52
> *To:* ora-apps-dba@xxxxxxxxxxxxx
> *Subject:* Tablespace Monitor
>
>   Hello DBA's
>
>
>
> How do you monitor you tablespace?
>
>
>
> What are the steps to be taken?
>
>
>
> Please let me know.
>
>
>
> Regards
>
> Mamta
>

Other related posts: