set term ON
set linesize 1000
set pagesize 3500
set verify     off
set feedback   off

alter session set nls_numeric_characters = ',.';

CLEAR BUFFER
CLEAR SQL

SPOOL &&ruta_fichero

set term OFF

@@header_dbinfo 107 DC

PROMPT
PROMPT
PROMPT Lista de usuarios con objetos de tipo tabla o indice
PROMPT -----------------------------------------------------

CLEAR COLUMNS
COLUMN USER_ID                     FORMAT 999   HEADING "USERID"
COLUMN USERNAME                    FORMAT A15   HEADING "USERNAME"
COLUMN NUM_OBJ                     FORMAT 99999 HEADING "NUM_OBJ"
COLUMN ACCOUNT_STATUS              FORMAT A9    HEADING "STATUS"
COLUMN DEFAULT_TABLESPACE          FORMAT A20   HEADING "DEF. TABLESPACE"
COLUMN TEMPORARY_TABLESPACE        FORMAT A20   HEADING "TMP. TABLESPACE"
COLUMN PROFILE                     FORMAT A10   HEADING "PROFILE"
COLUMN INITIAL_RSRC_CONSUMER_GROUP FORMAT A30   HEADING "CONSUMER GROUP"


SELECT USER_ID, USERNAME, DOB.TOTAL NUM_OBJ, ACCOUNT_STATUS, DEFAULT_TABLESPACE, TEMPORARY_TABLESPACE, PROFILE, INITIAL_RSRC_CONSUMER_GROUP
FROM DBA_USERS DU, (SELECT OWNER, COUNT(*) TOTAL FROM DBA_OBJECTS WHERE OBJECT_TYPE IN ('TABLE','INDEX') GROUP BY OWNER) DOB
WHERE DU.USERNAME NOT IN 
('ANONYMOUS', 'CTXSYS', 'DBSNMP', 'DIP', 'DMSYS', 'EXFSYS', 'HR', 'LBACSYS', 'MDDATA', 'MDSYS', 'MGMT_VIEW', 'ODM', 
'ODM_MTR', 'OE', 'OLAPSYS', 'ORDPLUGINS', 'ORDSYS', 'OUTLN', 'PM', 'QS', 'QS_ADM', 'QS_CB', 'QS_CBADM', 'QS_CS', 
'QS_ES', 'QS_OS', 'QS_WS', 'RMAN', 'SCOTT', 'SH', 'SI_INFORMTN_SCHEMA', 'SYS', 'SYSMAN', 'SYSTEM', 'TSMSYS', 
'WK_TEST', 'WKPROXY', 'WKSYS', 'WMSYS', 'XDB','ASTDBA10G')
AND EXISTS(SELECT 1 FROM DBA_OBJECTS DO WHERE DO.OWNER = DU.USERNAME AND OBJECT_TYPE IN ('TABLE','INDEX'))
AND DU.USERNAME = DOB.OWNER
ORDER BY DU.USERNAME;

------------------------------------------------------------------------------------------

PROMPT
PROMPT
PROMPT Lista de usuarios sin objetos de tipo tabla o indice
PROMPT -----------------------------------------------------

CLEAR COLUMNS
COLUMN USER_ID                     FORMAT 999 HEADING "USERID"
COLUMN USERNAME                    FORMAT A15 HEADING "USERNAME"
COLUMN ACCOUNT_STATUS              FORMAT A9  HEADING "STATUS"
COLUMN DEFAULT_TABLESPACE          FORMAT A20 HEADING "DEF. TABLESPACE"
COLUMN TEMPORARY_TABLESPACE        FORMAT A20 HEADING "TMP. TABLESPACE"
COLUMN PROFILE                     FORMAT A10 HEADING "PROFILE"
COLUMN INITIAL_RSRC_CONSUMER_GROUP FORMAT A30 HEADING "CONSUMER GROUP"


SELECT USER_ID, USERNAME, ACCOUNT_STATUS, DEFAULT_TABLESPACE, TEMPORARY_TABLESPACE, PROFILE, INITIAL_RSRC_CONSUMER_GROUP
FROM DBA_USERS DU
WHERE DU.USERNAME NOT IN 
('ANONYMOUS', 'CTXSYS', 'DBSNMP', 'DIP', 'DMSYS', 'EXFSYS', 'HR', 'LBACSYS', 'MDDATA', 'MDSYS', 'MGMT_VIEW', 'ODM', 
'ODM_MTR', 'OE', 'OLAPSYS', 'ORDPLUGINS', 'ORDSYS', 'OUTLN', 'PM', 'QS', 'QS_ADM', 'QS_CB', 'QS_CBADM', 'QS_CS', 
'QS_ES', 'QS_OS', 'QS_WS', 'RMAN', 'SCOTT', 'SH', 'SI_INFORMTN_SCHEMA', 'SYS', 'SYSMAN', 'SYSTEM', 'TSMSYS', 
'WK_TEST', 'WKPROXY', 'WKSYS', 'WMSYS', 'XDB','ASTDBA10G')
AND NOT EXISTS(SELECT 1 FROM DBA_OBJECTS DO WHERE DO.OWNER = DU.USERNAME AND OBJECT_TYPE IN ('TABLE','INDEX'))
ORDER BY DU.USERNAME;

------------------------------------------------------------------------------------------

PROMPT
PROMPT
PROMPT Lista de roles concedidos a usuarios
PROMPT -------------------------------------

CLEAR COLUMNS
COLUMN GRANTEE      FORMAT A15 HEADING "USERNAME"
COLUMN GRANTED_ROLE FORMAT A30 HEADING "ROLE"
COLUMN ADMIN_OPTION FORMAT A7  HEADING "ADM_OPT"
COLUMN DEFAULT_ROLE FORMAT A15 HEADING "DEFAULT_ROLE"

SELECT GRANTEE, GRANTED_ROLE, ADMIN_OPTION, DEFAULT_ROLE
FROM DBA_ROLE_PRIVS
WHERE GRANTEE IN
(SELECT USERNAME
FROM DBA_USERS
WHERE USERNAME NOT IN 
('ANONYMOUS', 'CTXSYS', 'DBSNMP', 'DIP', 'DMSYS', 'EXFSYS', 'HR', 'LBACSYS', 'MDDATA', 'MDSYS', 'MGMT_VIEW', 'ODM', 
'ODM_MTR', 'OE', 'OLAPSYS', 'ORDPLUGINS', 'ORDSYS', 'OUTLN', 'PM', 'QS', 'QS_ADM', 'QS_CB', 'QS_CBADM', 'QS_CS', 
'QS_ES', 'QS_OS', 'QS_WS', 'RMAN', 'SCOTT', 'SH', 'SI_INFORMTN_SCHEMA', 'SYS', 'SYSMAN', 'SYSTEM', 'TSMSYS', 
'WK_TEST', 'WKPROXY', 'WKSYS', 'WMSYS', 'XDB','ASTDBA10G')
) 
ORDER BY GRANTEE, GRANTED_ROLE;

------------------------------------------------------------------------------------------

PROMPT
PROMPT
PROMPT Lista de privilegios de sistema concedidos a usuarios
PROMPT ------------------------------------------------------

CLEAR COLUMNS
COLUMN GRANTEE      FORMAT A15 HEADING "USERNAME"
COLUMN PRIVILEGE    FORMAT A40 HEADING "PRIVILEGE"
COLUMN ADMIN_OPTION FORMAT A7  HEADING "ADM_OPT"

SELECT GRANTEE, PRIVILEGE, ADMIN_OPTION
FROM DBA_SYS_PRIVS
WHERE GRANTEE IN
(SELECT USERNAME
FROM DBA_USERS
WHERE USERNAME NOT IN 
('ANONYMOUS', 'CTXSYS', 'DBSNMP', 'DIP', 'DMSYS', 'EXFSYS', 'HR', 'LBACSYS', 'MDDATA', 'MDSYS', 'MGMT_VIEW', 'ODM', 
'ODM_MTR', 'OE', 'OLAPSYS', 'ORDPLUGINS', 'ORDSYS', 'OUTLN', 'PM', 'QS', 'QS_ADM', 'QS_CB', 'QS_CBADM', 'QS_CS', 
'QS_ES', 'QS_OS', 'QS_WS', 'RMAN', 'SCOTT', 'SH', 'SI_INFORMTN_SCHEMA', 'SYS', 'SYSMAN', 'SYSTEM', 'TSMSYS', 
'WK_TEST', 'WKPROXY', 'WKSYS', 'WMSYS', 'XDB','ASTDBA10G')
)
ORDER BY GRANTEE, PRIVILEGE;

------------------------------------------------------------------------------------------

PROMPT
PROMPT
PROMPT Lista de privilegios de objeto concedidos a usuarios
PROMPT -----------------------------------------------------

CLEAR COLUMNS
COLUMN GRANTEE    FORMAT A15 HEADING "USERNAME"
COLUMN OWNER      FORMAT A15 HEADING "OWNER"
COLUMN TABLE_NAME FORMAT A30 HEADING "TABLE_NAME"
COLUMN GRANTOR    FORMAT A15 HEADING "GRANTOR"
COLUMN PRIVILEGE  FORMAT A40 HEADING "PRIVILEGE"
COLUMN GRANTABLE  FORMAT A7  HEADING "ADM_OPT"

SELECT GRANTEE, OWNER, TABLE_NAME, GRANTOR, GRANTABLE, PRIVILEGE
FROM DBA_TAB_PRIVS
WHERE GRANTEE IN
(SELECT USERNAME
FROM DBA_USERS
WHERE USERNAME NOT IN 
('ANONYMOUS', 'CTXSYS', 'DBSNMP', 'DIP', 'DMSYS', 'EXFSYS', 'HR', 'LBACSYS', 'MDDATA', 'MDSYS', 'MGMT_VIEW', 'ODM', 
'ODM_MTR', 'OE', 'OLAPSYS', 'ORDPLUGINS', 'ORDSYS', 'OUTLN', 'PM', 'QS', 'QS_ADM', 'QS_CB', 'QS_CBADM', 'QS_CS', 
'QS_ES', 'QS_OS', 'QS_WS', 'RMAN', 'SCOTT', 'SH', 'SI_INFORMTN_SCHEMA', 'SYS', 'SYSMAN', 'SYSTEM', 'TSMSYS', 
'WK_TEST', 'WKPROXY', 'WKSYS', 'WMSYS', 'XDB','ASTDBA10G')
)
ORDER BY GRANTEE, OWNER, TABLE_NAME, PRIVILEGE, GRANTABLE;

------------------------------------------------------------------------------------------

PROMPT
PROMPT
PROMPT Lista de privilegios de columna concedidos a usuarios
PROMPT ------------------------------------------------------

CLEAR COLUMNS
COLUMN GRANTEE     FORMAT A15 HEADING "USERNAME"
COLUMN OWNER       FORMAT A15 HEADING "OWNER"
COLUMN TABLE_NAME  FORMAT A30 HEADING "TABLE_NAME"
COLUMN COLUMN_NAME FORMAT A10 HEADING "COL_NAME"
COLUMN GRANTOR     FORMAT A15 HEADING "GRANTOR"
COLUMN PRIVILEGE   FORMAT A40 HEADING "PRIVILEGE"
COLUMN GRANTABLE   FORMAT A7  HEADING "ADM_OPT"

SELECT GRANTEE, OWNER, TABLE_NAME, COLUMN_NAME, GRANTOR, GRANTABLE, PRIVILEGE
FROM DBA_COL_PRIVS
WHERE GRANTEE IN
(SELECT USERNAME
FROM DBA_USERS
WHERE USERNAME NOT IN 
('ANONYMOUS', 'CTXSYS', 'DBSNMP', 'DIP', 'DMSYS', 'EXFSYS', 'HR', 'LBACSYS', 'MDDATA', 'MDSYS', 'MGMT_VIEW', 'ODM', 
'ODM_MTR', 'OE', 'OLAPSYS', 'ORDPLUGINS', 'ORDSYS', 'OUTLN', 'PM', 'QS', 'QS_ADM', 'QS_CB', 'QS_CBADM', 'QS_CS', 
'QS_ES', 'QS_OS', 'QS_WS', 'RMAN', 'SCOTT', 'SH', 'SI_INFORMTN_SCHEMA', 'SYS', 'SYSMAN', 'SYSTEM', 'TSMSYS', 
'WK_TEST', 'WKPROXY', 'WKSYS', 'WMSYS', 'XDB','ASTDBA10G')
)
UNION ALL
SELECT NULL GRANTEE, NULL OWNER, NULL TABLE_NAME, NULL COLUMN_NAME, NULL GRANTOR, NULL PRIVILEGE, NULL GRANTABLE
FROM DUAL
ORDER BY GRANTEE, OWNER,TABLE_NAME;

CLEAR COLUMNS
SPOOL OFF
UNDEFINE ruta_fichero