OraFAQ Forum: Server Administration » How do I effectively monitor and notify OS diskspace utilized by Oracle tablespaces?

Home » RDBMS Server » Server Administration » How do I effectively monitor and notify OS diskspace utilized by Oracle tablespaces?

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

How do I effectively monitor and notify OS diskspace utilized by Oracle tablespaces? [message #258777]

Mon, 13 August 2007 10:06

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

Report message to a moderator

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

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

Report message to a moderator

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

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

Report message to a moderator

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

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.

Report message to a moderator

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

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...

Report message to a moderator

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

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

Report message to a moderator