Drop an Oracle Schema/User from Linux CLI (SQL*Plus): Kill Sessions + DROP USER CASCADE

Sometimes you need to completely remove an Oracle schema (user) and recreate it fresh—common in dev/test environments. This guide shows how to:

• Connect from Linux CLI using SQL*Plus
• Kill active sessions for a user
• Drop the user/schema with CASCADE
• Recreate the user and grant privileges

Important: DROP USER ... CASCADE deletes all objects in that schema (tables, views, procedures, etc.). Use carefully.

1) Connect as SYSDBA (Linux CLI)

sqlplus / as sysdba

If you are using a PDB (recommended), switch to the correct PDB first:

SHOW PDBS;
ALTER SESSION SET CONTAINER=ORCLPDB1;

2) Kill all active sessions for the schema

If the user is connected, DROP USER may fail with “user is currently connected”. Kill sessions first.

Option A (Recommended): Generate kill commands and run them

This is simple and easy to audit:

SELECT 'ALTER SYSTEM KILL SESSION '''||sid||','||serial#||''' IMMEDIATE;'
FROM v$session
WHERE username = 'TEST_DATABASE';

Copy the output and execute it.

Option B: PL/SQL block

This kills all sessions in one go:

BEGIN
  FOR s IN (
    SELECT sid, serial#
    FROM v$session
    WHERE username = 'TEST_DATABASE'
  ) LOOP
    EXECUTE IMMEDIATE
      'ALTER SYSTEM KILL SESSION ''' || s.sid || ',' || s.serial# || ''' IMMEDIATE';
  END LOOP;
END;
/

3) Drop the user/schema

DROP USER TEST_DATABASE CASCADE;

CASCADE removes all objects owned by the user.

Conclusion

Dropping a schema in Oracle is a simple process that involves terminating active sessions, removing the user with DROP USER ... CASCADE, and recreating it if needed. This method is commonly used in development environments to reset a schema and start with a clean database structure.