Delete all objects in schema using batch file in Oracle

Question

I have below Select query which will generate delete statements to delete all objects in schema.

select 'DROP '||OBJECT_TYPE||' '||OWNER||'.'||OBJECT_NAME
       || case when OBJECT_TYPE = 'TABLE'
               then ' CASCADE CONSTRAINTS PURGE' else '' end
       ||';' 
  from all_objects
 where OWNER = 'RATOR_MONITORING';

I want to create batch file and suppose that instead of generating delete statements separetly I can create may be cursor or something and save it in batch file and run the batch file to delete all contents in schema. How to do it?

score 1 · Answer 1 · answered Dec 02 '15 at 11:49

Here is the script

begin
  for i in (select * from dba_objects where owner = 'SO' and object_type <> 'TABLE')
  loop
    execute immediate 'drop ' || i.object_type || ' ' || i.object_name;
  end loop;
  for j in (select * from dba_objects where owner = 'SO' and object_type = 'TABLE')
  loop
    execute immediate 'drop ' || j.object_type || ' ' || j.object_name || ' cascade constraints';
  end loop;
end;
/

This link provides details about how to create shell script or batch script to run sql scripts.

https://oracle-base.com/articles/misc/oracle-shell-scripting

this script gives me an error as cannot delete index used for enforcement of keys — Andrew, Dec 02 '15 at 13:22

score 1 · Accepted Answer · answered Dec 02 '15 at 12:18

You can find many script in the Internet. Neither of them work on 100%. There can we various gotchas. Like scheduler chains or materialized view groups.

This is the one I use (it is also inspired by one I found in the Internet)

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;

it works for me but what if dont want to delete scheduler job then how to change the code ? — Andrew, Dec 02 '15 at 13:21
And i dont want to provide input values. It should directly run — Andrew, Dec 02 '15 at 13:32
I usually connect to the database as sys (sysdba user) and I do not want to drop any internal database objects. if you want to clear your own schema when replace every occurrence of `ALL_` with `USER_`. Then remover `..OWNER = '&USER' ` from all where clauses. — ibre5041, Dec 02 '15 at 15:17
You can also extend the list of of ignored object types `OBJECT_TYPE NOT IN ('INDEX', 'LOB')` if you want to exclude something. — ibre5041, Dec 02 '15 at 15:19

score 1 · Answer 3 · answered Dec 02 '15 at 12:38

Here we go i have compiled a shell to perform the Purge Schema. Let me know if this helps.

#weekly report
#!/bin/ksh
export ORACLE_HOME=/opt/oracle/product/1020
export PATH=$ORACLE_HOME/bin:$PATH:.
export NLS_LANG=AMERICAN_AMERICA.AL32UTF8

ABC=`sqlplus -s <username>/<password>@<sid> <<+
set sqlbl on;
set serveroutput on;
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;
/
exit
+`

this script is getting hanged and its very complicated too. Do we have any other much simpler script to delete all objects ? — Andrew, Dec 02 '15 at 13:31
Yes we have.. Let me prepare one for you a simpler approach :). I basically copied the script which worked for u as mentioned in below answer. I just refracted it inside a shell and tried in my workspace it works fine — Avrajit Roy, Dec 02 '15 at 13:45

Delete all objects in schema using batch file in Oracle

3 Answers3