User Profile Manipulation In Oracle

 --- We'll Find Profile for the user from below query 
select username,profile from dba_users where username=upper('&username');

--- Now we'll find profile resource limit for the profile 
select profile,resource_name,limit from dba_profiles where profile=upper('&profile_name');

--- We'll create new profile and then assign to scott user 

CREATE PROFILE c##scott_profile LIMIT
  COMPOSITE_LIMIT UNLIMITED
  SESSIONS_PER_USER UNLIMITED
  CPU_PER_SESSION UNLIMITED
  CPU_PER_CALL UNLIMITED
  LOGICAL_READS_PER_SESSION UNLIMITED
  LOGICAL_READS_PER_CALL UNLIMITED
  IDLE_TIME UNLIMITED
  CONNECT_TIME UNLIMITED
  PRIVATE_SGA UNLIMITED
  FAILED_LOGIN_ATTEMPTS 10
  PASSWORD_LIFE_TIME 180
  PASSWORD_REUSE_TIME UNLIMITED
  PASSWORD_REUSE_MAX UNLIMITED
  PASSWORD_VERIFY_FUNCTION NULL
  PASSWORD_LOCK_TIME 1
  PASSWORD_GRACE_TIME 7
  INACTIVE_ACCOUNT_TIME UNLIMITED;

--- We'll change profile resoure limits 
ALTER PROFILE c##scott_profile LIMIT
  password_lock_time 2;

--- We'll verify profile resource limit changed or not 
select profile,resource_name,limit from dba_profiles where profile=upper('&profile_name') 
and resource_name=upper('&resource_name');

--- We'll change user profile to new one 
ALTER USER scott PROFILE c##scott_profile;

--- We'll revert back profile 
alter user scott profile default;

--- We can drop the profile 
drop profile profile_name;

++++++++++++++++++++++++ User Details +++++++++++++++++++

SELECT username
	,account_status
	,to_char(created, 'DD/MM/YYYY HH:MI:SS AM') created
	,to_char(lock_date, 'DD/MM/YYYY HH:MI:SS AM') lock_date
	,to_char(expiry_date, 'DD/MM/YYYY HH:MI:SS AM') expiry_date
	,to_char(last_login, 'DD/MM/YYYY HH:MI:SS AM') last_login
	,to_char(password_change_date, 'DD/MM/YYYY HH:MI:SS AM') password_change_date
	,PROFILE
FROM dba_users
WHERE username = '&username';