Deleting All Objects Oracle

dropping all constraints....

begin
    for r in ( select table_name, constraint_name
               from user_constraints
               where constraint_type = 'R' )
    loop
        execute immediate 'alter table '||r.table_name
                          ||' drop constraint '||r.constraint_name;
    end loop;
end loop;

then dropping all user objects....

set header off
set feedback off
select 'drop '||object_type||' '|| object_name || ';' from user_objects 
  where object_type in ('VIEW','PACKAGE','SEQUENCE', 'PROCEDURE', 'FUNCTION') ;
select 'drop '||object_type||' '|| object_name || ';' from user_objects 
  where object_type in ('TABLE','INDEX') order by object_type;
  
select 'PURGE RECYCLEBIN;' from dual;
select 'commit;' from dual;

Oracle DeadLock Detection

Normally deadlock generates dump file and automatically is released by oracle system process

1) check v$session

sqlplus> select sid, serial#, username, command, lockwait, osuser from v$session where lockwait is not null

2) To kill a locked session, first need to find sid, serial and use

sqlplus>alter system kill session 'sid, serial#';

*** you need have dba priviledge to kill sessions

3. To find which SQL has lock wait

sqlplus>select sql_text from v$sqltext where (address,hash_value) in (select sql_address,sql_hash_value from v$session where lockwait is not null) order by address, hash_value, piece