One possible solution would be to login as SYS and drop the schema itself, but I need to configure a single login for any database access, i.e., the user whose database objects will be deleted.
I did not find my specific solution online, but I owe thanks to alternative approaches in some other posts.
First, make sure that you connect to Oracle as the correct user, i.e., the one whose tables will be dropped.
BEGIN
FOR i IN (SELECT table_name FROM user_tables)
LOOP
EXECUTE IMMEDIATE('DROP TABLE ' || user || '.' || i.table_name || ' CASCADE CONSTRAINTS');
END LOOP;
END;
Similar commands can be used to drop all triggers, sequences, etc. For example,
BEGIN
FOR i IN (SELECT trigger_name FROM user_triggers)
LOOP
EXECUTE IMMEDIATE('DROP TRIGGER ' || user || '.' || i.trigger_name);
END LOOP;
END;
and
FOR i IN (SELECT sequence_name FROM user_sequences)
LOOP
EXECUTE IMMEDIATE('DROP SEQUENCE ' || user || '.' || i.sequence_name);
END LOOP;
END;
NB: if you drop all tables before triggers, your triggers will be renamed with special characters and the command might not work; make sure to drop triggers before tables.
