Monday, August 17, 2015

Clean Oracle Database


Execute bellow commands to clean the database.


BEGIN
FOR c IN (SELECT table_name FROM user_tables) LOOP
EXECUTE IMMEDIATE ('DROP TABLE "' || c.table_name || '" CASCADE CONSTRAINTS');
END LOOP;

FOR s IN (SELECT sequence_name FROM user_sequences) LOOP
EXECUTE IMMEDIATE ('DROP SEQUENCE ' || s.sequence_name);
END LOOP;

FOR f IN (select object_name from user_objects where object_type = 'FUNCTION') LOOP
EXECUTE IMMEDIATE ('DROP FUNCTION ' || f.object_name);
END LOOP;

FOR t IN (select object_name from user_objects where object_type = 'TYPE') LOOP
EXECUTE IMMEDIATE ('DROP TYPE ' || t.object_name||' FORCE');
END LOOP;

FOR p IN (select object_name from user_objects where object_type = 'PROCEDURE') LOOP
EXECUTE IMMEDIATE ('DROP PROCEDURE ' || p.object_name);
END LOOP;

END;

No comments:

AWS EC2 - SSH locked with UFW

Need to update the instance's user data: 1. Stop the instance 2. Right click (windows) or ctrl + click (Mac) on the instance to open a c...