To get the correct values string, here is another approach, when you have no access to sys.user$
select
username,
extractvalue(
xmltype(
dbms_metadata.get_xml('USER',username)),
'/ROWSET/ROW/USER_T/SPARE4_12/text()')
from dba_users;
USER SPARE4_12 ----------- ------------------------- SCOTT S:12345678...;T:FEDCBA... OPS$ORACLE SYSTEM S: XS$NULL S:00000000...
I would then ignore users with empty strings or S:00000000% or S: strings
For Scott, then use
alter user scott identified by values
'S:717EC24059A00B0DCC917C07E320EEB7F3F
09F7AD3BD8B8747F8DF88158A;T:9D7444ED23
6670CDE90F8F2D342B9D70E2C4BB00EDBC3514
452A1DFC5260C5F4E132960B5F5E163BC0B063
0652238CC316B009E7707BB96E48CCDD1CF7FB
F12D4AD8EBDA10776C3E55337FA2B69FB356';
dbms_metadata.get_xml('USER','SCOTT') gives us way more info than get_ddl 🙂