for pando : hwm for datafiles

[Sureshy]

Hi Pando.

Thanks for your reply. For some reason I could not reply further with my original post.

Could you please instruct me on how to run this script.
I pasted into an sql file and ran from sqlplus but got errors.

Oracle 8.1.6.

code:--------------------------------------------------------------------------------rem
rem Remarks: minimal size of a datafile is 2 Oracle blocks
rem resizing should always be a multiple of Oracle blocks
rem
rem Requirements: select on sys.dba_data_files
rem select on sys.dba_free_space
rem select on sys.v_$parameter
rem
rem --------------------------------------------------------------------

prompt Calculation of HighwaterMark of datafiles
prompt ---------------------------------------------------------------------------------------------------------

set feed off
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 number;
filesize number;
extsize number;

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;
/

set feed on
prompt

Thanks

Suresh

[pando]

well I ran it as a script, what error you get?

[Sureshy]

SQL> @C:\SQLPLUS\HWM_FOR_DATAFILES
Calculation of HighwaterMark of datafiles
----------------------------------------------------------------------------------------------------
<>
*
ERROR at line 35:
ORA-06550: line 35, column 1:
PLS-00103: Encountered the symbol ">" when expecting one of the following:
begin declare end exit for goto if loop mod null pragma raise
return select update while
<<
close current delete fetch lock insert open rollback
savepoint set sql execute commit forall

The symbol ">" was ignored.

Thanks

Suresh



SQL>

[pando]

sorry, it should be

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         number;
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;
/

missed <> ....

[Sureshy]

Hi Pando..

Still having trouble with this.

Now getting the following :-

ERROR at line 36:
ORA-06550: line 36, column 5:
PLS-00103: Encountered the symbol ">" when expecting one of the following:
begin declare end exit for goto if loop mod null pragma raise
return select update while
<<
close current delete fetch lock insert open rollback
savepoint set sql execute commit forall

The symbol ">" was ignored.

[pando]

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;
/

try this one, the cut & paste doesnt work very good in my PC it seems

[Edited by pando on 10-30-2001 at 10:25 AM]

[Sureshy]

Hi Pando..

Still getting errors..

might as well give up for now. I will look at it in detail later on and see if i can work out what it is doing and try and fix myself.

Thanks

Suresh



SQL> @c:\sqlplus\hwm_for_datafiles
81 /
declare
*
ERROR at line 9:
ORA-06550: line 9, column 6:
PLS-00103: Encountered the symbol "DECLARE" when expecting one of the
following:
. ( * @ % & - + ; / at for mod rem and or
group having intersect minus order start union where connect
||
ORA-06550: line 37, column 6:
PLS-00103: Encountered the symbol ">" when expecting one of the following:
begin declare end exit for goto if loop mod null pragma raise
return select update while
ORA-06550: line 79, column 2:
PLS-00103: Encountered the symbol "/" when expecting one of the following:
begin declare end exception exit for goto if loop mod null
pragma raise return select update while

[vinit]

first of all, thanx pando for the script.
Now. if you remove the <> and the 2 words 'outer' inside the pl/sql and you're done.
Try it out.

[Sureshy]

Thanks Pando
Thanks Vinit - working now.

Very handy script.

[amar]

Do not remove the "<>" from Pando's script - I bet Pando has the following in his script

"<< outer >>"

So, replace the "<>" with "<< outer >>" and Pando's script will work fine!


-amar