| Autoextend on [message #394378] |
Thu, 26 March 2009 13:35  |
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 #394388 is a reply to message #394385] |
Thu, 26 March 2009 14:38  |
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 #394394 is a reply to message #394389] |
Thu, 26 March 2009 14:56 |
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 |
 |
Mahesh Rajendran
Messages: 10708
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.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
