70 lines
2.5 KiB
SQL
70 lines
2.5 KiB
SQL
@@header
|
|
|
|
/*
|
|
*
|
|
* Author : Vishal Gupta
|
|
* Purpose : Display list of users
|
|
* Parameters : 1 - DBUserName (Use % as wildcard, Default value '%')
|
|
*
|
|
* Revision History:
|
|
* ===================
|
|
* Date Author Description
|
|
* --------- ------------ -----------------------------------------
|
|
* 19-May-16 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 S
|
|
PROMPT *
|
|
PROMPT * Input Parameters
|
|
PROMPT * - User Name = '&&USERNAME'
|
|
PROMPT *****************************************************************
|
|
PROMPT
|
|
PROMPT
|
|
|
|
COLUMN user_id HEADING "Id" FORMAT 99999999
|
|
COLUMN username HEADING "UserName" FORMAT a25
|
|
COLUMN external_name HEADING "Ext Name" FORMAT a15
|
|
COLUMN created HEADING "Created" FORMAT a15
|
|
COLUMN account_status HEADING "Status" FORMAT a20 TRUNC
|
|
COLUMN lock_date HEADING "LockDate" FORMAT a15
|
|
COLUMN expiry_date HEADING "ExpiryDate" FORMAT a15
|
|
COLUMN profile HEADING "Profile" FORMAT a15 TRUNC
|
|
COLUMN default_tablespace HEADING "Def TBS" FORMAT a25 TRUNC
|
|
COLUMN temporary_tablespace HEADING "Temp TBS" FORMAT a25 TRUNC
|
|
COLUMN initial_rsrc_consumer_group HEADING "Resource|Group" FORMAT a25 TRUNC
|
|
|
|
|
|
|
|
SELECT u.user_id
|
|
, u.username
|
|
, u.external_name
|
|
, TO_CHAR(u.created,'DD-MON-YY HH24:MI') created
|
|
, u.account_status
|
|
, TO_CHAR(u.lock_date,'DD-MON-YY HH24:MI') lock_date
|
|
, TO_CHAR(u.expiry_date,'DD-MON-YY HH24:MI') expiry_date
|
|
, u.profile
|
|
, u.default_tablespace
|
|
, u.temporary_tablespace
|
|
, u.initial_rsrc_consumer_group
|
|
FROM dba_users u
|
|
WHERE UPPER(u.username) LIKE UPPER('&&USERNAME')
|
|
ORDER BY u.username
|
|
;
|
|
|
|
|
|
@@footer |