OraFAQ Forum: SQL & PL/SQL » where is the error?

Home » SQL & PL/SQL » SQL & PL/SQL » where is the error?

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

where is the error? [message #9894]

Thu, 11 December 2003 21:21

yyy_3
Messages: 11
Registered: December 2003

Junior Member

set serveroutput on
declare
cursor mycur is
select *
from sys.dba_objects
where owner='GF_AUDIT' and object_type<>'INDEX';
str sys.dba_objects%ROWTYPE;
tmp varchar2(255);
cnt integer;
cursor_handle integer;
begin
cursor_handle := dbms_sql.open_cursor;

open mycur;
loop
fetch mycur into str;
exit when mycur%notfound;

tmp:= concat('drop ', str.object_type);
tmp:= concat(tmp, ' gf_audit.');
tmp:= concat(tmp, str.object_name);
dbms_sql.parse(cursor_handle, tmp, dbms_sql.native);
cnt := dbms_sql.execute(cursor_handle);
end loop;

EXCEPTION
WHEN OTHERS THEN
dbms_output.putline('have error');

dbms_sql.close_cursor(cursor_handle);

commit;
end;

when the SQL is executed, the results is 'have error',
why?

when I deleted the statement 'EXCEPTION WHEN OTHERS THEN', and excute the SQL, the error display:
'ORA-01555 '--rollback segment error
why?
please tell me what sql I should execute.

thanks

(I used oracle 8.05)

Report message to a moderator

Re: where is the error? [message #9896 is a reply to message #9894]

Thu, 11 December 2003 21:55

Maaher
Messages: 7065
Registered: December 2001

Senior Member

1. A commit is unnecessary in this context: a DDL operation like ALTER/CREATE/DROP will invoke an implicit commit. Besides, commiting in a WHEN OTHERS exception block????
2. The ORA-01555 (snapshot too old)is a common error. The reason why you are hitting it is that you are doing a 'fetch across commit'. Remember that each DDL statement causes an implicit commit. Meanwhile you keep your cursor open. A Better way to accomplish what you are trying to do is:

Declare
  v_drop_statement Varchar2(1000);
Begin
  Loop
    Select 'Drop '||object_type||' GF_AUDIT.'||object_name
      Into v_drop_statement
      From sys.dba_objects
     Where owner = 'GF_AUDIT'
       And object_type != 'INDEX'
       And rownum = 1;
    execute immediate v_drop_statement;
  End Loop;
Exception
  When no_data_found Then
    Null;
  When Others Then
    Dbms_output.Put_Line('Error: '||SQLERRM);
End;

MHE

PS: The code is not tested, so you better check before run!

Report message to a moderator

Re: where is the error? [message #9900 is a reply to message #9896]

Fri, 12 December 2003 07:36

Frank
Messages: 7901
Registered: March 2000

Senior Member

Why not just drop and recreate the user ?
Let the db handle it, very likely to be much faster.

hth
Frank

Report message to a moderator

Re: where is the error? [message #9906 is a reply to message #9900]

Fri, 12 December 2003 11:06

Thiru
Messages: 1089
Registered: May 2002

Senior Member

actually dropping the objects first and then dropping the user has been faster than 'drop user cascade',especially when the schema has a ton of objects. Lot more data dictionary maintenance is required when dropping the user. When dropping the objects it pretty much deals with obj$,tab$,seg$,uet$,fet$ etc .. This is still faster with locally managed tablespaces.

Dropping & Recreating tablespaces is another option,under certain circumstances.

-Thiru

Report message to a moderator

Previous Topic:	copy file from one dir to another in PL/SQL
Next Topic:	Huge data

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 19 03:02:50 CDT 2024