Oracle EBS Inactive User Cleanup SQL Generator
Dormant EBS accounts are an audit finding and, for some licensing models, a cost. This generator produces the queries to find them and a script to end-date them safely after review.
Fill in
Use % as a wildcard
Seeded and service accounts that must never be ended
1. Users inactive for the chosen period
SELECT fu.user_name,
fu.description,
fu.last_logon_date,
fu.creation_date,
fu.end_date
FROM fnd_user fu
WHERE (fu.end_date IS NULL OR fu.end_date > SYSDATE)
AND (fu.last_logon_date IS NULL OR fu.last_logon_date < SYSDATE - 120)
AND fu.creation_date < SYSDATE - 120
AND fu.user_name NOT IN ('SYSADMIN','GUEST','AUTOINSTALL','ASGADM')
AND UPPER(fu.user_name) LIKE UPPER('%')
ORDER BY fu.last_logon_date NULLS FIRST, fu.user_name;2. Users with passwords expiring soon
SELECT fu.user_name,
fu.password_date,
fu.password_lifespan_days,
fu.password_date + fu.password_lifespan_days AS expires_on
FROM fnd_user fu
WHERE (fu.end_date IS NULL OR fu.end_date > SYSDATE)
AND fu.password_lifespan_days IS NOT NULL
AND fu.password_date + fu.password_lifespan_days BETWEEN SYSDATE AND SYSDATE + 14
AND UPPER(fu.user_name) LIKE UPPER('%')
ORDER BY expires_on;3. Disable a reviewed list of users
-- Review the output of query 1 first. Run in a TEST instance before production.
SET SERVEROUTPUT ON
BEGIN
FOR r IN (SELECT fu.user_name
FROM fnd_user fu
WHERE (fu.end_date IS NULL OR fu.end_date > SYSDATE)
AND (fu.last_logon_date IS NULL OR fu.last_logon_date < SYSDATE - 120)
AND fu.creation_date < SYSDATE - 120
AND fu.user_name NOT IN ('SYSADMIN','GUEST','AUTOINSTALL','ASGADM')
AND UPPER(fu.user_name) LIKE UPPER('%'))
LOOP
fnd_user_pkg.disableuser(r.user_name);
DBMS_OUTPUT.PUT_LINE('Disabled: ' || r.user_name);
END LOOP;
COMMIT;
END;
/4. Verify the result
SELECT fu.user_name,
fu.end_date,
fu.last_update_date
FROM fnd_user fu
WHERE fu.end_date IS NOT NULL
AND fu.last_update_date > SYSDATE - 1
ORDER BY fu.last_update_date DESC;Run the disable block only after the owners of each account have been contacted. FND_USER_PKG.DISABLEUSER end-dates the account and keeps history; it does not delete the user, since EBS does not support deleting users. LAST_LOGON_DATE is only maintained when sign-on auditing is enabled, so confirm SIGNONAUDIT:LEVEL first. Integration and batch accounts often have no interactive logon and must be excluded.
Scheduled access hygiene reports
Ask how Netray can run and explain dormant user checks on your EBS instance on a schedule.
Optional. The tool and every export work without this. No spam.
How to use the generator
Set the idle days, the user pattern and the accounts to exclude. Copy query 1 and run it, then send the list to managers for confirmation. Only after sign-off run the PL/SQL block in a test instance, check the result with query 4, and then repeat in production inside an approved change window. Keep the query output as audit evidence.
- Exclude seeded and integration accounts every time
- Contact account owners before disabling
- Test the block in a cloned instance
How inactivity is measured
FND_USER.LAST_LOGON_DATE records the last successful sign-on when sign-on auditing is active. A null value means the user never logged in, or auditing was off. The creation date test prevents newly created accounts from being flagged before they have had time to log in. Choose idle days to match policy, often 90 to 180 days, and a shorter value for privileged accounts.
Password and expiry checks
Query 2 uses PASSWORD_DATE plus PASSWORD_LIFESPAN_DAYS to estimate when a password expires. Accounts with no lifespan never expire by password age, which is itself worth a review for service accounts. Interface accounts that expire silently are a common cause of failed integrations, so warn their owners before the date.
Common mistakes
Disabling a service account used by an interface and breaking an integration, trusting last logon dates when auditing was off, and ending users who still own scheduled concurrent requests. Check for scheduled requests by the user in FND_CONCURRENT_REQUESTS before ending an account, and reassign them first.
Frequently Asked Questions
How do I disable a user in Oracle EBS with SQL?
Call FND_USER_PKG.DISABLEUSER with the user name from an apps session, or end-date the user in the Define User form. Both set the end date. EBS does not support deleting users because history references them.
Why is LAST_LOGON_DATE empty for active users?
The value is populated only for sign-on events recorded while sign-on auditing is active, and some login paths do not update it. Check the SIGNONAUDIT:LEVEL profile option and treat null as unknown, not as proof the user never logged in.
Will ending a user cancel their scheduled requests?
No. Scheduled concurrent requests submitted by the user remain and may still run under that user's identity. Cancel or reassign them before ending the account.
How many idle days should I use?
Many organizations use 90 days for privileged users and 180 days for regular users, but follow your own security policy. Seasonal staff such as year end finance users may need a longer window.
If you want dormant-user and access reports to run on a schedule and be explained in plain language, Netray can set that up on top of your EBS data without moving it off site.
Related Tools
Oracle EBS User and Responsibility Audit SQL Generator
Fill in a user and responsibility pattern and get ready-to-run queries for active user access, administrator access and duplicate access reviews.
OracleOracle EBS Profile Options Reference
A searchable lookup of the profile options EBS administrators change most: sign-on security, concurrent processing, multi-org, logging and session settings.
OracleOracle EBS Concurrent Managers and Settings Reference
What each standard concurrent manager and service does, and what the main tuning settings mean, in one searchable table.
OracleOracle EBS Instance Health Assessment
A 10-question self-assessment that scores how well your EBS instance is run and points to the first areas to fix.
More Oracle tools
See all- Oracle EBS Inventory Tables Reference
- Oracle EBS WIP Tables Reference
- Oracle EBS Cycle Count Workload Calculator
- Oracle EBS Discrete Manufacturing Go-Live Checklist
- Oracle EBS Inventory Accuracy Readiness Assessment
- Oracle EBS Concurrent Request Phase and Status Reference
- Oracle EBS Responsibility Design Worksheet
- Oracle EBS GL Period Close Checklist
- Oracle EBS AP Invoice Interface Template
New to Oracle E-Business Suite? Start with the Oracle E-Business Suite guide and toolkit, or plan with Oracle EBS Roadmap Planner, Training Path Builder, ERP Certification Study Planner.
Keep exploring
Free tools
- Oracle EBS User and Responsibility Audit SQL GeneratorFill in a user and responsibility pattern and get ready-to-run queries for active user access, administrator access and duplicate access reviews.
- Oracle EBS User Access Review TemplateA periodic access review record for EBS users and responsibilities, with scope, evidence, exceptions and reviewer sign-off.
- Oracle EBS On-Hand Reconciliation SQL GeneratorGenerate a SQL query that compares on-hand quantity to the net of material transactions for an organization, to spot unexplained differences.
ERP fixes and how-tos
- SQL to Find Responsibilities Assigned to a User in Oracle EBSReady-to-run SQL against FND_USER, FND_USER_RESP_GROUPS and FND_RESPONSIBILITY_TL to list every responsibility assigned to an Oracle EBS user.
- How to Create an Item in Oracle EBSCreate an inventory item in Oracle EBS: Master Items form, master organization, templates, attributes, organization assignment, categories and common errors.
- Oracle EBS Outside Processing and Production SchedulingSet up outside processing in Oracle EBS WIP and Purchasing: OSP items, resources, departments, routing and requisitions, plus how discrete job scheduling works.
Runs with your ERP
- Esker Order Management and Oracle E-Business SuiteHow Esker captures customer POs from email, fax and PDF and creates sales orders in Oracle EBS: integration, data flows, setup, pitfalls and a private AI layer.
- Cloud Inventory (DSI) with Oracle E-Business Suite: Mobile Barcode InventoryCloud Inventory, formerly DSI, adds offline mobile barcode inventory to Oracle E-Business Suite and JD Edwards. Data flows, setup steps, pitfalls and AI notes.
- BarTender and Oracle E-Business Suite: Label Printing from OracleHow BarTender prints labels from Oracle EBS using Oracle XML print requests and Integration Builder: methods, data flows, setup steps, pitfalls and AI help.
AI on your ERP
- AI for Oracle E-Business Suite, Without Leaving On-PremHosting Oracle EBS 12.2 on-prem or in a private cloud? Add a private LLM over concurrent programs, interface tables and AP/PO data without moving to Fusion.
- AI for Oracle EBS in Industrial Manufacturing: WIP, MRP, and BOM Questions Answered DirectlyAI for Oracle E-Business Suite in industrial manufacturing: WIP jobs, BOMs, MRP, inventory and purchasing answered in plain English, private and read-only.
Integrations
- How to use ChatGPT with Oracle E-Business SuiteUsing ChatGPT with Oracle E-Business Suite: export vs live access, custom GPT actions over ISG REST, enterprise data controls and when to keep data on-prem.
- How to connect Claude to Oracle E-Business SuiteConnect Claude to Oracle E-Business Suite via MCP, the Anthropic API, AWS Bedrock or Vertex, and know when an on-prem open-weight model is the better fit.