Creating a login profile is very easy in Oracle Enterprise Manager, click click and you’re effectively done. If you’re not using OEM or have access to OEM, you may want to know how to do it using SQLPlus.
First create the non-expiring login profile:
create profile DBA LIMIT PASSWORD_VERIFY_FUNCTION NULL PASSWORD_LIFE_TIME UNLIMITED;
We need to assign a user or a hundred to it. In this case, just the SYS and SYSTEM logins:
alter user SYS profile DBA; alter user SYSTEM profile DBA;
alter user SYS identified by ....; alter user SYSTEM identified by .....;
What if you don’t know the password for a particular user? Easy, use the password hash! Let’s get the password hash:
In Oracle 10g:
select username, password from dba_users where username = 'myuser';
In Oracle 11g:
select dbms_metadata.get_ddl('USER','myuser') from dual;
Plug in the hash into the alter user statement:
alter user myuser identified by "<insert hash here>";
For more details on the password hash, see Password Hash In Oracle 11g.