Use this statement to delete a user as well as the schemas associated with that user including all of the schema objects contained therein.
You need to have the system privilege DROP USER.
- If CASCADE is specified, all of the schemas of the user as well as their contents will be deleted. Furthermore, all foreign keys which reference the tables of the user are deleted database-wide.
- If schemas that belong to the user still exist, then CASCADE must be specified or these schemas must be explicitly deleted beforehand by using DROP SCHEMA statement.
- If the optional IF EXISTS clause is specified, then the statement does not throw an exception if the user does not exist.
- User sessions that were established before the commit of the DROP USER statement will still be active, but you can terminate them using the command KILL. To identifying these active user sessions, refer to the columns USER_NAME and EFFECTIVE_USER in system table EXA_DBA_SESSIONS, due to existence of the IMPERSONATE command. The EXA_DBA_SESSIONS table shows NULL for the dropped users.
DROP USER test_user1; DROP USER test_user2 CASCADE;