Today i was working on the migration of a legacy system from an old outdated server with Oracle 8 to a new one with Oracle 11 on it. I was thinking okay, normal routine drop the user like (drop user foo cascade) . Recreate the user and import the dump . But ( yes, sadly there was a but) i was not allowed to drop the user, and importing a dump into an Oracle database with objects in it doesn’t sound like a good idea. So i had to drop all objects from the schema, to be able to import everything without hassle.

I came up with the following script ( yes is know, it’s pretty standard, but this will do it as a first post :–) ) :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
SET SERVEROUTPUT ON SIZE 1000000
BEGIN
FOR cur_rec IN (
SELECT object_name, object_type
FROM user_objects
WHERE object_type IN (
'TABLE', 'VIEW', 'PACKAGE',
'PROCEDURE', 'FUNCTION', 'SEQUENCE'
)
)
LOOP
BEGIN
IF cur_rec.object_type = 'TABLE' THEN
EXECUTE IMMEDIATE 'DROP ' || cur_rec.object_type || ' "' ||
cur_rec.object_name || '" CASCADE CONSTRAINTS';
ELSE
EXECUTE IMMEDIATE 'DROP ' || cur_rec.object_type || ' "' ||
cur_rec.object_name || '"';
END IF;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.put_line(
'FAILED: DROP ' || cur_rec.object_type || ' "'
|| cur_rec.object_name || '"'
);
END;
END LOOP;
END;
/

So this was it. It’s simple as hell but it solved the problem . I hope you will find it useful.

OpenSo(u)rcery

Long time, no see. Don't worry, I'm not dead, not even remotely dead. I'm just pretty busy. I'm actually working on great stuff at work and …… Continue reading

Openduty @ Cloud Budapest

Published on April 12, 2015

Leaving Ustream

Published on February 07, 2015