Unlock/restore users/passwords after database migration based on template with data

Unlock/restore users/passwords after database migration

In one of my experiments with migration using database templates with data I ended in situation that all users in restored database are locked and password are expired. This is normal because of security but not very useful after migration J. After spend many hours on searching for the solution I finally got one.

Here is the script:

Run SQL to unlock all user accounts (you can choose exception like SYS, SYSTEM…accounts which do not need to be unlocked):

SELECT 'ALTER USER '|| name ||' ACCOUNT UNLOCK;' FROM sys.user$ WHERE name IN (SELECT USERNAME FROM DBA_USERS where USERNAME NOT IN ('OUTLN',

'DIP',

'ORACLE_OCM',

'WMSYS',

'EXFSYS',

'XDB',

'ANONYMOUS',

'APPQOSSYS',

'XS$NULL',

'SYS',

'SYSTEM',

'DBSNMP',

'SYSMAN') AND account_status != 'OPEN' )

As a result, I got three sql stamenets for three users in my test:

ALTER USER TESTONE ACCOUNT UNLOCK;

ALTER USER HR ACCOUNT UNLOCK;

ALTER USER TESTTWO ACCOUNT UNLOCK;

Run those scripts and accounts should be unlocked.

Then, to get SQL which “restore” the passwords run this script:

SELECT 'ALTER USER '|| name ||' IDENTIFIED BY VALUES '''|| password ||''';' FROM sys.user$ WHERE name IN (SELECT USERNAME FROM DBA_USERS where USERNAME NOT IN ('OUTLN',

'DIP',

'ORACLE_OCM',

'WMSYS',

'EXFSYS',

'XDB',

'ANONYMOUS',

'APPQOSSYS',

'XS$NULL',

'SYS',

'SYSTEM',

'DBSNMP',

'SYSMAN') AND account_status != 'OPEN' )

…and I got:

ALTER USER TESTONE IDENTIFIED BY VALUES 'E851CC2EB21F4DB7';

ALTER USER TESTTWO IDENTIFIED BY VALUES '6AD44FBD54FC6A0F';

ALTER USER HR IDENTIFIED BY VALUES '4C6D73C3E8B0F0DA';

I executed those sql statements and users got their old passwords back.