Re: Basic SQL Error Handling Qs ?

From: Amar Kumar Padhi <amar.padhi_at_gmail.com>
Date: Mon, 24 Dec 2007 20:35:15 +0400
Message-ID: <476FDFC3.6070208@gmail.com>

Hi Vivek,
I am ignoring the error you are getting for datafiles. Focussing on your objective, you could alternatively consider looping for table stats generation, trap the error you get for individual tables and ignore them. e.g.:-

begin
  for rec in (select owner, table_name from dba_tables where owner in ('AMAR')) loop

    begin
      dbms_stats.gather_table_stats(ownname => rec.owner, tabname => rec.table_name,

                                    estimate_percent => 10, cascade => 

true);

    exception

      when others then
        dbms_output.put_line('error in stats generation of - ' || 

rec.owner || '.' || rec.table_name);

    end;
  end loop;
end;
/

Thanks!
amar

VIVEK_SHARMA wrote:
> Folks
>
> Ran the following SQL with 48 Parallel processes (degree=48) to get the Error below.
> NOTE - This error is expected as some Datafiles are MISSING from the Database & hence
>
> Qs Is it possible for the same SQL Statement to continue executing inspite of the error i.e. to ignore the error
> Qs Will "execute sql whenever sqlerror continue" achieve this ELSE what other options exist?
>
> Cheers & Thanks
>
> SQL:-
> begin
> dbms_stats.gather_schema_stats(
> ownname => ...,
> degree => 48
> );
> end;
> ERROR:-
> ORA-12801: error signaled in parallel query server P059
> ORA-00376: file 1235 cannot be read at this time
> ORA-01111: name for data file 1235 is unknown - rename to correct file
> ORA-01110: data file 1235: '/oracle/.../MISSING01235'
>
>

--
http://www.freelists.org/webpage/oracle-l

Received on Mon Dec 24 2007 - 10:35:15 CST