Thursday, March 7, 2013

Cleaning up a schema in Oracle


It's very useful, even for the partition table and partition index.
After purge recyclebin, the partition binXXXX would go away.

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
      IF cur_rec.object_type != 'DATABASE LINK' THEN
        EXECUTE IMMEDIATE 'DROP ' || cur_rec.object_type || ' ' || cur_rec.object_name;
        EXECUTE immediate 'purge recyclebin';
      END IF;
    EXCEPTION
    WHEN OTHERS THEN
      NULL;
    END;
  END LOOP;
END;
/

From http://ahsan-javed.blogspot.ca/2008/07/cleaning-up-schema-in-oracle.html

No comments:

Post a Comment