Drop ‘FUNCTION’,'PACKAGE’,
‘PROCEDURE’,'SEQUENCE’,'SYNONYM’,
‘VIEW’,'TRIGGER’,'TABLE’
To be able run this procedure, privilegtes must be given directly to STAGING_DROP_USER
CREATE OR REPLACE PROCEDURE STAGING_DROP_USER.test AS
hand NUMBER ;
cursor c1 is
select object_type, owner, object_name from dba_objects
where object_type in (‘FUNCTION’,'PACKAGE’,'PROCEDURE’,'SEQUENCE’,'SYNONYM’,'VIEW’,'TRIGGER’)
and owner in (select username from dba_users where profile=’SCHEMA’);
r1 c1%rowtype;
–we cannot drop nested tables
cursor c2 is
select owner, table_name from dba_tables where owner in (select username from dba_users where profile=’SCHEMA’)
and table_name not in (select table_name from dba_nested_tables
where owner in (select username from dba_users where profile = ‘SCHEMA’));
r2 c2%rowtype;
BEGIN
– first have to drop plsql and other objects that table_exists_action does not deal with
FOR r1 IN c1 LOOP
execute immediate ‘drop ‘||r1.object_type||’ ‘||r1.owner||’.”‘||r1.object_name||’”‘ ;
END LOOP ;
FOR r2 IN c2 loop
execute immediate ‘drop table ‘||r2.owner||’.”‘||r2.table_name||’” CASCADE CONSTRAINTS PURGE’ ;
END LOOP ;
END ;
/
