-
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;
/
-
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
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|