Help - Search - Members - Calendar
Full Version: Monitoring Tablespace growth
Oracle DBA Forums > Oracle > Oracle Forum
alex86
Hi everybody!
I want to monitor remotely the growth of the space used by all the tablespaces of my DB (10g - windows), so I get all the information I need from the tables "dba_data_files" and "dba_free_space", grouping by tablespace_name. (I hope it's the correct way!).
But how can I retrieve the information of the real free physical space that I have at my disposal for the DB?
Should I make a particular query to the Operating System?
dave
yes query the os - or use enterprise manager
alex86
Ok, thanks. If I want to query the OS, what I have to do particularly?
Littlewheat
Hi alex,
for get used space on tablespace i use for example:

select tablespace_name TS_NAME, sum(bytes) TS_size from dba_extents group by tablespace_name

Real size of tablespace (datafiles) is possible get
select name df_name,bytes df_size from v$datafile
rjh
hi!

I usually use this script:

set feed off

column "tablespace_name" heading "Tablespace | Name" format a20
column "FileCount" heading "File | Count" format 999999
column "Size(MB)" heading "Size | (MB)" format 999,999,999.99
column "Free(MB)" heading "Free | (MB)" format 999,999,999.99
column "Used(MB)" heading "Used | (MB)" format 999,999,999.99
column "Max Ext(MB)" heading "Max Ext | (MB)" format 999,999,999
column "%Free" heading "% | Free" format 999.99
column "%Free Ext" heading "% | Free Ext" format 999.99
column "Graph" heading "Graph" format a11
column tablespace_name heading "Tablespace | Name" format a20


SELECT ts.tablespace_name, "File Count",
TRUNC("SIZE(MB)", 2) "Size(MB)",
TRUNC(fr."FREE(MB)", 2) "Free(MB)",
TRUNC("SIZE(MB)" - "FREE(MB)", 2) "Used(MB)",
df."MAX_EXT" "Max Ext(MB)",
(fr."FREE(MB)" / df."SIZE(MB)") * 100 "% Free",
RPAD('*', TRUNC(CEIL((fr."FREE(MB)" / df."SIZE(MB)") * 100)/10), '*') "Graph"
FROM (SELECT tablespace_name,
SUM (bytes) / (1024 * 1024) "FREE(MB)"
FROM dba_free_space
GROUP BY tablespace_name) fr,
(SELECT tablespace_name, SUM(bytes) / (1024 * 1024) "SIZE(MB)", COUNT(*)
"File Count", SUM(maxbytes) / (1024 * 1024) "MAX_EXT"
FROM dba_data_files
GROUP BY tablespace_name) df,
(SELECT tablespace_name
FROM dba_tablespaces) ts
WHERE fr.tablespace_name = df.tablespace_name (+)
AND fr.tablespace_name = ts.tablespace_name (+)
ORDER BY "% Free" desc
/

prompt
prompt "The last line shows the Tablespace wich is growing too large..."
prompt
aussie_dba
"But how can I retrieve the information of the real free physical space that I have at my disposal for the DB?"

Oracle does not know about un-allocaterd disk space, so I would monitor at the OS level.

Also read this thread:

http://dba.ipbhost.com/lofiversion/index.php/t5451.html

http://www.dba-oracle.com/t_database_growth_reports.htm
aussie_dba
See here, a script to monitor tablespace growth:

http://www.dba-oracle.com/t_tablespace_growth_scripts.htm

And this script to monitor RAC tablespaces:

http://www.dba-oracle.com/t_grid_rac_v$_tablespaces.htm
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.
Invision Power Board © 2001-2014 Invision Power Services, Inc.