PL/SQL oddities

From: lerobe - Lee Robertson <LEROBE_at_acxiom.co.uk>
Date: Thu, 23 Nov 2000 11:17:29 -0000
Message-Id: <10689.122804@fatcity.com>

All,  

I have a procedure (inherited) that tots up various information on tablespaces (free space, used, pct free etc). This works fine on one DB but craps out on another on the same box. The first DB brings back the expected results (formatting is probably out in the mail)  

---

Table_Space Total_Size Space_Used Free_Space PCT_USED

---

DATA                63000         30170         32830      47
INDEX                9500             0          9500      0
RBS                      500           200           300      40
SYSTEM                   500            41           459       8
TEMP                    1000           998             2      99
 

However on the other DB I get the following  

---

Table_Space Total_Size Space_Used Free_Space PCT_USED

---

DATA                     22000         17049          4951      77
INDEX                     6170          4170          2000      67

DECLARE
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error ORA-06512: at line 31

The second DB has many more tablespaces. Could anyone please shed some light on this, my PL/SQL isn't brilliant and I cannot fix it.  

Regards  

Lee  

Code is as follows  

spool &1
set serveroutput on
set verify off
set feedback off  

DECLARE

        cursor C_TS is
                select tablespace_name,sum((bytes)/(1024*1024))
                from dba_data_files
                group by tablespace_name;    
        cur_tablespace  varchar2(30);
        tname           char(12);
        size_str        varchar2(132);
        cur_tot_size    number;
        cur_used_size   number;
        cur_free_size   number;
        cur_pct_used    number;
BEGIN
        open C_TS;
 
dbms_output.put_line('======================================================
================');
        dbms_output.put_line('Table_Space       Total_Size      Space_Used

Free_Space PCT_USED');  

dbms_output.put_line('======================================================
================');
 
        LOOP
                fetch C_TS into cur_tablespace,cur_tot_size;
                exit when C_TS%notfound;
 
                select sum((bytes)/(1024*1024))
                        into    cur_free_size
                        from    dba_free_space
                        where   tablespace_name=cur_tablespace;
 
                cur_used_size := cur_tot_size - cur_free_size;
                cur_pct_used  :=

100-(round(cur_free_size*100)/cur_tot_size+0.5);  

                tname := cur_tablespace;
 
                size_str :=     to_char(cur_tot_size,'999999999999999') ||
                                to_char(cur_used_size,'9999999999999') ||
                                to_char(cur_free_size,'9999999999999') ||
                                to_char(cur_pct_used,'9999999');
                dbms_output.put_line(tname || size_str);
        END LOOP;

END;
/
set serveroutput off  

Lee Robertson
Acxiom
Tel: 0191 525 7344
Fax: 0191 525 7007
Email: lerobe_at_acxiom.co.uk  

The information contained in this communication is confidential, is intended only for the use of the recipient named above, and may be legally privileged. If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please re-send this communication to the sender and delete the Received on Thu Nov 23 2000 - 05:17:29 CST