What happens to schema objects when you DROP USER but don't specify CASCADE?

Question

The Oracle documentation for DROP USER states: "Oracle Database does not drop users whose schemas contain objects unless you specify CASCADE".

My understanding is that all schemas must have an associated user account (though that account can be restricted from use by restricting CREATE SESSION command, etc...). But what actually happens to previously owned objects when you use the DROP USER command? If the user no longer exists... then what account owns those objects?

For Context, I'm a developer not a DBA and don't have DCL rights on my databases so I can't test this out myself. I am working a migration project where this command may be necessary but I'd like to better understand the implications before passing a request along to my Enterprise DBA team.

Answer 1

If you attempt to drop a user (e.g. THE_USER) without specifying CASCADE you will get the following error:

ORA-01922: CASCADE must be specified to drop 'THE_USER'

Either use the CASCADE option with your DROP USER statement or manually remove all user objects before dropping the user.

HTH