warning: the script below is destructive and not 100% safe
update: it is unsafe to drop SYS_ objects, check for instance note 579399.1
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’)
and object_name like ‘SYS_%$’) 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
Hi Laurent
I added this to your script:
for f in (
//add this loop before the drop table loop!
select queue_table from user_queue_tables) loop
dbms_aqadm.DROP_QUEUE_TABLE(f.queue_table,true);
end loop;
for f in (
select job_name from user_scheduler_jobs) loop
dbms_scheduler.DROP_JOB(f.job_name,true);
end loop;
Of course this is very specific and the script is still incomplete.
Regards,
Stefan
thanks for the addition