以下脚本可以用于收集数据库中用户的角色和表空间等信息(user_role_tbs.sql):
SET pagesize 50 linesize 115REMCOLUMN username format a10 heading UserCOLUMN default_tablespace format a12 heading DefaultCOLUMN temporary_tablespace format a12 heading TemporaryCOLUMN granted_role format a25 heading RolesCOLUMN default_role format a10 heading Default?COLUMN admin_option format a7 heading Admin?COLUMN profile format a12 heading ProfileREMBREAK on username skip 1 on account_status on default_tablespace on temporary_tablespace on profileREMSELECT username, default_tablespace, temporary_tablespace, profile, 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;REMSET termout on flush on feedback on verify onCLEAR columnsCLEAR breaks
Sample Output:
User Default Temporary Profile Roles Admin? Default?---------- ------------ ------------ ------------ ------------------------- ------- ----------SCOTT USERS TEMP DEFAULT CONNECT NO YES RESOURCE NO YESSYS SYSTEM TEMP DEFAULT AQ_ADMINISTRATOR_ROLE YES YES AQ_USER_ROLE YES YES CONNECT YES YES DBA YES YES DELETE_CATALOG_ROLE YES YES EXECUTE_CATALOG_ROLE YES YES EXP_FULL_DATABASE YES YES HS_ADMIN_ROLE YES YES IMP_FULL_DATABASE YES YES OEM_MONITOR YES YES RECOVERY_CATALOG_OWNER YES YES RESOURCE YES YES SELECT_CATALOG_ROLE YES YESSYSTEM SYSTEM TEMP DEFAULT AQ_ADMINISTRATOR_ROLE YES YES DBA YES YES