listu.sql

--
-- Title       :   listu.sql
-- Description :   To list all users currently registered on the
--                 database.
--
-- Copyright   :   ABCdba.com 2002
--
 
SET TERMOUT OFF
STORE SET save_env.sql REPLACE
SET TERMOUT ON
 
CLEAR BREAKS
CLEAR COLUMNS
CLEAR COMPUTES
 
SET PAGESIZE 20
SET PAUSE ON
SET PAUSE       ' Press <<RETURN>> for next page...'
 
COL username HEA "      User Name      " FOR a21
COL default_tablespace HEA " Default Tablespace " FOR a20;
COL temporary_tablespace HEA "Temporary Tablespace" FOR a20;
COL grole HEA " DBA " FOR a5;
COL glock HEA "Locked" FOR a6;
 
SELECT
   LPAD( DECODE( p.granted_role, 'DBA' , '*' ), 3 ) grole,
   u.username, u.default_tablespace, u.temporary_tablespace,
   CASE WHEN u.lock_date IS NULL THEN '     ' ELSE '  *  ' END glock
FROM
   dba_users u,
   (
   SELECT
      grantee, granted_role
   FROM
      dba_role_privs
   WHERE
      granted_role = 'DBA'
   ) p
WHERE
   u.username = p.grantee (+)
ORDER BY
   u.username
/
 
@save_env.sql
host rm save_env.sql
SET TERMOUT ON

© copyright 2001-2014 ABCdba.com | all rights reserved