How to import oracle datapump file to a user which has an old version of imported schema ?

The question is straight forward asking about importing oracle data from datapump file into a user (schema in oracle) with an old schema of new imported schema.

The easiest way is :

1. Drop the users cascade

drop ‘user’ cascade;

2. Re- create the user

create user ‘user’ identified by ‘password’

3. Import the schema

imp user/password schema.dmp full=yes

But, if we are intended to avoid user dropping mechanisme, and only want to delete all object related to current user.

The simplest way is just browse the schema and drop object one by one. but, if the object is massive nearly thousands or hundreds, this will be bloody takes time.

Below is simple script to make this job easier just one click command only :

declare
objek varchar2(100);
cursor cur is
select *
from user_objects;
begin
for c in cur loop
begin
objek := ”;
if c.object_type = ‘VIEW’ then
objek := ‘drop view ‘ || c.object_name;
EXECUTE immediate objek;
elsif c.object_type = ‘TABLE’ then
objek := ‘drop table ‘ || c.object_name || ‘ cascade constraints’;
EXECUTE immediate objek;
elsif c.object_type = ‘SEQUENCE’ then
objek := ‘drop sequence ‘ || c.object_name;
EXECUTE immediate objek;
elsif c.object_type = ‘PACKAGE’ then
objek := ‘drop package ‘ || c.object_name;
EXECUTE immediate objek;
elsif c.object_type = ‘TRIGGER’ then
objek := ‘drop trigger ‘ || c.object_name;
EXECUTE immediate objek;
elsif c.object_type = ‘PROCEDURE’ then
objek := ‘drop procedure ‘ || c.object_name;
EXECUTE immediate objek;
elsif c.object_type = ‘FUNCTION’ then
objek := ‘drop function ‘ || c.object_name;
EXECUTE immediate objek;
elsif c.object_type = ‘SYNONYM’ then
objek := ‘drop synonym ‘ || c.object_name;
EXECUTE immediate objek;
elsif c.object_type = ‘INDEX’ then
objek := ‘drop index ‘ || c.object_name;
EXECUTE immediate objek;
elsif c.object_type = ‘PACKAGE BODY’ then
objek := ‘drop PACKAGE BODY ‘ || c.object_name;
EXECUTE immediate objek;
elsif c.object_type = ‘DATABASE LINK’ then
objek := ‘drop database link ‘ || c.object_name;
EXECUTE immediate objek;
end if;
exception
when others then
null;
end;
end loop;
end;
/

June, 20th 2012

Wednesday (@ADW)

Aah Ahmad Kusumah