drop all objects
Wednesday, May 14th, 2008warning: the script below is destructive and not 100% safe
A question was posted on the french forums of developez.net about how to drop all objects of an user. The drop user toto cascade; followed by create user toto identified by tott; was suggested as an easy answer, but I commented that create user must re-grant quotas, roles, system privileges, table privileges. Create user must also set the correct security status of the account (password, case-sensitive password, lock status, expiration status, profile), and the password history and failed login history is lost. Also it must set the default and temporary tablespaces.
It is also important to note that “drop user” requires dba privileges, when dropping the own object does not require privileges
To drop all objects I tried with plsql and a dictionary loop.
select object_type, count(*)
from user_objects
group by object_type;
OBJECT_TYPE COUNT(*)
------------------- ----------
INDEX 6
TYPE BODY 1
INDEXTYPE 1
PROCEDURE 1
JAVA CLASS 2
JAVA RESOURCE 1
JAVA SOURCE 1
FUNCTION 1
TABLE 10
TRIGGER 1
TYPE 1
MATERIALIZED VIEW 1
DATABASE LINK 1
PACKAGE BODY 1
CLUSTER 1
DIMENSION 1
OPERATOR 1
SEQUENCE 1
PACKAGE 1
19 rows selected.
purge recyclebin;
Purge successfully completed.
begin
for f in (
select object_type, object_name from user_objects
where object_type in (
‘MATERIALIZED VIEW’)) loop
execute immediate
‘drop materialized view “‘||f.object_name||’” preserve table’;
end loop;
for f in (
select table_name from user_tables) loop
execute immediate
‘drop table “‘||f.table_name||’” cascade constraints’;
end loop;
for f in (
select object_type, object_name from user_objects
where object_type in (
‘DIMENSION’,'CLUSTER’,'SEQUENCE’,
‘VIEW’,'FUNCTION’,'PROCEDURE’,
‘PACKAGE’,'SYNONYM’,'DATABASE LINK’,
‘INDEXTYPE’)) loop
execute immediate ‘drop ‘||
f.object_type||’ “‘||f.object_name||’”‘;
end loop
for f in (
select object_type, object_name from user_objects
where object_type in (
‘JAVA SOURCE’)) loop
execute immediate ‘drop ‘||
f.object_type||’ “‘||f.object_name||’”‘;
end loop;
for f in (
select object_type, object_name from user_objects
where object_type in (
‘JAVA RESOURCE’)) loop
execute immediate ‘drop ‘||
f.object_type||’ “‘||f.object_name||’”‘;
end loop;
for f in (
select object_type, object_name from user_objects
where object_type in (
‘JAVA CLASS’)) loop
execute immediate ‘drop ‘||
f.object_type||’ “‘||f.object_name||’”‘;
end loop;
for f in (
select object_type, object_name from user_objects
where object_type in (
‘TYPE’,'OPERATOR’)) loop
execute immediate ‘drop ‘||
f.object_type||’ “‘||
f.object_name||’” force’;
end loop;
end;
/
PL/SQL procedure successfully completed.
select * from user_objects
no rows selected.
All objects of the current schema disappeared, do not try this as sys, this script is destructive with no confirmation
Juniors in my company will enter dates like 30-JUN-52, which will be fine with YY and wrong with RR or RRRR. 
?
