Tablespace space usage monitoring for autextend tablespaces
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 20

Thread: Tablespace space usage monitoring for autextend tablespaces

  1. #1
    Join Date
    May 2007
    Posts
    6

    Tablespace space usage monitoring for autextend tablespaces

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

    Appreciate replies ASAP.

    thanks,
    Hussain

  2. #2
    Join Date
    Mar 2004
    Location
    DC,USA
    Posts
    650
    Write a sql query, OEM.......

  3. #3
    Join Date
    May 2007
    Posts
    6
    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

  4. #4
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    look at dba_free_space and dba_data_files

  5. #5
    Join Date
    Mar 2004
    Location
    DC,USA
    Posts
    650
    autoextensible column under dba_data_files

    Also this query would give you the used size, free and percentage used for all the tablespaces.
    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';
    Hope this helps you.

  6. #6
    Join Date
    May 2007
    Posts
    6
    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

  7. #7
    Join Date
    Mar 2004
    Location
    DC,USA
    Posts
    650
    Post the output of dba data files for the respective tablespace ts1.

  8. #8
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    Quote Originally Posted by 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
    if you use autoextend that is going to happen - it will always be full and extend when it needs to

  9. #9
    Join Date
    May 2007
    Posts
    6
    Hi bdasan,
    there are many fields in the dba_data_files, which fileds you would like to see in the output?

  10. #10
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,002
    You could write a perl script that monitors the alert log for errors and for messages relating to datafiles being extended.
    this space intentionally left blank

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width