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,

1 Answer 1

create or replace procedure p1 as
  for u in (select username from dba_users where account_status in ('EXPIRED', 'EXPIRED(GRACE)') and username not in ('SYS', 'SYSTEM', '...'))
    execute immediate 'alter user "' || u.username || '" account lock';
  end loop;

For 12c and higher you could use the ORACLE_MAINTAINED='N' for filtering the built-in users instead of the IN-list.

  • Hi sir, thanks for your help. About 15 minutes ago to your response, I created a procedure like this and it runs successfully. And also your response is a true. Thank you again.
    – jrdba
    Commented Jul 1, 2020 at 13:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.