Script User Unlimited Tablespace Privilège et Quota.

REPHEADER PAGE CENTER 'USER INFORMATION QUOTA et UNLIMITED TABLESPACE PRIVILEGE'
SET LINESIZE 100
SET PAGESIZE 900
COL USERNAME FORMAT A10
COL TABLESPACE_NAME FORMAT A16
COL "INFO QUOTA" FORMAT A57
  SELECT USERNAME,
         TABLESPACE_NAME,
         DECODE (SIGN (MAX_BYTES), -1, 'Unlimited Tbs sur '||TABLESPACE_NAME, 
         'Quota de ' ||(ROUND((MAX_BYTES/1024/1024),2))||' Mo') "INFO QUOTA",
         ROUND((BYTES/1024/1024),2) "IN USE EN Mo"
    FROM DBA_TS_QUOTAS
   WHERE USERNAME  NOT IN(SELECT grantee
                           FROM DBA_SYS_PRIVS
                          WHERE privilege ='UNLIMITED TABLESPACE')
UNION 
  SELECT USERNAME,
         TABLESPACE_NAME, 
         DECODE (SIGN (MAX_BYTES), -1, 'Unlimited Tbs sur '||TABLESPACE_NAME|| 
         ' Obsolete car Unlimited Tbs Priv', 'Quota de ' ||
          (ROUND((MAX_BYTES/1024/1024),2))||' Mo Obsolete car UNLIMITED TBS PRIV') "INFO QUOTA",
         ROUND((BYTES/1024/1024),2) "IN USE EN Mo"
    FROM DBA_TS_QUOTAS
   WHERE USERNAME  IN(SELECT grantee
                           FROM DBA_SYS_PRIVS
                          WHERE privilege ='UNLIMITED TABLESPACE')
UNION
 SELECT grantee,
        '*',
        'Unlimited Tbs Privilege',
        NULL
   FROM DBA_SYS_PRIVS
  WHERE privilege ='UNLIMITED TABLESPACE'
ORDER BY 4 ;

                      USER INFORMATION QUOTA et UNLIMITED TABLESPACE PRIVILEGE

USERNAME   TABLESPACE_NAME  INFO QUOTA                                                IN USE EN Mo
---------- ---------------- --------------------------------------------------------- ------------
MGMT_VIEW  SYSTEM           Unlimited Tbs sur SYSTEM                                             0
RMAN       DATA             Quota de 10 Mo Obsolete car UNLIMITED TBS PRIV                       0
SCOTT      DATA             Unlimited Tbs sur DATA Obsolete car Unlimited Tbs Priv             ,13
DMSYS      SYSAUX           Quota de 200 Mo                                                    ,25
RMAN       RMAN_CATALOG     Unlimited Tbs sur RMAN_CATALOG Obsolete car Unlimited Tbs         8,25
                             Priv

OLAPSYS    SYSAUX           Unlimited Tbs sur SYSAUX Obsolete car Unlimited Tbs Priv         15,56
SYSMAN     SYSAUX           Unlimited Tbs sur SYSAUX Obsolete car Unlimited Tbs Priv         48,56
CTXSYS     *                Unlimited Tbs Privilege
DBSNMP     *                Unlimited Tbs Privilege
EXFSYS     *                Unlimited Tbs Privilege
MDDATA     *                Unlimited Tbs Privilege
MDSYS      *                Unlimited Tbs Privilege
OLAPSYS    *                Unlimited Tbs Privilege
ORDPLUGINS *                Unlimited Tbs Privilege
ORDSYS     *                Unlimited Tbs Privilege
OUTLN      *                Unlimited Tbs Privilege
RMAN       *                Unlimited Tbs Privilege
SCOTT      *                Unlimited Tbs Privilege
SYS        *                Unlimited Tbs Privilege
SYSADM     *                Unlimited Tbs Privilege
SYSMAN     *                Unlimited Tbs Privilege
SYSTEM     *                Unlimited Tbs Privilege
TSMSYS     *                Unlimited Tbs Privilege
WMSYS      *                Unlimited Tbs Privilege
XDB        *                Unlimited Tbs Privilege

26 ligne(s) sélectionnée(s).

SQL>