Tuesday, July 24, 2012

Drop all DB objects - Oracle

Created this for future reference. This SQL goes and drops all user objects in the current DB, great when messing with local Oracle XS DB
declare

v_str1 varchar2(200) := null;

cursor get_sql is select 'drop '||o.object_type||' '|| o.object_name|| DECODE(o.OBJECT_TYPE,'TABLE',' CASCADE CONSTRAINTS PURGE') v_str1
from user_objects o
where o.object_type in ('TABLE','VIEW','PACKAGE','TYPE','PROCEDURE','FUNCTION','TRIGGER','SEQUENCE') order by object_type,object_name;

begin

open get_sql;

loop

fetch get_sql into v_str1;
if get_sql%notfound
then exit;
end if;
execute immediate v_str1;
end loop;
close get_sql;
end;

5 comments:

  1. I did ennjoyed reading this content

    ReplyDelete
  2. You got it! I’ll be here when you’re ready for round two. Catch you soon and take care until then! Pakistani Clothes UK

    ReplyDelete
  3. This SQL script for dropping database objects reminds me of the wild crafting in Infinite Craft, a game I've been playing lately. You combine basic elements to create unbelievably complex things, much like this script combines commands to handle an entire database! It's fascinating to see how foundational pieces build something so powerful. What's the most surprising or useful thing you've "crafted" with a simple script like this?

    ReplyDelete

Building KubeSkippy: Learnings from a thought experiment

So, I got Claude Code Max and I thought of what would be the most ambitious thing I could try "vibe"? As my team looks after Kuber...