Información sobre usuarios:

Para ver el usuario conectado en esa sesión:

Show user

Para obtener información básica del usuario conectado consulta la vista USER_USERS. Con el siguiente query se obtiene el nombre del usuario y sus tablespaces por defecto.
Para obtener información de todos los usuarios de la base de datos, utiliza la vista DBA_USERS.

select username, account_status, created
from dba_users
 order by 1;

Además de la tabla dba_user existe la función sys_contrext que te permite obtener información similar:

select sys_context('USERENV', 'CURRENT_USER') USUSARIO
    , sys_context ('USERENV', 'AUTHENTICATION_METHOD') AUTH_MTH
    , sys_context ('USERENV','HOST') HOSTS
    , sys_context ('USERENV','INSTANCE_NAME') INST
  FROM DUAL

Para obtener los usuarios conectados a la base de datos ese momento:

select count(*), username from v$session group by username;

En caso de un cluster:

select count(*), username from gv$session group by username;

Obtener las select que se están ejecutando en ese momento en la base de datos:

select  a.sid, a.username, b.sql_text
    from v$session a
     , v$sqltext_with_newlines b
     where a.sql_id = b.sql_id
     order by a.username, a.sid, b.piece;

La misma select que la anterior pero para Oracle 9 o versiones inferiores:

select  a.sid, a.username, b.sql_text
 from v$session a
  , v$sqltext_with_newlines b
   where a.sql_address= b.address
And  a.sql_hash_value = b.hash_value
  order by a.username, a.sid, b.piece;

Cómo ver los roles de un usuario

Para ver los roles del usuario conectado en ese momento:

select username, granted_role
   from user_role_privs;

La siguiente select muestra los roles asignados a un usuario en concreto:

select grantee, granted_role from dba_role_privs
  where grantee = upper ('&grantee')
  order by grantee;

Para ver los roles asignado a un rol:

select role, granted_role
 from role_role_privs;

Para ver los roles definidos en la base de datos:

select role from dba_roles;

Arquitectura del diccionario de datos

Existen dos categorías generales dentro las vistas del diccionario:

• Estáticas:  vistas  USER/ALL/DBA .
• Dinámicas:  vistas V$ y GV$

 Vistas estáticas

 

Estas vistas se llaman estáticas por que su contenido solo cambia con ciertos eventos transaccionales, como la creación de una tabla o el conceder un privilegio.

Hay tres niveles de vistas estáticas:

 USER

ALL

DBA

Las vistas USER contienen información sobre el propio usuario. Por  ejemplo, la vista USER_TABLES almacena la información de las tablas del usuario. No se necesitan privilegios especiales para consultar esta tabla.

El siguiente nivel son las vistas estáticas ALL. Estas vistas contienen toda la información a las que el propio usuario tiene acceso. Por ejemplo, ALL_TABLES muestra todas las tablas de la base de datos sobre las que el usuario puede hacer select, las suyas y las de otros usuarios.

Las siguientes son las DBA vistas estáticas. Las vistas DBA contienen metadatos  describiendo todos los objetos de la base de datos (sin importar el propietario de los objetos o de los privilegios). Para acceder la las vistas DBA se tiene que tener el role DBA o el grant SELECT_CATALOG_ROLE.

Las vistas estáticas están basadas en las tablas internas de oracle como USER$, TAB$ y IND$.

Las tablas del diccionario de datos (como USER$, TAB$ y IND$) se crean durante la ejecución del comando CREATE DATABASE. Como parte de la creación de la base de datos se ejecuta el script sql.bsq, que se encarga de construir las tablas de diccionario de datos interno. El sql.bsq archivo se encuentra localizado en $ORACLE_HOME/rdbms/admin.

Las vistas estáticas se crean cuando se ejecuta el script catalog.sql (normalmente este script se ejecuta inmediatamente después de ejecutar satisfactoriamente el comando CREATE DATABASE. Este archivo esta localízalo en directorio en $ORACLE_HOME/rdbms/admin.

Este tipo de vistas se crean a partir de las tablas estáticas.

Vistas dinámicas

Estas visitas están constantemente actualizadas por Oracle y reflejan la situación actual de la instancia y de la base de datos. Estas visitas son esenciales para el diagnostico de problemas de rendimiento.

 Las vistas V$ y GV$ se basan indirectamente en las tablas X$, las cuales son estructuras internas de memoria que se instancias cuando se levanta la instancia de Oracle. Algunas de las vistas V$ están disponibles desde el primer momento que se levanta la instancia de oracle. Por ejemplo, la V$PARAMETER se rellena antes de que la instancia se monte. Otras vistas contienen información derivada de la vista del controlfile (cómo V$CONTROLFILE) y por tanto necesitan que la instancia este montada para tener información.

Estas vistas se crean con el $ORACLE_HOME/rdbms/admin/catalog.sql

Si se trabaja con Oracle Real Application Clusters existen las vistas globales GV$. Estas vistas dan información global de todas las instancias de un cluster, y contienen una columna INST_ID para determinar las instancias dentro de ambiente  cluster.