I have an Oracle database (Oracle 11gR2) and I want to create a job. The job will trigger my procedure.
The procedure must comply with the rules I have specified below:
-
If a user's expiry_date value is EXPIRED (select username, account_status, lock_date, expiry_date from dba_users where account_status='EXPIRED';), it will lock this user.
-
If a user's expiry_date value is EXPIRED and if it is already lock state, it will not do anything.
-
Exclude internal schemas for this operation.
I searched it but I didn't find any clear solution for it.
How can I create this procedure?
Best regards,
Best Answer
For 12c and higher you could use the
ORACLE_MAINTAINED='N'
for filtering the built-in users instead of theIN-list
.