List certain user ids and drop them in Oracle

Question

How do I list certain user ids and drop them?

For instance, I want to list user ids directly connected to database, in Oracle, and not any functional id.

I need to write a script for the automation

I want to write a script to list such direct ids and drop them automaticaly.

How can I do it?

Philᵀᴹ · Accepted Answer · 2015-12-07 12:30:50Z

To select a list if users connected to the database:

select distinct USERNAME
from v$session;

Note that some will be internal Oracle sessions.

To drop a user in Oracle, you use the DROP USER command:

DROP USER USERNAME;

Scripting it is easy, you can run in a loop and use EXECUTE IMMEDIATE, or spool a query from SQL*Plus to create the script for you:

  select 'drop user '||USERNAME||';' 
  from v$session
  where username not in (... LIST OF USERS YOU DONT WANT TO DROP ...);

I do wonder why on earth you'd want to automate this though. Seems like a very bizarre business case.

1 Answer 1