RIDVAN EKER Oracle

September 26, 2007

Drop Objects

Filed under: Datapump — RIDVAN EKER @ 5:00 pm

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

Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: