Oracle Database Users Report
Displays detailed information about database users, including account status, tablespaces, profile, creation date, authentication type, and other account attributes. Useful for user administration and security auditing.
oraclesqluser-role-managementv1.0.0
0 stars0 downloads21 views0 comments
By OracleDba • Created
Code
(32 lines)1234567891011121314151617181920212223242526272829303132
-- -----------------------------------------------------------------------------------
-- File Name : https://oracle-base.com/dba/monitoring/users.sql
-- Author : Tim Hall
-- Description : Displays information about all database users.
-- Requirements : Access to the dba_users view.
-- Call Syntax : @users [ username | % (for all)]
-- Last Modified: 21-FEB-2005
-- -----------------------------------------------------------------------------------
SET LINESIZE 200 VERIFY OFF
COLUMN username FORMAT A20
COLUMN account_status FORMAT A16
COLUMN default_tablespace FORMAT A15
COLUMN temporary_tablespace FORMAT A15
COLUMN profile FORMAT A15
SELECT username,
account_status,
TO_CHAR(lock_date, 'DD-MON-YYYY') AS lock_date,
TO_CHAR(expiry_date, 'DD-MON-YYYY') AS expiry_date,
default_tablespace,
temporary_tablespace,
TO_CHAR(created, 'DD-MON-YYYY') AS created,
profile,
initial_rsrc_consumer_group,
editions_enabled,
authentication_type
FROM dba_users
WHERE username LIKE UPPER('%&1%')
ORDER BY username;
SET VERIFY ON