Oracle 9i: SYSTEM user is locked. How to unlock it?

Oracle has the concept of locking accounts. When a DBA (or the system) locks the account, no one can use it (even with the valid password) till the DBA (or system) unlocks the account.

New in the Oracle 9i installation for security is a new screen. This screen appears with a list of ALL the usernames (accounts) that come with Oracle. The installation gives you the chance to set new passwords for these accounts (and hence to unlock them). If you do NOTHING, the system leaves these accounts locked so a hacker can't use the default accounts.

The only account you will have access to is the sys account. You'll need to login for example:

sqlplus

Enter user-name: sys/change_on_install as sysdba

Once in you'll need to decide how you want to handle security.

For example:

1) You could just unlock all the accounts.

2) You could set new passwords for the accounts and unlock the accounts

3) You could choose to unlock and change the passwords on some but not all accounts.

Some suggestions on changing the accounts:

1) You could unlock all the accounts leaving the default password:

spool on unlocks.sql
select 'alter user ' || username || ' account unlock;' from dba_users

alter user SYS account unlock;
alter user SYSTEM account unlock;
alter user OUTLN account unlock;
alter user DBSNMP account unlock;
alter user WMSYS account unlock;
alter user ORDSYS account unlock;
alter user ORDPLUGINS account unlock;
alter user MDSYS account unlock;
alter user CTXSYS account unlock;
alter user XDB account unlock;
alter user ANONYMOUS account unlock;
alter user WKSYS account unlock;
alter user WKPROXY account unlock;
alter user ODM account unlock;
alter user ODM_MTR account unlock;
alter user OLAPSYS account unlock;
alter user RMAN account unlock;
alter user HR account unlock;
alter user OE account unlock;
alter user PM account unlock;
alter user SH account unlock;
alter user QS_ADM account unlock;
alter user QS account unlock;
alter user QS_WS account unlock;
alter user QS_ES account unlock;
alter user QS_OS account unlock;
alter user QS_CBADM account unlock;
alter user QS_CB account unlock;
alter user QS_CS account unlock;
alter user SCOTT account unlock;
alter user ERCF account unlock;
alter user PERFSTAT account unlock;
alter user TOAD account unlock;
alter user SAMPLE account unlock;

spool off

NOTE: At this point you can do the command:

host vi unlocks.sql
OR
host notepad unlocks.sql

and modify (e.g. remove unnecessary lines from) the unlocks.sql file. You don't have to do this action as the extra lines won't prevent the sql code from doing what you desire.

@unlocks.sql

2) You could set new passwords and/or unlock the accounts using the same basic concept (see #1).

select 'alter user ' || username || ' identified by new_secret_password account unlock;' from dba_users;

Where new_secret_password is a default "global" password for all account. This has advantages and disadvantages.

The downside is if someone figures out/discovers this global password, your system is open to them.

The upsides are you don't have:

1) Any of the default Oracle passwords in your system.
2) To remember lots of different passwords.

NOTE: You could set a "global" password and then individually change accounts you feel require their own unique passwords.

Credit:
Bertram Moshier
Oracle Certified Professional 8i and 9i DBA