CODEWITHSUNDEEP
sqloracle-databaseoracle11g
fabrix_00
fabrix_00

Reputation: 163

drop user ORA-00604 and ORA-00054

I execute a impdp, but it does not finish because there isnt space in tablespace.

I stop the impdb and now i Need drop the new schema and I use this command:

SQL> drop user TEST cascade;
drop user TGK_EXOR_IFIL_008_432 cascade
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

I dont find any lock on database for this schema:

SQL> select * from v$session where username = 'TEST';

no rows selected

I use oracle 11g

Upvotes: 1

Views: 13617

Answers (2)

chandan kumar
chandan kumar

Reputation: 1

I faced a similar issue in an Oracle 12c database and got it resolved like this:

Connect to the database using command prompt:

sql> sys as sydba
(enter password)

Run

sql>@catqueue.sql under C:\app\dbuser\product\12.1.0\dbhome_1\RDBMS\ADMIN

(Possibly adapting the path to your ADMIN directory.)

Then run

drop user xxxx cascade;

This should be able to drop the user.

Upvotes: 0

mmmmmpie
mmmmmpie

Reputation: 3039

The impdp was either still running or it was rolling back the import itself.
You can try and wait for it to finish or kill it manually. Find it with this:

select o.object_name as "object_name",
       s.sid as "sid",
       s.serial# as "serial#",
       s.username as "username",
       sq.sql_fulltext as "sql_fulltext"
  from v$locked_object l, dba_objects o, v$session s,
       v$process p, v$sql sq
  where l.object_id = o.object_id
    and l.session_id = s.sid and s.paddr = p.addr
    and s.sql_address = sq.address;

credit
After finding the object that is locked you can kill the sid.
Or if a table is holding it up you can mark it read only:

alter table table_name read only;

Upvotes: 3

Related Questions