Home » RDBMS Server » Server Administration » How do I effectively monitor and notify OS diskspace utilized by Oracle tablespaces?
How do I effectively monitor and notify OS diskspace utilized by Oracle tablespaces? [message #258777] Mon, 13 August 2007 10:06 Go to next message
rookiedba
Messages: 11
Registered: March 2007
Location: Montana
Junior Member
I am a newbie to DBA duties so would greatly appreciate some help here...

I am working on a PL/SQL package to manage and free up space utilzed by extending Tablespaces when disk space reaches 85% (to send a warning message) and when it reaches 95% ( then start deleting records from the oldest partitions of the data tables).

We have an OLAP DB, where we have setup partitions of tables in each TableSpace. When the disk utilization reaches 85% then I should raise a warning (inserting a record to the SysMonitoring table) and when it reaches 95% ( then start deleting the oldest partition of the table in that TableSpace until the disk utlization falls below 85%). I have a mechanism to select the oldest partition table which I will use in there (so do not wory about that). But the rest of the functionality is needed. I am using DBA_SEGMENTS and DBA_DATA_FILES as a resource but can't build an effective logic yet. Would greatly appreciate any insight or help.

Thanks

Rookie DBA
Re: How do I effectively monitor and notify OS diskspace utilized by Oracle tablespaces? [message #258855 is a reply to message #258777] Mon, 13 August 2007 11:59 Go to previous messageGo to next message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
use this for calculating free space in tablespace.
SQL> desc dba_free_space
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 TABLESPACE_NAME                                    VARCHAR2(30)
 FILE_ID                                            NUMBER
 BLOCK_ID                                           NUMBER
 BYTES                                              NUMBER
 BLOCKS                                             NUMBER
 RELATIVE_FNO                                       NUMBER

Re: How do I effectively monitor and notify OS diskspace utilized by Oracle tablespaces? [message #258866 is a reply to message #258855] Mon, 13 August 2007 12:22 Go to previous messageGo to next message
rookiedba
Messages: 11
Registered: March 2007
Location: Montana
Junior Member
Hi there and tahnks for your response...

I knew that dba_free_space is available. It is to find a algorithm to correctly identify the data file sizes of the Tablespaces whne they are on autoextend mode and then bring them back to 85% of the diskspace allocated to Oracle.

WHen autoextend is on the datafile will grow and even if I truncate the tables in that tablespace, the datafile size will remain the same and thus preventing me from effectively monitoring disk space

Thanks

RDBA
Re: How do I effectively monitor and notify OS diskspace utilized by Oracle tablespaces? [message #258870 is a reply to message #258866] Mon, 13 August 2007 12:29 Go to previous messageGo to next message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
Post your Oracle version 4 decimals.
As Autoextend is not good option.

Quote:
WHen autoextend is on the datafile will grow and even if I truncate the tables in that tablespace, the datafile size will remain the same and thus preventing me from effectively monitoring disk space


Dont use autoextend On but give a large proper size to tablespace.
OFF auto extend and then check the same querry for space.
Re: How do I effectively monitor and notify OS diskspace utilized by Oracle tablespaces? [message #259507 is a reply to message #258870] Wed, 15 August 2007 15:00 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
Autoextend can be appropriate when you can't predict data growth, like when you buy an app and that's configured specifically for your org (SAP, Oracle Applications etc). Autoextend is bad when someone does something wrong like loads a 1M row flat file when you intended to load a 100 row file or autoextend is on an undo tablespace and someone runs a cartesean join and a 10k resultset ends up being a 100M row resultset...
Re: How do I effectively monitor and notify OS diskspace utilized by Oracle tablespaces? [message #259720 is a reply to message #258777] Thu, 16 August 2007 05:54 Go to previous messageGo to next message
cbruhn2
Messages: 41
Registered: January 2007
Member
Hi Rookiedba,

I think I would setup the Oracle Enterprise Manager for monitoring space requirements ( and also a lot of other different tedious DBA jobs).
You can setup the Oracle Enterprise Manager to send you an alert when you reach a specific value/threshold.
Please look in appropriate manual for your version of Oracle on how to setup.

best regards
Carl Bruhn
Re: How do I effectively monitor and notify OS diskspace utilized by Oracle tablespaces? [message #261758 is a reply to message #258777] Thu, 23 August 2007 09:09 Go to previous messageGo to next message
Sanjay Bajracharya
Messages: 279
Registered: October 2001
Location: Florida
Senior Member
Oracle Enterprise Manager (OEM) is the way to go....
Else you write you own scripts
--
Sanjay B.
OS diskspace monitoring script [message #321007 is a reply to message #258777] Sat, 17 May 2008 13:00 Go to previous messageGo to next message
shane123
Messages: 1
Registered: May 2008
Junior Member
simple disk space monitoring script
http://nalanta.com/node/26
Re: How do I effectively monitor and notify OS diskspace utilized by Oracle tablespaces? [message #321008 is a reply to message #258777] Sat, 17 May 2008 13:29 Go to previous message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
Responding to a 9 month old thread is likely a waste of electrons & disk space.
Previous Topic: compiling invalid objects
Next Topic: Memory Question
Goto Forum:
  


Current Time: Sat Dec 10 22:21:18 CST 2016

Total time taken to generate the page: 0.10166 seconds