Identified by values reloaded

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 🙂

Leave a Reply

Your email address will not be published.