oracle 11g使用deferred_segment_creation延迟段创建特性时遇到的问题总结(二)

/* Formatted on 6/8/2012 9:10:59 AM (QP5 v5.215.12089.38647) */
SELECT username, tablespace_name, privilege
FROM (SELECT grantee username, 'Any Tablespace' tablespace_name, privilege
FROM ( -- first get the users with direct grants
SELECT p1.grantee grantee, privilege
FROM dba_sys_privs p1
WHERE p1.privilege = 'UNLIMITED TABLESPACE'
UNION ALL
-- and then the ones with UNLIMITED TABLESPACE through a role...
SELECT r3.grantee, granted_role privilege
FROM dba_role_privs r3
START WITH r3.granted_role IN
(SELECT DISTINCT p4.grantee
FROM dba_role_privs r4, dba_sys_privs p4
WHERE r4.granted_role = p4.grantee
AND p4.privilege =
'UNLIMITED TABLESPACE')
CONNECT BY PRIOR grantee = granted_role)
-- we just whant to see the users not the roles
WHERE grantee IN (SELECT username FROM dba_users)
OR grantee = 'PUBLIC'
UNION ALL
-- list the user with unimited quota on a dedicated tablespace
SELECT username, tablespace_name, 'DBA_TS_QUOTA' privilege
FROM dba_ts_quotas
WHERE max_bytes = -1)
WHERE tablespace_name LIKE UPPER ('SYSTEM')
OR tablespace_name = 'Any Tablespace' AND username = 'TEST';