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
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment