How to decommission an account with greatest caution

* Double check with the app team.

* If there's a non-prod database, decommission the account there first.

* If there's a session in (g)v$session for the account (user), find and talk to the person responsible. If not, check logon history in dba_audit_session or (if using unified auditing) unified_audit_trail in the past say 30 days:

select os_username, userhost, timestamp, returncode
from dba_audit_session
where username='&1' and action_name='LOGON' and timestamp>sysdate-30
order by 3;
select os_username, userhost, event_timestamp, return_code
from unified_audit_trail
where dbusername='&1' and action_name='LOGON' and event_timestamp>sysdate-30
order by 3;

* Check dependencies and object grants:

select owner, name, type, referenced_name from dba_dependencies where referenced_owner='APPUSER' order by 1,2,3;
select referenced_owner, referenced_name, referenced_type, type from dba_dependencies where owner='APPUSER' order by 1,2,3;
select * from dba_tab_privs where owner='APPUSER' order by 1,2,3;
select * from dba_tab_privs where grantee='APPUSER' order by 1,2,3;

and talk to the various app teams about the upcoming decommissioning.

Keep in mind that at least two types of objects can be referenced objects but when they point to objects outside the database that no longer exist, they can still be valid: external table and library. For example, if you see a library object whose code as shown in dba_libraries is /u01/nosuchfile.so, which does not exist, you should not drop the library object without checking for its dependents. (A directory object can refer to a non-existing directory but a directory object cannot be referenced inside the database.)


The following is basically a scream test, but it's a small one by only partially breaking the account.

* Lock the account if not locked, and wait some days. The duration of the wait is better set by you and the app team together.

* Rename a few tables and wait some days:

As owner: rename apptab to apptab2bdropped;
Not as owner: alter table appuser.apptab rename to apptab2bdropped;

Prepare rollback SQL scripts for fast rollback in emergency.

* Invalidate some PL/SQL objects (because you can't rename them) and check status some days later:

select object_id, status from xxx_objects where owner='APPUSER' and object_name='PLSQLOBJNAME';
--if the status is 'VALID', then
exec dbms_utility.invalidate(&objectid)
select status from xxx_objects where owner='APPUSER' and object_name='PLSQLOBJNAME';

Prepare rollback SQL scripts.

If some days later the status changes to 'VALID', then the PL/SQL object was run, and auto-compiled in runtime. If it remains 'INVALID', it was not run.

You can also audit usage of the PL/SQL objects:

audit execute on <PL/SQL object> by access;

and query dba_audit_trail where obj_name='<PL/SQL object>'.

* (Optional) To check if an index is used, start monitoring:

alter index appuser.appindex monitoring usage;

and check v$object_usage, or in newer versions, dba_object_usage . You can even monitor accumulated stats in v$index_usage_info.

* If after some days nothing is found non-functional, then using the rollback scripts created earlier, rename the tables back, and validate or compile the manually invalidated PL/SQL objects so you can have a good schema dump.

* Get other basic info as well, because some info such as grants from other accounts to the account to be dropped won't be part of the dump. Create file user.sql with the following lines (sys.user$ can only be queried by sys in 12c+; but it's not important):

set pages 10000 array 100 trims on
col spare4 for a70
spo &&1
select * from dba_users where username='&&1';
select password, spare4 from sys.user$ where name='&&1';
select * from dba_sys_privs where grantee='&&1' order by 1,2,3;
select * from dba_role_privs where grantee='&&1' order by 1,2,3;
select owner, table_name, privilege from dba_tab_privs where grantee='&&1' order by 1,2,3;
select grantee, table_name, privilege from dba_tab_privs where owner='&&1' order by 1,2,3;
select * from dba_ts_quotas where username='&&1';
select object_type, count(*) from dba_objects where owner='&&1' group by object_type order by 1;
select segment_type, count(*), round(sum(bytes)/1048576) mb from dba_segments where owner='&&1' group by segment_type order by 1;
spo off

Set linesize appropriately, say 130. Run it by passing the username in all capital, e.g.:

SQL> set lines 130
SQL> @user APPUSER

It creates APPUSER.lst in the current directory.

* Dump the schema with expdp and drop the user from the database. The dump file can be stored at a safe archiving location and/or given to the app team.