Dropping all objects of a schema

The user_objects view has the current user's objects, so if run as SYS it would try to drop SYS's objects - very bad news, as it would destroy your database. You can read about the three versions of tthat view in the documentation.

For SYS to see another user's objects you should look at the dba_objects view instead, filtering on the user you're interested in; and include the target schema (owner) in the drop statement too:

select 'drop ' || object_type || ' "' || owner || '"."' || object_name || '";'
from dba_objects
where object_type in ('VIEW','PACKAGE','SEQUENCE', 'PROCEDURE', 'FUNCTION', 'INDEX')
and owner = 'A';

I've also included wrapping the object name (and less usefully the owner) in double quotes, in case there are any objects that were created with quoted identifiers.

---

If you included tables in the query and tried to run the output you might get errors from trying to drop dependent obects in the wrong order, i.e. dropping a parent table before its children. There are object types too, but if you ultimately want to drop everything, it might be simpler to drop and recreate the user - capturing and recreating their privileges too.

FYI, the query below only generates all the sql, then you need to run the generated sql to actually get things done.

Code Snippet (with tables included):

select 'drop '||object_type||' '|| object_name || ';' from user_objects where object_type in ('VIEW','PACKAGE','SEQUENCE', 'PROCEDURE', 'FUNCTION', 'INDEX', 'TABLE');