Script for checking the status of a user and its designated tablespace, before drop

The following script will generate a "drop user" and a "drop tablespace" command if the tablespace is used exclusively by the user you want to drop.

-- ============================================================
-- drop_user_safety_check.sql
-- Run as SYSDBA
-- ============================================================

set pagesize 200
set linesize 200
set verify off
set feedback off
set trimspool on

prompt
prompt Enter username to analyse:
accept v_username char prompt 'Username: '

column username format a30
column account_status format a20
column tablespace_name format a30
column status format a12
column other_owners format a12
column generated_statement format a120

prompt
prompt ============================================================
prompt 1. Account status
prompt ============================================================

select
    username,
    account_status,
    lock_date,
    expiry_date
from
    dba_users
where
    username = upper('&v_username');

prompt
prompt ============================================================
prompt 2. Tablespaces the user actually writes to (has segments in)
prompt ============================================================

select distinct
    s.tablespace_name
from
    dba_segments s
where
    s.owner = upper('&v_username')
order by
    s.tablespace_name;

prompt
prompt ============================================================
prompt 3. Tablespace status
prompt ============================================================

select distinct
    t.tablespace_name,
    t.status,
    t.contents
from
    dba_tablespaces t
    join dba_segments s
      on s.tablespace_name = t.tablespace_name
where
    s.owner = upper('&v_username')
order by
    t.tablespace_name;

prompt
prompt ============================================================
prompt 4. Exclusivity check (must be owned ONLY by this user)
prompt ============================================================
col tablespace_name format a30
col owner_count format 999999
col owners format a20
with ts_usage as (
    select
        tablespace_name,
        count(distinct owner) as owner_count,
        listagg(distinct owner, ', ') within group (order by owner) as owners
    from
        dba_segments
    where
        tablespace_name in (
            select distinct tablespace_name
            from dba_segments
            where owner = upper('&v_username')
        )
    group by
        tablespace_name
)
select
    tablespace_name,
    owner_count,
    owners,
    case
        when owner_count = 1 then 'YES'
        else 'NO'
    end as exclusive_to_user
from
    ts_usage
order by
    tablespace_name;

prompt
prompt ============================================================
prompt 5. Generated DROP statements (SAFE ONLY)
prompt ============================================================
set heading off
set pagesize 0
spool exec.sql

-- Drop user (always generated, copy-ready) or execute the file exec.sql
select
    'DROP USER ' || upper('&v_username') || ' CASCADE;' as generated_statement
from
    dual;

-- Drop tablespaces ONLY if exclusive
with ts_usage as (
    select
        tablespace_name,
        count(distinct owner) as owner_count
    from
        dba_segments
    where
        tablespace_name in (
            select distinct tablespace_name
            from dba_segments
            where owner = upper('&v_username')
        )
    group by
        tablespace_name
)
select
    'DROP TABLESPACE ' || tablespace_name ||
    ' INCLUDING CONTENTS AND DATAFILES;' as generated_statement
from
    ts_usage
where
    owner_count = 1
order by
    tablespace_name;
spool off;

prompt
prompt ============================================================
prompt End of report
prompt ============================================================

set feedback on
exit