RE: Tablespace free space monitoring, including AUTOEXTEND

  • From: "Mark W. Farnham" <mwf@xxxxxxxx>
  • To: <Joel.Patterson@xxxxxxxxxxx>, <jkstill@xxxxxxxxx>, <adar666@xxxxxxxxxxxx>
  • Date: Wed, 14 Jul 2010 18:38:37 -0400

A quite reasonable way to handle this is creating a table of available
volumes or mount points with the total space available (or reserved) for
Oracle on it and then having the sys admins update that table if they add
some physical space. Then your "am I about to go splat" reports can subtract
the sum of the size of the datafiles on that mount point from the total
available to yield the amount available for autoextents and/or new files.
Creating this sort of cooperation through information in the database is
often a great first step in getting a working relationship going when there
is trouble between the storage management and DBA camps, and it is useful
everywhere.

 

Of course if you've got the permissions to directly keep track of what is
actually available that is even better.

 

(Us and Us always works better than Us and Them).

 

  _____  

From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Joel.Patterson@xxxxxxxxxxx
Sent: Wednesday, July 14, 2010 3:26 PM
To: jkstill@xxxxxxxxx; adar666@xxxxxxxxxxxx
Cc: oracle-l@xxxxxxxxxxxxx
Subject: RE: Tablespace free space monitoring, including AUTOEXTEND

 

Here is one written to run in EM_grid as a job.  (the double %%), but you
could easily modify it for Sqlplus etc.   It mails a message if a new file
is needed.

 

It works on either 2K or 8K blocksizes (which determine the max datafile
size).   You can modify for other block sizes, and you can tell it when to
send the email.

 

For instance for 8K blocksize, it will send a message whenever all the
datafiles in a tablespace are over 30Gbs, (you supply the number in the case
statement for v_datafile_size).

 

 

As for 'available disk space', we monitor that separately, the sysadmins
actually monitor it, and I believe they use various tools such as foglight.

Joel Patterson 
Database Administrator 
904 727-2546 

  _____  

From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Jared Still
Sent: Wednesday, July 14, 2010 11:35 AM
To: adar666@xxxxxxxxxxxx
Cc: oracle-l@xxxxxxxxxxxxx
Subject: Re: Tablespace free space monitoring, including AUTOEXTEND

 

On Tue, Jul 13, 2010 at 11:00 PM, Yechiel Adar <adar666@xxxxxxxxxxxx> wrote:

One of the main problems of this kind of scripts is that they do not check
the actual available disk space for auto extend.
You can have a definition that show that you have 32 GB available, with auto
extend, but you gave only 10 GB on the disk.


That could be remedied should someone be ambitious enough
to write the Java SP to get that info.


Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist
Oracle Blog: http://jkstill.blogspot.com
Home Page: http://jaredstill.com

Other related posts:

  1. Home
  2. oracle-l
  3. Archive
  4. 07-2010