How to drop everything on a oracle schema

Friday, 8 June 2007, 1:39 | Category : Database, Technology
Tags :

BEGIN
FOR cur_rec IN (SELECT table_name, constraint_name
FROM user_constraints
WHERE constraint_type = ‘R’) LOOP
EXECUTE IMMEDIATE ‘ALTER TABLE ‘ || cur_rec.table_name || ‘ DROP CONSTRAINT ‘ || cur_rec.constraint_name;
END LOOP;
FOR cur_rec IN (SELECT object_name, object_type
FROM user_objects) LOOP
BEGIN
EXECUTE IMMEDIATE ‘DROP ‘ || cur_rec.object_type || ‘ ‘ || cur_rec.object_name;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
END LOOP;
execute immediate ‘purge recyclebin’;
END;
/

  • http://msanjay.weblogs.us Sanjay

    thanks i almost tried this but then used squirrel and graphically deleted all tables (cascade constraints)