Wednesday, August 24, 2016

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;

No comments:

Post a Comment