Home » RDBMS Server » Server Administration » Autoextend on (Oracle 10.2.0.3 Linux AS4)
Autoextend on [message #394378] Thu, 26 March 2009 13:35 Go to next message
rsreddy28
Messages: 295
Registered: May 2007
Senior Member
Hi All,

There seems to be fight going on among my peers about the Autoextend on feature what we have for the datafile . Let me take an example where I have a datafile like:

SQL> Create tablespace xyz datafile '/users/xyz.dbf' size 50m
autoextend on;

To my knowledge it means that first oracle will try to fill up 50m, then after that if required it'll try to use the mount point space as we have autoextend on enabled. So we had a query which gives us if the free space if the datafile is autoextend on:

SELECT  df.file_name,
        df.tablespace_name,
        df. status,
        (df.bytes/1024000) t,
        (fs.s/df.bytes*100) p,
        decode (ae.y,1,'YES','NO') a
  FROM  dba_data_files df,
        (SELECT file_id,SUM(bytes) s
           FROM dba_free_space
           GROUP BY file_id) fs,
        (SELECT file#, 1 y
           FROM sys.filext$
           GROUP BY file#) ae
  WHERE df.file_id = fs.file_id
    AND ae.file#(+) = df.file_id
ORDER BY df.tablespace_name, df.file_id;


I'm looking for a query which gives me the exact free space basing on the mount point space availability. Assuming the free space in my mount point is 500 mb , so my datafile can extend max upto 500 m. But the query output doesn't show the output in the way I wanted .

Can somebody help me on this.

regards,
Raj
Re: Autoextend on [message #394383 is a reply to message #394378] Thu, 26 March 2009 14:14 Go to previous messageGo to next message
BlackSwan
Messages: 25035
Registered: January 2009
Location: SoCal
Senior Member
Oracle does not know, care, or have the ability to obtain amount of free disk space.

The problem gets more complicated when a tablespace consists of multiple files which reside on multiple volumes.

This is why some advocate NOT using AUTOEXTEND.
Re: Autoextend on [message #394385 is a reply to message #394378] Thu, 26 March 2009 14:30 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
As BlackSwan wrote
>>This is why some advocate NOT using AUTOEXTEND.
I am one such person.
With or without autoextend on, you need to monitor the disk usage.
Without, you always have some room to increase / add datafile.
With, most probably your disk is full before you can react and there is a need to call the Sys Admins/Storage Admins.
It is better off without autoextend on.
>>I'm looking for a query which gives me the exact free space basing on the mount point space availability.
This information is in OS.
You can complicate your life by using complex OS calls from pl/sql using options like java
or
Just use
df -h or df -k

If you are using ASM, there are options with ASM dictionary views.

The only situation where Autoextend on is much useful is
during controlled, massive data loading operations.
Re: Autoextend on [message #394388 is a reply to message #394385] Thu, 26 March 2009 14:38 Go to previous messageGo to next message
rsreddy28
Messages: 295
Registered: May 2007
Senior Member
I agree with Blackswan and Mahesh . But what happens is when you are working in an organization , where we can't decide whether to go for Autoextend on/Not . That's where all the problems crop up.

Even I don't like the feature of Autoextend on , where it does not work as expected or it's not perfect with the requirement.

So , you mean to say we need to carry on with Autoextend on like this only. I don't know whether it's really a curse or boon for monitoring the datafile free space .
Re: Autoextend on [message #394389 is a reply to message #394388] Thu, 26 March 2009 14:43 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>>I don't know whether it's really a curse or boon for monitoring the datafile free space .
It is upto you.
You are not the first to do some 'monitoring' or the only one doing it. It is very simple and been there for ever I can remember.
Monitor for the disk usage using OS commands.
You must be doing this anyhow ( for other OS directories).


Re: Autoextend on [message #394390 is a reply to message #394389] Thu, 26 March 2009 14:47 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Any remote monitoring tool like nagios should be able to do this too.
Re: Autoextend on [message #394394 is a reply to message #394389] Thu, 26 March 2009 14:56 Go to previous messageGo to next message
rsreddy28
Messages: 295
Registered: May 2007
Senior Member
Yes, I'm not the only one to do this but it makes all the shell scripts futile when it gets triggers a mail for the datafile reached the filesize value. And that too if we are working in an enterprise where we have around 500 databases , things like this makes go wild.

Anyhow all this was my thoughts about the issues I face with monitoring about Autoextend datafiles, thats it.
Re: Autoextend on [message #394397 is a reply to message #394394] Thu, 26 March 2009 15:06 Go to previous message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Not sure why it would be called futile. As said, it depends on how put things to use. These are just tools.
The shell script that I wrote in 2001, that would monitor 350 databases across the world (even before these web enabled EM thingies existed) is still churning (though, they moved on)

>>when it gets triggers a mail for the datafile reached the filesize value
I thought, you wanted to monitor the usage.
Previous Topic: DBverify Vs RMAN Validate
Next Topic: ORA-00376: file 201 cannot be read at this time
Goto Forum:
  


Current Time: Sat Dec 03 20:27:23 CST 2016

Total time taken to generate the page: 0.09855 seconds