oracle drop all schema tables
BEGIN
FOR cur_rec IN (SELECT OBJECT_NAME, OBJECT_TYPE
FROM ALL_OBJECTS -- Choose types
WHERE OBJECT_TYPE IN ('TABLE', 'VIEW', 'PACKAGE',
'PROCEDURE', 'FUNCTION', 'SEQUENCE'))
AND OWNER = 'owner_name'
LOOP
BEGIN
IF cur_rec.OBJECT_TYPE = 'TABLE'
THEN
EXECUTE IMMEDIATE 'DROP ' || cur_rec.OBJECT_TYPE || ' "'
|| cur_rec.OBJECT_NAME || '" CASCADE CONSTRAINTS';
ELSE
EXECUTE IMMEDIATE 'DROP ' || cur_rec.OBJECT_TYPE || ' "'
|| cur_rec.OBJECT_NAME || '"';
END IF;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.PUT_LINE('FAILED: DROP '
|| cur_rec.OBJECT_TYPE || ' "'
|| cur_rec.OBJECT_NAME || '"');
END;
END LOOP;
END;