Oracle Database Discussions

Hi..

I am using few SQL scripts for the tablespace/datafile resize process ...

Calling SQL Script: /***/***/sql/dba_resize_start.sql

set verify off
set feedback off
set serveroutput on
execute dbms_output.enable(20000);

spool $SQL/dba_resize_run.sql
prompt set timing on
prompt set appinfo "Resizing Tablespaces to Min"

declare
cursor cur_ts is
select tablespace_name,sum(bytes) fs
from dba_free_space f
where tablespace_name not in (select TABLESPACE_NAME from ***dba.defragexclude)
--where tablespace_name like '%IND%'
--where tablespace_name like '%DAT%'
group by tablespace_name
order by *** desc;
*** number :=1;
v_include number;
begin
for *** in cur_ts loop
exit when ***>50;
dbms_output.put_line('define TABLESPACE_***='||***.tablespace_name);
dbms_output.put_line('@$SQL/dba_resize_tbs.sql');
***:=***+1;
end loop;
end;
/
spool off
set feedback on
prompt To Start Check: @dba_resize_run.sql
@$SQL/dba_resize_run.sql

Which builds: /******sql/dba_resize_run.sql

set timing on
set appinfo "Resizing Tablespaces to Min"
define TABLESPACE_***=***
@$SQL/dba_resize_tbs.sql
define TABLESPACE_***=***
@$SQL/dba_resize_tbs.sql
define TABLESPACE_***=***
@$SQL/dba_resize_tbs.sql
define TABLESPACE_***=***
@$SQL/dba_resize_tbs.sql
define TABLESPACE_***=***
@$SQL/dba_resize_tbs.sql
define TABLESPACE_***=***
@$SQL/dba_resize_tbs.sql
define TABLESPACE_***=***
@$SQL/dba_resize_tbs.sql
define TABLESPACE_***=***
@$SQL/dba_resize_tbs.sql
.
.
.

Which calls: /******sql/dba_resize_tbs.sql

set feed offselect 'Resizing Tablespace: '||('&&TABLESPACE_NM') tbs from dual;
set feed on
declare
v_filesize number;
v_ddl varchar2(255) default '';
resize_too_much exception;
file_size_too_small exception;
pragma exception_init(resize_too_much, -3297);
pragma exception_init(file_size_too_small, -1024);
begin
for var_datafile in (select file_name, bytes
from dba_data_files
where tablespace_name=(UPPER('&&TABLESPACE_NM')))
loop
v_filesize:=round(var_datafile.bytes/(1024*1024));
loop
begin
v_filesize:=v_filesize-24; <<===========shrinks file 24 MB at a time. Who knows why?
if v_filesize<=0 then exit; end if;
v_ddl:='alter database datafile '''||var_datafile.file_name||
''' resize '||v_filesize||'M';
execute immediate (v_ddl);
exception
when resize_too_much then exit;
when file_size_too_small then exit;
end;
end loop;
end loop;
end;
/
UNDEFINE TABLESPACE_***;
When this runs it I see a lot of these messages in the alert log:

Wed Dec 19 07:00:29 2007
alter database datafile '/***/***/***.dbf' resize 79089M
Wed Dec 19 07:00:29 2007
ORA-3297 signalled during: alter database datafile '/***/***/***.dbf'...
Wed Dec 19 07:00:29 2007
alter database datafile '/***/***/***.dbf' resize 81784M
ORA-3297 signalled during: alter database datafile '/***/***/***.dbf'...
Wed Dec 19 07:00:29 2007
alter database datafile '***/***/***.dbf' resize 81776M
ORA-3297 signalled during: alter database datafile '***/***/***.dbf'...
Wed Dec 19 07:00:29 2007
alter database datafile '***/***/***.dbf' resize 79453M
ORA-3297 signalled during: alter database datafile '***/***/***.dbf'...
Wed Dec 19 07:00:29 2007
alter database datafile '***/***/***.dbf' resize 80891M
ORA-3297 signalled during: alter database datafile '***/***/***.dbf'...
Wed Dec 19 07:00:29 2007
alter database datafile '***/***/***.dbf' resize 79530M

Please help me in letting these messages go away.

Thanks in advance.

Rgds
Geeta