1-- Specify CASCADE to drop all objects in the user's schema before dropping user.
2DROP USER MY_USER CASCADE;
1BEGIN
2 FOR cur_rec IN (SELECT OBJECT_NAME, OBJECT_TYPE
3 FROM ALL_OBJECTS -- Choose types
4 WHERE OBJECT_TYPE IN ('TABLE', 'VIEW', 'PACKAGE',
5 'PROCEDURE', 'FUNCTION', 'SEQUENCE'))
6 AND OWNER = 'owner_name'
7 LOOP
8 BEGIN
9 IF cur_rec.OBJECT_TYPE = 'TABLE'
10 THEN
11 EXECUTE IMMEDIATE 'DROP ' || cur_rec.OBJECT_TYPE || ' "'
12 || cur_rec.OBJECT_NAME || '" CASCADE CONSTRAINTS';
13 ELSE
14 EXECUTE IMMEDIATE 'DROP ' || cur_rec.OBJECT_TYPE || ' "'
15 || cur_rec.OBJECT_NAME || '"';
16 END IF;
17 EXCEPTION
18 WHEN OTHERS
19 THEN
20 DBMS_OUTPUT.PUT_LINE('FAILED: DROP '
21 || cur_rec.OBJECT_TYPE || ' "'
22 || cur_rec.OBJECT_NAME || '"');
23 END;
24 END LOOP;
25END;