¿Quién no tiene su chuleta de consultas útiles que se suelen utilizar en el día a día, y en nuestras aventuras y desventuras con un BBDD  Oracle?

En este pequeño  post vamos  a ver un resumen de consultas SQL de Oracle,  basadas  la mayoría sobre las vistas del diccionario de Oracle,  y animo a todo el que quiera añadir otras que considere de utilidad a responder el post publicando las suyas, a ver si entre todos creamos un repositorio que podamos consultar desde cualquier lugar para facilitarnos la vida, o sacarnos de algún que otro apuro.

Consulta Oracle SQL sobre la vista que muestra el estado de la base de datos

En primer lugar veamos  uan simple consulta muy general para    ver como esta la BBDD  en el momento de la consulta  .

La  consulta es bastante sencilla:

select * from v$instance

Con esta interesante consulta podemos  ver el numero de la instancia y su sombre , la version de BBDD, el tiempo desde el que esta arrancada la BBDD y es  estado en general  de la BBDD

Esa vista es tan interesante que de hecho se usa otra consulta Oracle SQL que muestra si la base de datos está abierta  :

select status from v$instance

Consulta Oracle SQL sobre la vista que muestra los parámetros generales de Oracle

Si queremos leer unos  258 parámetros generales de una BBDD  Oracle v 9   , en efecto  podemos recuperar  sus valores directamente con la siguiente consulta:

select * from v$system_parameter






De esta  consulta podemos obtener valores muy  interesantes ,como podemos ver en la pantalla anterior. De hecho basada en esa vista , podemos recuperar  algunos  de los mas importantes   simplemente filtrando  por el campo «name«.   A continuación   aquí algunas de las mas destacadas: 

• Si solo queremos  conocer la Versión de Oracle   lanzaremos esta consulta

select value from v$system_parameter where name = ‘compatible’

• Si solo queremos  conocer  la Ubicación y nombre del fichero spfile

select value from v$system_parameter where name = ‘spfile’

• Esta Consulta Oracle SQL es  para conocer la Ubicación y número de ficheros de control

select value from v$system_parameter where name = ‘control_files‘

• Y por ultimo una consulta Oracle SQL para conocer el Nombre de la base de datos

select value from v$system_parameter where name = ‘db_name’

Una vista muy parecida  a v$system  es la vista  v$parameter  ,la cual nos permite ver parámetros de Oracle, valor actual y su descripción

SELECT v.name, v.value value, decode(ISSYS_MODIFIABLE, 'DEFERRED',

'TRUE', 'FALSE') ISSYS_MODIFIABLE, decode(v.isDefault, 'TRUE', 'YES',

'FALSE', 'NO') "DEFAULT", DECODE(ISSES_MODIFIABLE, 'IMMEDIATE',

'YES','FALSE', 'NO', 'DEFERRED', 'NO', 'YES') SES_MODIFIABLE,

DECODE(ISSYS_MODIFIABLE, 'IMMEDIATE', 'YES', 'FALSE', 'NO',

'DEFERRED', 'YES','YES') SYS_MODIFIABLE , v.description

FROM V$PARAMETER v

WHERE name not like 'nls%' ORDER BY 1

Consulta Oracle SQL sobre la vista que muestra las conexiones actuales a Oracle

Ahora  vamos   a ver la vista v$session . La vista V$SESSION es la base de toda la información relacionada con el estado actual del sistema:

 Como casi todas las consultas que vamos a ver ,para visualizar  el resultado de esta consulta  es necesario entrar con privilegios de administrador

Veamos una consulta general para obtener los usuarios conectados a la BBDD;

select osuser, username, machine, program from v$session order by osuser




También sobre esa misma vista podemos lanzar una consulta  que muestra el número de conexiones actuales a Oracle    agrupado por aplicación que realiza la  conexión:

select program Aplicacion, count(program) Numero_Sesiones

from v$session

group by program

order by Numero_Sesiones desc

Otra consulta interesante  basada en la vista v$session ,  es la siguiente que  muestra los usuarios de Oracle conectados y el número de sesiones por usuario:

select username Usuario_Oracle, count(username) Numero_Sesiones

from v$session

group by username

order by Numero_Sesiones desc

Cada vez que se conecta a los datos se crea una sesión en la base de datos para realizar sus operaciones. Un DBA puede ver fácilmente esto consultando la vista de sistema V$SESSION.

Para ello  podemos lanzar  la siguiente consulta (en el caso del entorno RAC, DBA debe utilizar GV$SESSION en lugar de V$SESSION).

selectcount(*),type fromv$sessiongroup by type;

Puede utilizar la consulta siguiente para identificar qué usuario está creando un número alto de sesiones.

select SID,USERNAME,COMMAND,PROCESS,TERMINAL,PROGRAM from v$session where type=’USER’;

• SID es la ID de la sesión, USERNAME es el nombre del usuario de la base de datos.
• Process es el número de proceso. 
• Terminal es el nombre del sistema que esta ejecutando la consulta. 
• Program muestra el nombre del programa que esta usando la consulta.

Consultas  sobre el Diccionario de datos

Esta es una de las consultas mas potentes pues incluye todas las vistas y tablas de la Base de Datos. segun la version de la BBDD Oracle  puede llegar la consulta  a lanzar el resultados de unss 500 tablas.

Simplemente    para obtenerla podemos lanzar la siguiente consulta:

select * from dictionary

Este es resultado obtenido  lanzado  contra una BBDD Oracle 9:

Basándonos en el resultado  anterior  vamos a  ver algunas consultas sencillas ,por ejemplo:

• Podemos lanzar  una consulta  que muestra los datos de una tabla especificada.En este caso filtramos  por todas las tablas que lleven la cadena «XXX»:  select * from ALL_ALL_TABLES where upper(table_name) like ‘%XXX%’
• Consulta  que muestra las descripciones de los campos de una tabla especificada, peor en este caso con todas las tablas que lleven la cadena «XXX»: select * from ALL_COL_COMMENTS where upper(table_name) like ‘%XXX%’
• Consulta para conocer las tablas propiedad del usuario actual: select * from user_tables
• Consulta  para conocer todos los objetos propiedad del usuario conectado a Oracle  select * from user_catalog