| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: PL/SQL oddities
I have also seen this happen when the shared pool is too small. My *theory*
is there is a bug in shared pool memory management that allows shared pool
memory to get too fragmented to let a particular pacakage function
correctly. I have seen this issue on versions from 6.0.37 on VMS to 8.0.5
on HP-UX. The fix is to bump shared pool memory. You can test to see if
this is the problem by flushing the shared pool, running your sql, and if it
works you can bump your shared pool size at the next boot. Be advised
though that this will force all your users to reparse all their SQL and can
slow things right down until the cache stabalizes.
Allan
----- Original Message -----
From: "Oweson Flynn" <Oweson.Flynn_at_liberty.co.za>
To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com>
Sent: Thursday, November 23, 2000 6:51 AM
Subject: Re: PL/SQL oddities
> Hi Lee,
>
> The official error message is as follows:
> ORA-06502 PL/SQL: numeric or value error
>
> Cause: 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).
> Action: Change the data, how it is manipulated, or how it is declared so
> that values do not violate constraints.
>
>
> Looking at the code, it could be one of a number of things, but my first
> guess is that your tablespace name is longer than 12 characters. Change
the
> line from
> tname := cur_tablespace;
> to
> tname := SUBSTR( cur_tablespace, 1, 12); -- Only take the
> first 12 characters of the tablespace name
>
> Tell me if this helps!
>
> Regards
> Oweson Flynn
> ------------------------------------------------------------------
> Certified Oracle DBA
> The Flynn Consultancy
> Tel: 082-600-7-006
> Fax: (011) 782-9313
> EMail: oef_at_icon.co.za
>
>
>
> ----- Original Message -----
> To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com>
> Sent: Thursday, November 23, 2000 1:20 PM
>
>
> > 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
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
> > original message or any copy of it from your computer
> > system.
> > --
> > Please see the official ORACLE-L FAQ: http://www.orafaq.com
> > --
> > Author: lerobe - Lee Robertson
> > INET: LEROBE_at_acxiom.co.uk
> >
> > Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> > San Diego, California -- Public Internet access / Mailing Lists
> > --------------------------------------------------------------------
> > To REMOVE yourself from this mailing list, send an E-Mail message
> > to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> > the message BODY, include a line containing: UNSUB ORACLE-L
> > (or the name of mailing list you want to be removed from). You may
> > also send the HELP command for other information (like subscribing).
 |
 |