DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: ORA-01426: numeric overflow

  1. #1
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    Hi

    I am getting this 1426 error when I execute this PL/SQL block, it sometimes works sometimes doesnt seems that if the file size is too big this blows. Anyone know why? Has it got anything to do with the binary_integer used in the code?


    set serveroutput on
    execute dbms_output.enable(2000000);

    declare
    cursor c_dbfile is
    select tablespace_name
    ,file_name
    ,file_id
    ,bytes
    from sys.dba_data_files
    where status !='INVALID'
    order by tablespace_name,file_id;

    cursor c_space(v_file_id in number) is
    select block_id,blocks
    from sys.dba_free_space
    where file_id=v_file_id
    order by block_id desc;

    blocksize binary_integer;
    filesize binary_integer;
    extsize binary_integer;

    begin

    /* get the blocksize of the database, needed to calculate the startaddress */

    select value
    into blocksize
    from v$parameter
    where name = 'db_block_size';

    /* retrieve all datafiles */

    for c_rec1 in c_dbfile
    loop
    filesize := c_rec1.bytes;
    <>
    for c_rec2 in c_space(c_rec1.file_id)
    loop
    extsize := ((c_rec2.block_id - 1)*blocksize + c_rec2.blocks*blocksize);
    if extsize = filesize
    then
    filesize := (c_rec2.block_id - 1)*blocksize;
    else
    /* in order to shrink the free space must be uptil end of file */
    exit outer;
    end if;
    end loop outer;
    if filesize = c_rec1.bytes
    then
    dbms_output.put_line('Tablespace: '
    ||' '||c_rec1.tablespace_name||' Datafile: '||c_rec1.file_name);
    dbms_output.put_line('Can not be resized, no free space at end of file.')
    ;
    dbms_output.put_line('.');
    else
    if filesize < 2*blocksize
    then
    dbms_output.put_line('Tablespace: '
    ||' '||c_rec1.tablespace_name||' Datafile: '||c_rec1.file_name);
    dbms_output.put_line('Can be resized uptil: '||2*blocksize
    ||' Bytes, Actual size: '||c_rec1.bytes||' Bytes');
    dbms_output.put_line('.');
    else
    dbms_output.put_line('Tablespace: '
    ||' '||c_rec1.tablespace_name||' Datafile: '||c_rec1.file_name);
    dbms_output.put_line('Can be resized uptil: '||filesize
    ||' Bytes, Actual size: '||c_rec1.bytes);
    dbms_output.put_line('.');
    end if;
    end if;
    end loop;
    end;
    /

  2. #2
    Join Date
    Apr 2001
    Posts
    118
    Yes, I could see how binary_integers would not be large enough to hold some of the computed values in this procedure. They only hold a 32 bit signed value, approx. -2 billion to 2 billion, so it doesn't seem that unexpected for you to generate numbers larger than that when you are multiplying values by your blocksize, which I will assume is in the thousands. If your db_block_size is 8096, then any block_id value > 265263 will result in too large of a number. Actually, any row whose sum of the blocks and block_id is greater than 265263 will cause this error.

    HTH,

    Heath

  3. #3
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Binary Integer can take up values from -2147483647 to +2147483647.
    Suppose if you have a data file of size 2048 MB (or > ), then size of the file is 2147483648 bytes, which can not be stored in binary integer.

    Check up the actual data file size. Instead of binary integer, use NUMBER data type.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width