List Users Created on the Database

--
-- List Database Users
--
 
SET TERMOUT OFF
STORE SET save_env.sql REPLACE
SET TERMOUT ON
 
CLEAR BREAKS
CLEAR COLUMNS
CLEAR COMPUTES
 
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;
 
SELECT
   LPAD( DECODE( p.granted_role, 'DBA' , '*' ), 3 ) grole,
   u.username, u.default_tablespace, u.temporary_tablespace
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

Related Scripts

Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License