SPOOL drop.sql SELECT 'DROP '||object_type||' '||object_name||';' FROM dba_objects WHERE owner='SCOTT'; SPOOL OFF @drop @drop
The above quickly creates a script to drop all of the user's objects. It then runs that script. The script is run twice to handle objects with Foreign Key constraints. But the above is not a single-shot as requested.