ユーザ情報確認SQL

・SQL/Plus用ユーザ、ロール確認スクリプト
※password_versions列は11gから追加されたものなので10gの場合はコメントアウトして実行する

set pagesize 1000  linesize 200
col username                 for a25 heading Name
col default_tablespace       for a12 heading Default_TS
col temporary_tablespace     for a12 heading Temp_TS
col granted_role             for a30 heading Roles
col default_role             for a10 heading Default?
col admin_option             for a7  heading Admin?
col profile                  for a12 heading Profile
col account_status           for a20 heading Status
col password_versions        for a10 heading Pass
break on username skip 1 on account_status on default_tablespace on temporary_tablespace on profile
SELECT a.username,
       a.default_tablespace,
       a.temporary_tablespace,
       a.profile,
       a.account_status,
       a.password_versions,
       granted_role,
       admin_option,
       default_role
  FROM sys.dba_users a, sys.dba_role_privs b
 WHERE a.username = b.grantee(+)
 ORDER BY username,
          default_tablespace,
          temporary_tablespace,
          profile,
          granted_role;
set termout on flush on feedback on verify on
clear columns
clear breaks

参考：KROWN36029