Generate Reset Password statements

 A lot of companies we as DBA's work for have Test- and Development databases that are based on a copy of a Production database. Every time a lower environment databases is refreshed with a copy of production, there are always post-clone steps needed, to convert the production data into test- or development-data. A nice article to make this action easy can be read here.

One of these post-clone steps is a reset of passwords for all user accounts. Passwords on the lower environments should not be the same as on the production database.
Before a test- or development database is refreshed, it is smart to retrieve and keep all passwords of user accounts of the old copy of these databases and restore them after the refresh. 

Below a script is listed that generates "ALTER USER..." statements for all users in the database, except the common-users. It is known that resetting a password for a common user can only be done on the CDB level and not on the PDB level, so they are excluded.

The script makes use of a database directory called TMPDIR, that points to '/tmp'. First create this directory:

SQL> CREATE DIRECTORY TMPDIR AS '/tmp';

SQL> GRANT READ,WRITE ON DIRECTORY TMPDIR TO CLONE_USER;

It is assumed the one knows that grants have to be set before one can create a directory, or that one asks someone else (the DBA) to create a directory for him/her. The user CLONE_USER is a fictive user, e.g. the user that performs the post-clone actions.

Before the old copy of the Test-/Development database is destroyed/replaced, run this script, to save the old passwords.

The script:

declare
    cursor userlist is
        select username
        from   dba_users
        where      common = 'NO';
    l_usercmd varchar2(500);
    l_query   varchar(500);
    fd        utl_file.file_type;
begin
    -- Open SQL output file
    fd := utl_file.fopen('TMPDIR', 'ResetUserPasswords.sql', 'W');
    for r1 in userlist
    loop
        -- Generate CREATE USER command for every user
        l_usercmd := dbms_metadata.get_ddl('USER', r1.username);
        -- Reformat query so it can be used to reset the password
        l_query   :=
            trim(
                replace(replace(replace(substr(l_usercmd, 1, instr(l_usercmd, 'DEFAULT') - 1), 'CREATE', 'ALTER') || ';', chr(10), '')
                      , chr(13)
                      , ''
                       )
            );
        -- Write the query into the SQL file
        utl_file.put_line(fd, l_query);
    end loop;
    utl_file.put_line(fd, 'quit;');
    utl_file.fclose(fd);
end;

When successfully finished, a script "ResetUserPassword.sql" can be found in /tmp.
Execute this script on the new copy of the Test-/Development database.