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?

Answer 1

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;

Answer 2

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

Answer 3

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
+`