Hi guys,
In 9iR2, how can I monitor the Tablespace space usage while datafiles are autextend for the tablespace?
Appreciate replies ASAP.
thanks,
Hussain
Printable View
Hi guys,
In 9iR2, how can I monitor the Tablespace space usage while datafiles are autextend for the tablespace?
Appreciate replies ASAP.
thanks,
Hussain
Write a sql query, OEM.......:)
Hi dbasan,
Your reply is appreciated.....
As I'm new to Oracle and in the learning phase, I'm looking the query to find / monitor the space usage on tablespaces with datafiles autoextends.
-H
look at dba_free_space and dba_data_files
autoextensible column under dba_data_files
Also this query would give you the used size, free and percentage used for all the tablespaces.
Hope this helps you. :)Code:SELECT dts.tablespace_name,NVL(ddf.bytes / 1024 / 1024, 0) avail,
NVL(ddf.bytes - NVL(dfs.bytes, 0), 0)/1024/1024 used,
NVL(dfs.bytes / 1024 / 1024, 0) free,
TO_CHAR(NVL((ddf.bytes - NVL(dfs.bytes, 0)) / ddf.bytes * 100, 0), '990.00') "Used %",
dts.contents,dts.extent_management,dts.status FROM sys.dba_tablespaces dts,
(select tablespace_name, sum(bytes) bytes
from dba_data_files group by tablespace_name) ddf,
(select tablespace_name, sum(bytes) bytes
from dba_free_space group by tablespace_name) dfs
WHERE dts.tablespace_name = ddf.tablespace_name(+)
AND dts.tablespace_name = dfs.tablespace_name(+)
AND NOT (dts.extent_management like 'LOCAL'
AND dts.contents like 'TEMPORARY')
UNION ALL
SELECT dts.tablespace_name,NVL(dtf.bytes / 1024 / 1024, 0) avail,
NVL(t.bytes, 0)/1024/1024 used,NVL(dtf.bytes - NVL(t.bytes, 0), 0)/1024/1024 free,
TO_CHAR(NVL(t.bytes / dtf.bytes * 100, 0), '990.00') "Used %",dts.contents,
dts.extent_management,dts.status
FROM sys.dba_tablespaces dts,
(select tablespace_name, sum(bytes) bytes
from dba_temp_files group by tablespace_name) dtf,
(select tablespace_name, sum(bytes_used) bytes
from v$temp_space_header group by tablespace_name) t
WHERE dts.tablespace_name = dtf.tablespace_name(+)
AND dts.tablespace_name = t.tablespace_name(+)
AND dts.extent_management like 'LOCAL'
AND dts.contents like 'TEMPORARY';
Thanks dbasan,
thanks for the srcipt, but it's showing free space is 0 and used space 100% for the datafiles which are autoextend, please advise.
Following is output,
TABLESPACE_NAME AVAIL USED FREE Used % CONTENTS EXTENT_MAN STATUS
------------------------------ ---------- ---------- ---------- ------- --------- ---------- ---------
TS1 900 900 0 100.00 PERMANENT LOCAL ONLINE
Post the output of dba data files for the respective tablespace ts1.
if you use autoextend that is going to happen - it will always be full and extend when it needs toQuote:
Originally Posted by hussain123
Hi bdasan,
there are many fields in the dba_data_files, which fileds you would like to see in the output?
You could write a perl script that monitors the alert log for errors and for messages relating to datafiles being extended.
autoextensions arent logged anywhere
Yes it is logged in alertlog.Quote:
Originally Posted by davey23uk
hussain123,
Your request for the thread is:
May be you have to go through the above threads once again and your questions are briefly answered already. :)Code:how can I monitor the Tablespace space usage while datafiles are autextend for the tablespace?
Install OEM and you can monitor growth graphically, else
use the query, script it and run at frequent intervals to get you the growth.
> autoextensible column in dba data files would let you know that the datafile is in auto extention.
> In your case its showing a fully occupied in the sense its been utilised completely, you may consider imcreasing the disk space where the data file resides, and that should solve your problem.
autoextensions are NOT logged in the alert log
Question here is... have you set a max size for the tablespace or not?
If you have set a max size it's pretty easy to monitor it.
If you have not, things get a little more tricky 'cause you have to check physical disk availability too.
Autoextends are logggggggggggggggggggggggggged.:DQuote:
Originally Posted by davey23uk
On what context you deny it? or is it by overconfident!Code:Dump file d:\oracle\admin\db1\bdump\alert_db1.log
Thu May 03 14:22:32 2007
ORACLE V9.2.0.6.0 - Production vsnsta=0
vsnsql=12 vsnxtr=3
Windows 2000 Version 5.2 Service Pack 2, CPU type 586
Tue May 08 10:51:29 2007
ALTER DATABASE DATAFILE 'E:\ORADATA\DB1\USERS01.DBF' AUTOEXTEND on next 1m
Tue May 08 10:51:29 2007
Completed: ALTER DATABASE DATAFILE 'E:\ORADATA\DB1\USERS01.DB
dbasan, Does your piece of alertlog shows datafile extending at that particular time... or does it shows you have set datafile for autoextend?
Yes.Quote:
Originally Posted by PAVB
so therefore is doesnt log auto extensions, so stop peddling your crap
This is a decent forum. Dont degrade it with your foul language. I think you have to go back to school to learn decency. Communicating in this forum needs a lil bit of being civilised. :rolleyes:
One who knows what is the purpose of alert log would never be ignorant. Any change to the datafile status is always logged in alert. I was reiterating this and what gandolf989 said in the previous thread, which a brat could not understand. :oCode:Long before this place was so good.
We use a Perl script to monitor the alert logs on our databases. The alert log sends emails based on a series of filters. If I alter a tablespace to make it larger, smaller, autoextend etc, i get an email. Perhaps you are correct in stating the the mere act of Oracle autoextending a tablespace does not get logged in an alert log. I would have to set up a test to make sure though.Quote:
Originally Posted by davey23uk