Oracle Drop all the object from user

drop all the table
drop all the object from schema
drop all the table from own schema

SQL> connect scott/tiger
Connected.



begin

for i in ( select * from user_objects ) loop

EXECUTE immediate 'drop '|| i.object_type||' "'||i.object_name || '" ' ;

-- dbms_output.put_line ('drop '|| i.object_type||' "'||i.object_name || '" ;' );
-- dbms_output.put_line(i.object_type ||'.'||i.object_name ) ;

end loop;
end;

/

PL/SQL procedure successfully completed.




from sys

delete all the object from sys for other schema


begin

for i in ( select * from dba_objects where owner='BOAPTUS' and object_type in ('TABLE' ) ) loop

EXECUTE immediate 'drop '|| i.object_type||' '|| i.owner||'.'||'"'||i.object_name || '" ' ;

-- dbms_output.put_line ('drop '|| i.object_type||' "'||i.object_name || '" ;' );
-- dbms_output.put_line(i.object_type ||'.'||i.object_name ) ;

end loop;
end;

/





sql> select OBJECT_TYPE,count(*) from dba_objects
where OWNER='BOAPTUS'
group by OBJECT_TYPE ;

OBJECT_TYPE COUNT(*)
------------------- --------------------
LOB 4

http://anuj-singh.blogspot.com/2011/07/oracle-lob-delete.html


sql>purge dba_recyclebin;

DBA Recyclebin purged.

sql>

select OBJECT_TYPE,count(*) from dba_objects
where OWNER='BOAPTUS'
group by OBJECT_TYPE ;
no rows selected



SQL> select * from dba_objects where owner='BOAPTUS' ;

no rows selected



select 'alter table ' || table_name || ' disable constraint ' || constraint_name || ';' from user_constraints;


ALTER TABLE table_name DISABLE CONSTRAINT constraint_name;


select owner, constraint_name,table_name,index_owner,index_name
from dba_constraints
where (index_owner,index_name) in (select owner,index_name from dba_indexes
where tablespace_name='TABLESPACE_NAME');

=========================================================

drop all the object for particular user / schema






declare

v_object varchar2(30);
v_owner varchar2(30):='SCOTT' ;

begin


for x in (select object_type from dba_objects where owner= v_owner) loop

if x.object_type='TABLE' then

for i in ( select * from dba_objects where owner= v_owner and object_type= 'TABLE' ) loop

EXECUTE immediate 'drop '|| i.object_type||' '|| i.owner||'.'||'"'||i.object_name || '" cascade constraints ' ;

-- dbms_output.put_line ('drop '|| i.object_type||' "'||i.object_name || '" ;' );
-- dbms_output.put_line(i.object_type ||'.'||i.object_name ) ;

end loop ;

elsif x.object_type='MATERIALIZED VIEW' then

for i in ( select * from dba_objects where owner= v_owner and object_type= 'MATERIALIZED VIEW' ) loop

EXECUTE immediate 'drop '|| i.object_type||' '|| i.owner||'.'||'"'||i.object_name || '" ' ;

-- dbms_output.put_line ('drop '|| i.object_type||' "'||i.object_name || '" ;' );
-- dbms_output.put_line(i.object_type ||'.'||i.object_name ) ;

end loop ;

elsif x.object_type in ( 'DIMENSION','CLUSTER','SEQUENCE','VIEW','FUNCTION','PROCEDURE','PACKAGE','SYNONYM',
'DATABASE LINK','INDEXTYPE','PACKAGE BODY','TRIGGER') then

for i in ( select * from dba_objects where owner= v_owner
and object_type in ( 'DIMENSION','CLUSTER','SEQUENCE','VIEW','FUNCTION','PROCEDURE','PACKAGE','SYNONYM',
'DATABASE LINK','INDEXTYPE','PACKAGE BODY','TRIGGER')) loop

EXECUTE immediate 'drop '|| i.object_type||' '|| i.owner||'.'||'"'||i.object_name || '" ' ;

end loop ;

elsif x.object_type in ('TYPE','OPERATOR') then

for i in ( select * from dba_objects where owner= v_owner and object_type in ( 'TYPE','OPERATOR') ) loop

EXECUTE immediate 'drop '|| i.object_type||' '|| i.owner||'.'||'"'||i.object_name || '" force ' ;

end loop ;

elsif x.object_type in ('JAVA RESOURCE') then

for i in ( select * from dba_objects where owner= v_owner and object_type in ( 'JAVA RESOURCE') ) loop
EXECUTE immediate 'drop '|| i.object_type||' '|| i.owner||'.'||'"'||i.object_name || '" ' ;

end loop ;


elsif x.object_type in ('JAVA CLASS') then

for i in ( select * from dba_objects where owner= v_owner and object_type in ( 'JAVA CLASS') ) loop
EXECUTE immediate 'drop '|| i.object_type||' '|| i.owner||'.'||'"'||i.object_name || '" ' ;

end loop ;



else

for q in ( select queue_table from dba_queue_tables where owner= v_owner) loop
dbms_aqadm.DROP_QUEUE_TABLE(q.queue_table,true);
end loop;


for j in ( select job_name from dba_scheduler_jobs where owner= v_owner ) loop
dbms_scheduler.DROP_JOB(j.job_name,true);
end loop;

-- null ;

end if;

end loop;

EXECUTE immediate 'purge dba_recyclebin' ;
execute immediate 'purge recyclebin';


end;

/