1

This query:

select distinct profile from dba_profiles;

Returns 3 rows:

  • default
  • default1
  • default2.

I want to change the PASSWORD_LIFE_TIME for all these profiles to UNLIMITED with a single query or without manually passing the profile names in query.

Because I do not know how many profiles are there in the system, the number of profiles will vary. I have to execute this query in multiple systems.

6
  • Can you explain why you want to do it in one query? This seems like a very odd requirement. Commented Feb 9, 2016 at 11:53
  • Because I do not know how many profiles are there in the system, the number of profiles will vary. I have to execute this query in multiple systems. Commented Feb 9, 2016 at 12:06
  • 1
    That seems like a really bad idea to me. If you're determined to do it, I'll post an answer... Commented Feb 9, 2016 at 12:20
  • Please be aware that changing the profiles does nothing to the existing expiry_date for your users. The only time password_lifetime comes into play is when a user password is changed. Also, I agree it seems like a bad idea. If you have so many profiles that it is a serious problem to do them one at a time, then I'd question your use of profiles.
    – EdStevens
    Commented Feb 9, 2016 at 12:52
  • Thanks EdStevens, then I think, query to change the password_life_time should be included before the user creation. Commented Feb 9, 2016 at 12:55

2 Answers 2

3

You can simply do it in PL/SQL in a loop:

begin
  for c in (select distinct profile from dba_profiles)
  loop
    execute immediate 'alter profile ' || c.profile || ' limit password_life_time unlimited';
  end loop;
end;
/
1
DECLARE
  v_query varchar2(1024) := '';
BEGIN
FOR x IN (SELECT DISTINCT PROFILE FROM dba_profiles)
loop
  v_query := 'alter profile ' || x.PROFILE || ' limit password_life_time unlimited';
  execute immediate v_query;
end loop;
END;
/
1
  • A couple words/explanation would be better than just a piece of code alone. Commented Feb 9, 2016 at 13:37

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.