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
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;