-- CREATED BY DAVID ALDRIDGE and posted to DIZWELL.com
-- Use this to find accounts whose passwords match their usernames.
-- Read the Dizwell thread listed above.
-- I successfully tested this on April 13, 2005 - Edward ;-)
DECLARE
   l_old_password   VARCHAR2 (30);
   l_matches        CHAR (1);
   my_text          VARCHAR2 (300);
BEGIN
   FOR pwd IN (SELECT username, PASSWORD
                 FROM DBA_USERS
                WHERE username NOT IN ('SYS', 'SYSTEM'))
   LOOP
      l_old_password := pwd.PASSWORD;

      EXECUTE IMMEDIATE    'Alter user '
                        || pwd.username
                        || ' identified by '
                        || pwd.username;

      SELECT DECODE (COUNT (*), 1, 'Y', 'N')
        INTO l_matches
        FROM DBA_USERS
       WHERE username = pwd.username AND PASSWORD = l_old_password;

      IF l_matches = 'Y'
      THEN
         DBMS_OUTPUT.put_line ('This one: ' || pwd.username);
      END IF;

      my_text :=
            'Alter user '
         || pwd.username
         || ' identified by values '
         || ''''
         || l_old_password
         || '''';
--    dbms_output.put_line(my_text);
      EXECUTE IMMEDIATE (my_text);
   END LOOP;
END;
/