Dropping all objects of a schema

Question

The query below will return a statement that can be used to drop all the tables which are present in the current user A's schema (normal scenario).

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

But what if this query is run by the DBA with a SYS or SYSDBA login? What objects are present in the user_objects view when logged in using sys/sysdba user? Will it drop all the tables of all the schemas in the database or will the query throw an error? Intention is to drop only objects of Schema 'A'.

Answer 1

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.