Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

SHUTDOWN ABORT in ORACLE 10g

569230Mar 27 2007 — edited Mar 27 2007
Hi,
I wrote a procedure to be run before reinstalling a database. As I am not interested in rolling back, or waiting for transactions to finish, I am using SHUTDOWN ABORT to drop users. Everything works well in ORACLE 9i but it cannot restart in 10g - error:

ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor.

The procedure looks like:

SHUTDOWN ABORT
STARTUP RESTRICT

declare
cursor cnt_usr is
Select username from dba_users where username in (....some user names);
str_cmd varchar2(400):='';
begin
FOR usr IN cnt_usr LOOP
BEGIN
str_cmd := 'DROP USER '|| usr.username || ' CASCADE ' ;
dbms_output.put_line ('Executing: '|| str_cmd);
EXECUTE IMMEDIATE str_cmd;
EXCEPTION
WHEN OTHERS THEN str_cmd := ' ';
END;
END LOOP;

end;
/
ALTER SYSTEM DISABLE RESTRICTED SESSION;
exit;

Any idea what may cause the error?
Thank you
Dan
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Apr 24 2007
Added on Mar 27 2007
3 comments
585 views