@@header

/*
*
*  Author  : Vishal Gupta
*  Purpose : Display DB User Information
*  Parameters : 1 - DBUserName (Use % as wildcard, Default value '%')
*
*  Revision History:
*  ===================
*  Date       Author        Description
*  ---------  ------------  -----------------------------------------
*  24-May-16  Vishal Gupta  Added version specific output logic
*  05-Apr-13  Vishal Gupta  Created
*
*/


/************************************
*  INPUT PARAMETERS
************************************/
UNDEFINE USERNAME
DEFINE USERNAME="&&1"  

set term off
COLUMN  _USERNAME          NEW_VALUE  USERNAME             NOPRINT
SELECT DECODE('&&USERNAME','','%','&&USERNAME')   "_USERNAME"
FROM DUAL;
set term on


PROMPT *****************************************************************
PROMPT *  D A T A B A S E    U S E R    I N F O R M A T I O N
PROMPT * 
PROMPT * Input Parameters 
PROMPT *  - User Name = '&&USERNAME'
PROMPT *****************************************************************
PROMPT 
PROMPT 

set pages 0
SELECT /* First Column */
                                TRIM(SUBSTR('User Id                 : ' || u.user_id                                       ,1,70)) || chr(10)
                             || TRIM(SUBSTR('DB UserName             : ' || u.username                                      ,1,70)) || chr(10)
                             || TRIM(SUBSTR('External Name           : ' || u.external_name                                 ,1,70)) || chr(10)
                             || TRIM(SUBSTR('Created                 : ' || TO_CHAR(u.created,'DD-MON-YYYY HH24:MI:SS')     ,1,70)) || chr(10)
                             || TRIM(SUBSTR('Account Status          : ' || u.account_status                                ,1,70)) || chr(10)
                             || TRIM(SUBSTR('Lock Date               : ' || TO_CHAR(u.lock_date,'DD-MON-YYYY HH24:MI:SS')   ,1,70)) || chr(10)
                             || TRIM(SUBSTR('Password Expiry Date    : ' || TO_CHAR(u.expiry_date,'DD-MON-YYYY HH24:MI:SS') ,1,70)) || chr(10)
                             || TRIM(SUBSTR('Default Tablespace      : ' || u.default_tablespace                            ,1,70)) || chr(10)
                             || TRIM(SUBSTR('Temporary Tablespace    : ' || u.temporary_tablespace                          ,1,70)) || chr(10)
                             || TRIM(SUBSTR('Profile                 : ' || u.profile                                       ,1,70)) || chr(10)
&&_IF_ORA_11gR1_OR_HIGHER    || TRIM(SUBSTR('Password Versions       : ' || u.password_versions                             ,1,70)) || chr(10)
                             || TRIM(SUBSTR('Resource Consumer Group : ' || u.initial_rsrc_consumer_group                   ,1,70)) || chr(10)
&&_IF_ORA_11gR2_OR_HIGHER    || TRIM(SUBSTR('Authentication Type     : ' || u.authentication_type                           ,1,70)) || chr(10)
&&_IF_ORA_11gR1_OR_HIGHER    || TRIM(SUBSTR('Editions Enabled        : ' || u.editions_enabled                           ,1,70)) || chr(10)
                                column1
 FROM dba_users u
WHERE UPPER(u.username) = UPPER('&&USERNAME') 
;
set pages 150

PROMPT **********************
PROMPT *  TABLESPACE QUOTAS
PROMPT **********************


COLUMN username          HEADING "UserName"            FORMAT a25
COLUMN tablespace_name   HEADING "TablespaceName"      FORMAT a30
COLUMN bytes             HEADING "Used(MB)"            FORMAT 999,999,999
COLUMN max_bytes         HEADING "MaxQuota(MB)"        FORMAT a15
COLUMN dropped           HEADING "Dropped"             FORMAT a7

SELECT q.username
     , q.tablespace_name
     , ROUND((q.bytes)/power(1024,2))   bytes
     , DECODE(q.max_bytes,'-1','Unlimited',TO_CHAR(ROUND(q.max_bytes/power(1024,2)),'999,999,999')) max_bytes
     , q.dropped
  FROM dba_ts_quotas q
     , dba_users u
 WHERE u.username = q.username
   AND UPPER(u.username) = UPPER('&&USERNAME') 
;

@@footer