Home » SQL & PL/SQL » SQL & PL/SQL » Tablespace Monitoring script (oracle 10g)
Tablespace Monitoring script [message #584891] Mon, 20 May 2013 06:17 Go to next message
x-oracle
Messages: 332
Registered: April 2011
Location: gujarat
Senior Member
hello here
can anybody help to create a script i want to monitor my tablespaces so.anybody help me

i want monitor my tablespace by my sqlplus
in this script i want tablespace name,total size(MB),user Space (MB),Used Space (%),Free space(MB)
help in this please

[EDITED by LF: fixed topic title typo; was "scrip"]

[Updated on: Mon, 20 May 2013 15:10] by Moderator

Report message to a moderator

Re: Tablespace Monitoring scrip [message #584911 is a reply to message #584891] Mon, 20 May 2013 07:57 Go to previous messageGo to next message
Michel Cadot
Messages: 58938
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It is simple.
DBA_DATA_FILE.BYTES gives the size of each file in each tablespace.
DBA_SEGMENTS gives the size of each segment in each tablespace.

Regards
Michel
Re: Tablespace Monitoring scrip [message #584925 is a reply to message #584911] Mon, 20 May 2013 09:03 Go to previous messageGo to next message
BlackSwan
Messages: 22718
Registered: January 2009
Senior Member
http://www.orafaq.com/wiki/Scripts
Re: Tablespace Monitoring scrip [message #584984 is a reply to message #584911] Tue, 21 May 2013 00:56 Go to previous messageGo to next message
x-oracle
Messages: 332
Registered: April 2011
Location: gujarat
Senior Member
thanks for your reply
i show this script


SQL> SELECT Total.name "Tablespace Name",
  2         nvl(Free_space, 0) Free_space,
  3         nvl(total_space-Free_space, 0) Used_space,
  4         total_space
  5  FROM
  6    (select tablespace_name, sum(bytes/1024/1024) Free_Space
  7       from sys.dba_free_space
  8      group by tablespace_name
  9    ) Free,
 10    (select b.name,  sum(bytes/1024/1024) TOTAL_SPACE
 11       from sys.v_$datafile a, sys.v_$tablespace B
 12      where a.ts# = b.ts#
 13      group by b.name
 14    ) Total
 15  WHERE Free.Tablespace_name(+) = Total.name
 16  ORDER BY Total.name;


but its gives me only Tablespace Name,FREE_SPACE, USED_SPACE ,TOTAL_SPACE
info but i want also Used Space(%) also can u help me in this please
Re: Tablespace Monitoring scrip [message #584985 is a reply to message #584984] Tue, 21 May 2013 01:24 Go to previous messageGo to next message
Littlefoot
Messages: 19525
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
You have 10 apples. 7 of them are red.
How many apples - in percentage - are red?
Re: Tablespace Monitoring scrip [message #584994 is a reply to message #584985] Tue, 21 May 2013 02:45 Go to previous messageGo to next message
x-oracle
Messages: 332
Registered: April 2011
Location: gujarat
Senior Member
ok thanks
i got it now i successfully create this scrip
SQL> select df.tablespace_name "Tablespace",
  2  totalusedspace "Used MB",
  3  (df.totalspace - tu.totalusedspace) "Free MB",
  4  df.totalspace "Total MB",
  5  round(100 * ( ( tu.totalusedspace)/ df.totalspace)) Used_Pct
  6  from
  7  (select tablespace_name,
  8  round(sum(bytes) / 1048576) TotalSpace
  9  from dba_data_files
 10  group by tablespace_name) df,
 11  (select round(sum(bytes)/(1024*1024)) totalusedspace, tablespace_name
 12  from dba_segments
 13  group by tablespace_name) tu
 14  where df.tablespace_name = tu.tablespace_name ;

Tablespace                        Used MB    Free MB   Total MB   USED_PCT
------------------------------ ---------- ---------- ---------- ----------
SYSAUX                                422          8        430         98
UNDOTBS1                               15        445        460          3
USERS                                8931       5170      14101         63
SYSTEM                                542          8        550         99

SQL> 
Re: Tablespace Monitoring scrip [message #585033 is a reply to message #584994] Tue, 21 May 2013 08:19 Go to previous messageGo to next message
BlackSwan
Messages: 22718
Registered: January 2009
Senior Member
What results when 1 or more datafiles have AUTOEXTEND enabled.
Re: Tablespace Monitoring scrip [message #588529 is a reply to message #585033] Wed, 26 June 2013 06:36 Go to previous messageGo to next message
chintan.patel
Messages: 131
Registered: July 2008
Location: Ahmedabad
Senior Member
Ok Thanks.

Chintan
Re: Tablespace Monitoring scrip [message #588738 is a reply to message #584994] Thu, 27 June 2013 16:44 Go to previous messageGo to next message
lkngstr82is
Messages: 33
Registered: January 2010
Location: USA
Member
Following SQL will give you AUTO MAX for auto extensible TS

select a.tablespace_name,
round(alloc_space/(1024*1024*1000), 2) "Alloc(GB)",
round((alloc_space-nvl(free_space, 0))/(1024*1024*1000), 2) "Used(GB)",
round((nvl(free_space, 0)+(max_space-alloc_space))/(1024*1024*1000), 2) "Free(GB)",
round((max_space)/(1024*1024*1000), 2) "AutoMax(GB)",
round(((alloc_space-nvl(free_space, 0))/max_space)*100, 2) "Used %" from
(select tablespace_name, sum(bytes) alloc_space, 
sum(greatest(MAXBYTES, BYTES)) max_space
from dba_data_files
group by tablespace_name) a, 
(select tablespace_name, sum(bytes) free_space
from dba_free_space
group by tablespace_name) b
where a.tablespace_name=b.tablespace_name(+)
order by 1;

Re: Tablespace Monitoring scrip [message #588779 is a reply to message #588738] Fri, 28 June 2013 05:59 Go to previous message
chintan.patel
Messages: 131
Registered: July 2008
Location: Ahmedabad
Senior Member
Thanks Harsh

Actually I need this query to check with my query.

Chintan
Previous Topic: Snap id
Next Topic: How to use outer join condition in my below query.
Goto Forum:
  


Current Time: Sat Aug 30 05:34:16 CDT 2014

Total time taken to generate the page: 0.11610 seconds