66846425363d17967.png (62.48 KB, 下载次数: 449)
2023-3-30 15:11 上传
可以看出有些用户password有值，spare4也有值
当我们想用户password没有值的时候保留spare4的值，可以使用nvl函数
SQL> select name, nvl(password,spare4) from sys.user$;
713966425370cf0407.png (43.25 KB, 下载次数: 463)
2023-3-30 15:15 上传
运行后，若password为空，则返回spare4的值，若password不为空值，则返回其自身。
注意：使用nvl函数的前提是查询到的结果不为空，就是说查询语句本身不能返回null，只有查询记录存在的情况下nvl函数才会对记录中的字段赋值，如果没有记录赋值则为空谈了！
批量生成创建用户的命令；
SQL> select ' alter user ' || NAME || ' identified by values ''' || nvl(b.password,b.spare4) || ''';'
2 from sys.user$ b ,dba_users a
3 where B.NAME = A.USERNAME;
'ALTERUSER'||NAME||'IDENTIFIEDBYVALUES'''||NVL(B.PASSWORD,B.SPARE4)||''';'
----------------------------------------------------------------------------------------------------
alter user SYSTEM identified by values '2D594E86F93B17A1';
alter user SYS identified by values '8A8F025737A9097A';
alter user SYSBACKUP identified by values 'S:0000000000000000000000000000000000000000229CFFCF2E3971
1F43C6;T:0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
000000000000000000000000000000000000016DCCDE28526BD82FA66481E84D24B8B';
alter user SYSRAC identified by values 'S:00000000000000000000000000000000000000001914850E4E50E1DD6
AF7;T:0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000F679664D8CD59A1584F90F3539779851';
alter user OJVMSYS identified by values 'S:00000000000000000000000000000000000000001A989EF330631AC7
'ALTERUSER'||NAME||'IDENTIFIEDBYVALUES'''||NVL(B.PASSWORD,B.SPARE4)||''';'
----------------------------------------------------------------------------------------------------
56D7;T:000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
000000000000000000000000000000000006CB0BA7F0D9B8E0560B68267636D5D63';
alter user SYSKM identified by values 'S:0000000000000000000000000000000000000000C0F585D26BC5CA0258
82;T:00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000000000003444AB23942569D3CF1B135E5677FF26';
alter user OUTLN identified by values 'S:00000000000000000000000000000000000000000BE73A354C4081125B
9A;T:00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
000000000000000000000000000000000EF0236F490C9E04D932BE33489127045';
'ALTERUSER'||NAME||'IDENTIFIEDBYVALUES'''||NVL(B.PASSWORD,B.SPARE4)||''';'
----------------------------------------------------------------------------------------------------
alter user SYS$UMF identified by values 'S:00000000000000000000000000000000000000000D8C01CF538F2FA4
EEB7;T:000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
00000000000000000000000000000000000F6BF6A5CB0FF7E4D0FF7CF3ACA55140D';
alter user SYSDG identified by values 'S:0000000000000000000000000000000000000000A723572098C0F47EB0
63;T:00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
00000000000000000000000000000000060B89ABC0E52011C36523825C2A6A385';
| 欢迎光临 重庆思庄Oracle、Redhat认证学习论坛 (http://bbs.cqsztech.com/) | Powered by Discuz! X3.2 |