PLSQL - Drop all database objects of a user

Question

I'm trying to use a procedure (no parameters) to drop all of the user-created database objects located within the schema from where the procedure is launched, but I'm really not sure on how to go about this. Here's what I have so far, but I think I'm going about this the wrong way.

create or replace procedure CLEAN_SCHEMA is
cursor schema_cur is
select 'drop '||object_type||' '|| object_name||  DECODE(OBJECT_TYPE,'TABLE',' CASCADE CONSTRAINTS;',';')
from user_objects;
schema_rec schema_cur%rowtype;
begin
select 'drop '||object_type||' '|| object_name||  DECODE(OBJECT_TYPE,'TABLE',' CASCADE CONSTRAINTS;',';')
into schema_rec
from user_objects;
end;
/

Willtrude

Oops!!! Are you not shooting yourself? CLEAN_SCHEMA might get picked up in the cursor for deletion. – Gert

Answer 1

create or replace
FUNCTION                DROP_ALL_SCHEMA_OBJECTS RETURN NUMBER AS
PRAGMA AUTONOMOUS_TRANSACTION;
cursor c_get_objects is
  select object_type,'"'||object_name||'"'||decode(object_type,'TABLE' ,' cascade constraints',null) obj_name
  from user_objects
  where object_type in ('TABLE','VIEW','PACKAGE','SEQUENCE','SYNONYM', 'MATERIALIZED VIEW')
  order by object_type;
cursor c_get_objects_type is
  select object_type, '"'||object_name||'"' obj_name
  from user_objects
  where object_type in ('TYPE');
BEGIN
  begin
    for object_rec in c_get_objects loop
      execute immediate ('drop '||object_rec.object_type||' ' ||object_rec.obj_name);
    end loop;
    for object_rec in c_get_objects_type loop
      begin
        execute immediate ('drop '||object_rec.object_type||' ' ||object_rec.obj_name);
      end;
    end loop;
  end;
  RETURN 0;
END DROP_ALL_SCHEMA_OBJECTS;

Create the above function (autonomous so DDL can be called via a function) then you can just:

select DROP_ALL_SCHEMA_OBJECTS from dual;

when you want to drop all your objects, make sure you dont try to drop the proc your running (i dont care about the procs thats why i dont have procs or functions in the object_type list)

if you want to drop everything you need an anonymous block

but i needed to be able to do this from a tool that only allowed ansi sql (not plsql) hence a stored proc.

Enjoy.

Sternutation

I came to learn about how to delete one function. I'll just repeat what I learned since no one tells how the deletion works. execute immediate(string) e.g. execute immediate('drop function myStupidFunction') ~ check the spaces. – Byrnie

Answer 2

declare
  cursor ix is
    select *
      from user_objects
     where object_type in ('TABLE', 'VIEW', 'FUNCTION', 'SEQUENCE');
begin
 for x in ix loop
   execute immediate('drop '||x.object_type||' '||x.object_name);
 end loop;
end;

Monteverdi

rror report: ORA-00933: SQL command not properly ended ORA-06512: at line 8 00933. 00000 - "SQL command not properly ended" – Normannormand

Just one remark: To make it work with the 'JAVA SOURCE' type, one must double-quote the object_name, since they can contain special characters that needs quoting (e.g. /2bbec77c_JsonEncoding). Beside this small (but necessary) modification, it works great. Thanks! – Speak

I would add another cursor just for tables and in in execution add ' CASCADE CONSTRAINTS' as tables will not always be dropped. – Spalding

Answer 3

Unless the user has hard to reapply permissions, its probably easier to just drop the user and recreate them.

Clipping

DROP USER userName CASCADE; "Specify CASCADE to drop all objects in the user's schema before dropping the user." Check docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/… – Thibeault

Answer 4

Thanks Martin Brambley,

I feel we can simplify your answer in the following way.

CREATE OR REPLACE
procedure  DROP_ALL_SCHEMA_OBJECTS AS
PRAGMA AUTONOMOUS_TRANSACTION;
cursor c_get_objects is
  select object_type,'"'||object_name||'"'||decode(object_type,'TABLE' ,' cascade constraints',null) obj_name
  FROM USER_OBJECTS
  where object_type in ('TABLE','VIEW','PACKAGE','SEQUENCE','SYNONYM', 'MATERIALIZED VIEW', 'TYPE')
  order by object_type;
BEGIN
  begin
    for object_rec in c_get_objects loop
      execute immediate ('drop '||object_rec.object_type||' ' ||object_rec.obj_name);
    end loop;
  end;
END DROP_ALL_SCHEMA_OBJECTS;

/

execute DROP_ALL_SCHEMA_OBJECTS;

Sinh

Answer 5

What you've got is a good start.

Here is the rest:

• You have a cursor AND a select statement. You only need the cursor.
• Your next step is to call the drop statement using dynamic PLSQL. I'd use the EXECUTE IMMEDIATE statement. Its more elegant and preformance friendly to just select the name of the thing you're dropping and submit it as a bind variable to EXECUTE IMMEDIATE.
• In order to drop the objects of the schema calling the method and not the schema owning the method you have to use "AUTHID CURRENT_USER". See the Oracle documentation for more info.
• Other things to drop: packages, functions, procedures (the system will likely hang then timeout if you try to drop this method while its running), Java classes, triggers, views, types

Lastly, this is obviously a very dangerous method so you may want to consider putting it in a script instead of a stored procedure so it isn't left in the database for anyone to run.

Alizaalizarin

Answer 6

You're close - as someone else has noted you need an "EXECUTE IMMEDIATE" for the statement. You should consider:

• Instead of creating a procedure to do this, run this as an anonymous PL/SQL block so you don't have the issue of trying to drop a procedure that is running.

• Add a test for object type of TABLE and for that case modify the drop statement to include the cascade option to handle tables that are "parents" of other tables via foreign key constraints. Remember that you'll probably be generating the cursor list in an order that doesn't consider dependencies that will block the drop.

• Also on the subject of dependencies, it is probably best to drop tables first (add a DECODE in your cursor that assigns a lower numeric value to this object type and order the cursor select by this value). If you have Oracle objects of type TYPE that are used as column types in a table definition the table must be dropped first.

• If you use Oracle Advanced Queuing the objects related to this MUST be dropped with the AQ package API calls. Although you can drop the Oracle-generated tables for queue support with a regular DROP TABLE, you will find yourself in the catch-22 position of then not being able to drop the related queues nor add them back. Up to version 10g at least you couldn't even drop the containing schema without putting the database in a special mode when this situation existed

Electrodeposit

Answer 7

Thanks Martin Brambley and Vijayan Srinivasan!

But Vijayan Srinivasan's version is not correct, because dependent objects of type 'TYPE' sometime generates errors during drop them:

ORA-02303: cannot drop or replace a type with type or table dependents

My version drop ALL objects from Schema with additional:

• drop procedures and functions (expect 'DROP_ALL_SCHEMA_OBJECTS')
• drop all jobs and dbms_jobs
• drop all db_links
• do not drop nested tables, because DROPing of nested tables not supported

CREATE OR REPLACE
procedure  DROP_ALL_SCHEMA_OBJECTS AS
PRAGMA AUTONOMOUS_TRANSACTION;
cursor c_get_objects is
  select uo.object_type object_type_2,'"'||uo.object_name||'"'||decode(uo.object_type,'TABLE' ,' cascade constraints',null) obj_name2
  FROM USER_OBJECTS uo
  where uo.object_type in ('TABLE','VIEW','PACKAGE','SEQUENCE','SYNONYM', 'MATERIALIZED VIEW', 'FUNCTION', 'PROCEDURE')
        and not (uo.object_type = 'TABLE' and exists (select 1 from user_nested_tables unt where uo.object_name = unt.table_name))
        and not (uo.object_type = 'PROCEDURE' and uo.object_name = 'DROP_ALL_SCHEMA_OBJECTS')
  order by uo.object_type;
cursor c_get_objects_type is
  select object_type, '"'||object_name||'"' obj_name
  from user_objects
  where object_type in ('TYPE');
cursor c_get_dblinks is
  select '"'||db_link||'"' obj_name
  from user_db_links;
cursor c_get_jobs is
  select '"'||object_name||'"' obj_name
  from user_objects
  where object_type = 'JOB';
cursor c_get_dbms_jobs is
  select job obj_number_id
  from user_jobs
  where schema_user != 'SYSMAN';
BEGIN
  begin
    for object_rec in c_get_objects loop
      execute immediate ('drop '||object_rec.object_type_2||' ' ||object_rec.obj_name2);
    end loop;
    for object_rec in c_get_objects_type loop
      begin
        execute immediate ('drop '||object_rec.object_type||' ' ||object_rec.obj_name);
      end;
    end loop;
    for object_rec in c_get_dblinks loop
        execute immediate ('drop database link '||object_rec.obj_name);
    end loop;
    for object_rec in c_get_jobs loop
        DBMS_SCHEDULER.DROP_JOB(job_name => object_rec.obj_name);
    end loop;
    commit;
    for object_rec in c_get_dbms_jobs loop
        dbms_job.remove(object_rec.obj_number_id);
    end loop;
    commit;
  end;
END DROP_ALL_SCHEMA_OBJECTS;

/

execute DROP_ALL_SCHEMA_OBJECTS;
drop procedure DROP_ALL_SCHEMA_OBJECTS;

exit;

Provinciality

Answer 8

I indicate below a procedure that can be used to delete all objects. The NOT IN can be adjusted, currently it includes, among others, PROCEDURE so as not to try to delete the procedure itself.

CREATE OR REPLACE PROCEDURE DROP_ALL_SCHEMA_OBJECTS 
IS
    CURSOR OBJECTS IS
    SELECT 'DROP '||OBJECT_TYPE||' '||USER||'.'||object_name|| CASE OBJECT_TYPE WHEN 'TABLE' THEN ' CASCADE CONSTRAINTS' END DROPALL
    FROM USER_OBJECTS
    WHERE OBJECT_TYPE NOT IN ('INDEX','TRIGGER','LOB','PROCEDURE');
    
BEGIN
    FOR SENTENCE IN OBJECTS 
    LOOP
        EXECUTE IMMEDIATE(SENTENCE.DROPALL);
    END LOOP;
END;
/

EXEC DROP_ALL_SCHEMA_OBJECTS;

Lugger

Use the right code fragment delimiters: triple backticks (the initial ones followed by plsql to indicate the language) – Rule