CODEWITHSUNDEEP
oracle-databaseplsqloracle11g
Srikant
Srikant

Reputation: 129

Dropping all objects of a schema

The query below will return a statement that can be used to drop all the tables which are present in the current user A's schema (normal scenario).

select 'drop '||object_type||' '|| object_name || ';' 
from user_objects 
where object_type in ('VIEW','PACKAGE','SEQUENCE', 'PROCEDURE', 'FUNCTION', 'INDEX');

But what if this query is run by the DBA with a SYS or SYSDBA login? What objects are present in the user_objects view when logged in using sys/sysdba user? Will it drop all the tables of all the schemas in the database or will the query throw an error? Intention is to drop only objects of Schema 'A'.

Upvotes: 7

Views: 40740

Answers (4)

abhihello123
abhihello123

Reputation: 1728

CREATE OR REPLACE PROCEDURE PROC_ABHISEK_CLEAR_DB_SCHEMA AS
/******************************************************************************
   NAME:      PROC_ABHISEK_CLEAR_DB_SCHEMA
   PURPOSE:   Clears Database Schema by dropping vital Oracle Objects
   AUTHOR:    ABHISEK
   DATE:      28-Jan-2021
******************************************************************************/
TYPE list_string is table of varchar2(300);
query_list list_string;
delete_gen_query varchar2(500);
PROGRAM_NAME varchar2(50);
begin
    PROGRAM_NAME := 'DB CLEARING PROCESS';
    dbms_output.put_line(PROGRAM_NAME||' Started...');
    delete_gen_query := q'$select 'drop ' || object_type || ' ' || 
                           DECODE(object_type, 'TABLE', object_name || ' CASCADE CONSTRAINTS',
                             object_name) 
                           from user_objects 
                           where object_type in ('TABLE','VIEW','SEQUENCE') order by object_name$';
    execute immediate delete_gen_query bulk collect into query_list;
    dbms_output.put_line('Total Objects to be deleted: ' || query_list.count );
    
    for cnt in 1..query_list.count
    loop
        execute immediate query_list(cnt);
    end loop;
end PROC_ABHISEK_CLEAR_DB_SCHEMA;
/

Upvotes: 0

Feng Zhang
Feng Zhang

Reputation: 1960

FYI, the query below only generates all the sql, then you need to run the generated sql to actually get things done.

Code Snippet (with tables included):

select 'drop '||object_type||' '|| object_name || ';' from user_objects where object_type in ('VIEW','PACKAGE','SEQUENCE', 'PROCEDURE', 'FUNCTION', 'INDEX', 'TABLE');

Upvotes: 2

Alex Poole
Alex Poole

Reputation: 191570

The user_objects view has the current user's objects, so if run as SYS it would try to drop SYS's objects - very bad news, as it would destroy your database. You can read about the three versions of tthat view in the documentation.

For SYS to see another user's objects you should look at the dba_objects view instead, filtering on the user you're interested in; and include the target schema (owner) in the drop statement too:

select 'drop ' || object_type || ' "' || owner || '"."' || object_name || '";'
from dba_objects
where object_type in ('VIEW','PACKAGE','SEQUENCE', 'PROCEDURE', 'FUNCTION', 'INDEX')
and owner = 'A';

I've also included wrapping the object name (and less usefully the owner) in double quotes, in case there are any objects that were created with quoted identifiers.

If you included tables in the query and tried to run the output you might get errors from trying to drop dependent obects in the wrong order, i.e. dropping a parent table before its children. There are object types too, but if you ultimately want to drop everything, it might be simpler to drop and recreate the user - capturing and recreating their privileges too.

Upvotes: 6

ibre5041
ibre5041

Reputation: 5298

No when executed as SYS it will destroy your database. Try this. It will prompt 3 times for schema name to be cleared.

Beware this script might get stuck in a infinite loop, if you have curious object type in your schema (like SCHEDULER CHAIN - for example) 

set serveroutput on size unlimited
declare
  v_ItemCount integer;
begin
  SELECT count(*)
    INTO v_ItemCount
    FROM ALL_OBJECTS AO
   WHERE AO.OWNER = '&USER'
     AND AO.OBJECT_TYPE NOT IN ('INDEX', 'LOB')
     AND AO.OBJECT_NAME NOT LIKE 'BIN$%';
  while (v_ItemCount > 0) loop
    for v_Cmd in (SELECT 'drop ' || AO.OBJECT_TYPE || ' ' || '"'||AO.OWNER||'"'|| '.' || '"'||AO.OBJECT_NAME||'"' ||
                         DECODE(AO.OBJECT_TYPE,
                                'TABLE',
                                ' CASCADE CONSTRAINTS',
                                '') as DROPCMD,
                                AO.OWNER,
                                AO.OBJECT_TYPE,
                                AO.OBJECT_NAME
                    FROM ALL_OBJECTS AO
                   WHERE AO.OWNER = '&USER'
                     AND AO.OBJECT_TYPE NOT IN ('INDEX', 'LOB')
                     AND AO.OBJECT_NAME NOT LIKE 'BIN$%') 
    loop
      begin
        if v_Cmd.OBJECT_TYPE = 'SCHEDULE' then
          DBMS_SCHEDULER.DROP_SCHEDULE('"'||v_Cmd.OWNER||'"'||'.'||'"'||v_Cmd.OBJECT_NAME||'"', true);
        ELSIF v_Cmd.OBJECT_TYPE = 'JOB' then
          DBMS_SCHEDULER.DROP_JOB('"'||v_Cmd.OWNER||'"'||'.'||'"'||v_Cmd.OBJECT_NAME||'"', true);
        ELSIF v_Cmd.OBJECT_TYPE = 'PROGRAM' then
          DBMS_SCHEDULER.DROP_PROGRAM('"'||v_Cmd.OWNER||'"'||'.'||'"'||v_Cmd.OBJECT_NAME||'"', true);                              
        else
          execute immediate v_Cmd.dropcmd;
        end if;  
        dbms_output.put_line(v_Cmd.dropcmd);        
      exception
        when others then
          null; -- ignore errors
      end;
    end loop;
    SELECT count(*)
      INTO v_ItemCount
      FROM ALL_OBJECTS AO
     WHERE AO.OWNER = '&USER'     
       AND AO.OBJECT_TYPE NOT IN ('INDEX','LOB')
       AND AO.OBJECT_NAME NOT LIKE 'BIN$%';
  end loop;
  execute immediate 'purge dba_recyclebin';
end;

Upvotes: 1

Related Questions