Tablespace space usage monitoring for autextend tablespaces

[hussain123]

Hi guys,
In 9iR2, how can I monitor the Tablespace space usage while datafiles are autextend for the tablespace?

Appreciate replies ASAP.

thanks,
Hussain

[dbasan]

Write a sql query, OEM.......

[hussain123]

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

[davey23uk]

look at dba_free_space and dba_data_files

[dbasan]

autoextensible column under dba_data_files

Also this query would give you the used size, free and percentage used for all the tablespaces.

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';

Hope this helps you.

[hussain123]

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

[dbasan]

Post the output of dba data files for the respective tablespace ts1.

[davey23uk]

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

if you use autoextend that is going to happen - it will always be full and extend when it needs to

[hussain123]

Hi bdasan,
there are many fields in the dba_data_files, which fileds you would like to see in the output?

[gandolf989]

You could write a perl script that monitors the alert log for errors and for messages relating to datafiles being extended.