Almost all Oracle DBAs need to continually check the status of various tablespaces in the database. Here is a SQL script that will generate a bar graph showing the status of tablespaces in the database.
It generates a PHP file (tsu.php) that upon execution generates tsu.gif, a graphical representation of the status of the various tablespaces. The graph size is fixed and the bar width changes according to the number of tablespaces. The bar color is red if utilization exceeds 80%, it is yellow if utilization is between 60% to 80%, and is green for utilization less than 60%.
Some details, such as tablespace size (used and remaining), are retrieved from the Oracle instance but are not included in the graph. If one is interested in these, just include those along with the names of the tablespaces.
The script requires PHP support on your system. Moreover, if your PHP installation does not support 'gif' format, simply replace all occurences of 'gif' with 'png'. PNG support is generally available in PHP. This script has to be executed with DBA priviledges. It has been tested on Red Hat Linux 6.2 and Oracle 8.1.6 with eight tablespaces. Anyone who can write similar script in Perl is invited. It will be more system independent.
REM free_space_graph.sql REM Generates 'tsu.php' which in turn generates 'tsu.gif' set serveroutput on DECLARE FH UTL_FILE.FILE_TYPE ; parts number ; w number
; st number := 0 ; sys_date char(15) ; dbname varchar2(9) ; t_name varchar2(15) ; t_status char(8) ; t_size number(9) := 0 ; t_used number(9) := 0 ; t_remain number(9) := 0 ; t_per_used number(5,2) := 0 ; cursor s1 is select t.tablespace_name , decode(t.status, 'ONLINE', t.status, nls_initcap(t.status)) , (tsa.bytes / 1024) , ((tsa.bytes - decode(tsf.bytes, null, 0, tsf.bytes)) / 1024), (decode(tsf.bytes, null, 0, tsf.bytes) / 1024), (round((1 - decode(tsf.bytes, null, 0,tsf.bytes) / tsa.bytes) * 100)) from sys.dba_tablespaces t, sys.sm$ts_avail tsa, sys.sm$ts_free tsf where t.tablespace_name = tsa.tablespace_name and t.tablespace_name = tsf.tablespace_name (+) order by 1, t.status, t.tablespace_name; BEGIN FH := UTL_FILE.FOPEN('/home/oracle/shishir','tsu.php','w') ; UTL_FILE.PUT( FH, '<?php header ("Content-type: image/gif"); $im = @ImageCreate (500, 245) or die ("Cannot Initialize new GD image stream"); $background_color = ImageColorAllocate ($im, 200, 200, 255); $violet = ImageColorAllocate ($im, 140, 0, 100); $yellow = ImageColorAllocate ($im, 255, 255, 0); $red = ImageColorAllocate ($im, 255, 0, 0); $green = ImageColorAllocate ($im, 0,255, 0); $text_color = ImageColorAllocate ($im, 0, 0, 0); imagedashedline ($im, 0, 200, 500, 200, $text_color); ') ; UTL_FILE.NEW_LINE(FH,1) ; select to_char(sysdate) into sys_date from dual ; select name into dbname from v$database ; select count(*) into parts from sys.dba_tablespaces ; w := 250 / parts ; open s1 ; loop fetch s1 into t_name, t_status, t_size, t_used , t_remain, t_per_used; exit when s1%notfound ; utl_file.putf(FH , 'ImageString($im,4,%s,207,"%s%",$text_color);', st + w/2,t_per_used); utl_file.new_line(FH, 1) ; if (t_per_used >= 60 AND t_per_used < 80 ) then utl_file.putf(FH, 'imagefilledrectangle ($im, %s, %s, %s, 200, $yellow);', st+w/2,200-2*t_per_used,st+w/2+w); end if ; if (t_per_used >= 80 ) then utl_file.putf(FH, 'imagefilledrectangle ($im, %s, %s, %s, 200, $red);', st+w/2,200-2*t_per_used,st+w/2+w); end if ; if (t_per_used < 60 ) then utl_file.putf(FH, 'imagefilledrectangle ($im, %s, %s, %s, 200, $green);', st+w/2,200-2*t_per_used,st+w/2+w); end if ; utl_file.new_line(FH, 1) ; utl_file.putf(FH, 'ImageStringUp($im,4,%s,180,"%s (%s)",$text_color);',st+w/2,t_name,t_status) ; utl_file.new_line(FH, 1) ; st := st + 2* w ; end loop ; utl_file.new_line(FH, 1) ; utl_file.putf(FH , 'ImageString ($im,4,2,225,"%s",$violet);',sys_date) ; utl_file.new_line(FH, 1) ; utl_file.putf(FH , 'ImageString ($im,5,150,225,"Database : %s",$violet);',dbname) ; utl_file.new_line(FH, 1) ; utl_file.putf(FH,'ImageString ($im,4,360,225,"TABLE SPACES USED",$violet);') ; utl_file.new_line(FH, 1) ; utl_file.putf(FH,'Imagegif ($im, "tsu.gif");') ; utl_file.new_line(FH, 1) ; utl_file.putf(FH,'?>') ; utl_file.new_line(FH, 1) ; utl_file.fclose(FH) ; EXCEPTION when utl_file.invalid_path then dbms_output.put_line('Invalid path : check utl_file_path in initsid.ora .') ; raise ; END ; /
[Ed. note: The following was added by the author on 27 March 2002.] In the line:
utl_file.putf(FH,'Imagegif ($im, "tsu.gif");') ;
file name (tsu.gif) has been incorporated to save output to a file which can be used anywhere. Without it, the image has to be seen only in the browser. Netscape and IE behave differently if proper header is not included. The PHP manual says:
int imagegif (int im [, string filename]) ;
The filename argument is optional, and if left off, the raw image stream will be output directly. By sending an image/gif content-type using header(), you can create a PHP script that outputs GIF images directly.
Colleen M writes: I got this script working but I had to make a chnge in the PHP file. The last line was:
Imagegif ($im, "tsu.gif");
which I changed to:
I'm not sure if that's because of the version of PHP we're using (v. 3). [Ed. note: A few people had other problems with the initial version of this script, so the corrected version is above.]
Jeffrey E. wrote in with this question: "My Oracle is installed on a Digital Alpha 4100 server (Digital Unix 4.0D OS), but has no PHP installed. I setup a Linux (RedHat 6.2) box with APACHE, MYSQL and PHP. I'm wondering whether the script can be saved in my Lunix box to monitor my Oracle installed on my Digital box. If it is possible, do you mind giving me some guideline about how to do so?"
The author responded this way: The following are the possible ways to do that:
- If you have Oracle on Linux too, just run the script by using a SQL*NET remote database connection.
- If not, run the SQL on your Digital box and copy the 'tsu.php' file to the Linux box. You can copy it in the web server directory of Linux: /var/www/html for RH 7.x or /home/httpd/html for RH 6. This copying can be by floppy or by FTP. If you want all this to be automatic, create cron jobs to execute the script and ftp the file to the Linux box. Cron jobs are executed by the system at predefined times.
- The third option is to rewrite the script so that PHP will take care of connecting to Oracle and getting the desired information to plot the graph. I'll write such a program and post it soon.
I myself fall in situation #2 above. I have Oracle and PHP installed on different servers. Hope this helps you.
Verna L. wrote with another question: "I get a lot of "ORA-06502" errors when trying to run this script. How can I fix it?
The author responded: This error occurs when an arithmetic, numeric, string, conversion, or constraint error occurred. For example, this error occurs if an attempt is made to assign the value NULL to a variable declared NOT NULL, or if an attempt is made to assign an integer larger than 99 to a variable declared NUMBER(2). In our script, we have some lines:
t_size number(9) := 0 ; t_used number(9) := 0 ; t_remain number(9) := 0 ;This works for tablespaces having sizes less than or equal to "999,999,999" KB. For bigger tables, you can change 'number(9)' above to 'number(10)' or 'number(12)' as per your needs.
Yossi N. writes: On Oracle9i, I needed the following change (which is better to use):
t_name sys.DBA_TABLESPACES.tablespace_name%type ; t_status sys.DBA_TABLESPACES.status%type ;
For More Information
- What do you think about this tip? E-mail the Editor at firstname.lastname@example.org with your feedback.
- The Best Oracle Web Links: tips, tutorials, scripts, and more.
- Have an Oracle tip to offer your fellow DBA's and developers? The best tips submitted will receive a cool prize--submit your tip today!
- Ask your technical Oracle questions--or help out your peers by answering them--in our live discussion forums.
- Check out our Ask the Experts feature: Our SQL, database design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.
This was first published in March 2002