Recopilación scripts y consultas útiles de Oracle

¿Quién no tiene su chuleta de consultillas útiles que se suelen utilizar en el día a día, y en nuestras aventuras y desventuras con la base de datos?

Listado de consultas SQL de Oracle, la mayoría sobre las vistas del diccionario de Oracle, para obtener información sobre objetos de la base de datos propios o a los que tiene permisos de acceso si se trata de un usuario normal, y sobre objetos de cualquier usuario si el usuario es DBA.

La idea es crear un pequeño un repositorio para poder consultar desde cualquier lugar para facilitarnos la vida, o sacarnos de algún que otro apuro.

Consultas para usuarios de Oracle

Consultas que se pueden hacer a nivel de usuario sin privilegios de administrador, suelen ir también sobre el diccionario de datos de Oracle, pero retornan datos de los objetos a los que el usuario tiene acceso

-- Consulta Oracle SQL sobre el Diccionario de datos 
-- Con esta vista se consultan los nombres de las tablas o vistas del diccionario a las que el usuario tiene acceso, útil para saber dónde buscar información sobre objetos a los que el usuario tiene acceso

select * from dictionary where table_name like '%TABLES%'

-- Consulta Oracle SQL que muestra los metadatos de la/s tabla/s especificada/s por nombre
-- (en este caso todas las tablas que lleven la cadena "XXX")

select * from ALL_ALL_TABLES where upper(table_name) like '%XXX%'

-- Consulta Oracle SQL que muestra las descripciones de los campos de una tabla especificada 
   (en este caso todas las tablas que lleven la cadena "XXX")

select * from ALL_COL_COMMENTS where upper(table_name) like '%XXX%'

-- Consulta Oracle SQL para conocer las tablas propiedad del usuario actual

select * from user_tables

-- Consulta Oracle SQL para conocer todos los objetos propiedad del usuario conectado a Oracle

select * from user_catalog

-- Consulta Oracle SQL para conocer los productos Oracle instalados y la versión

select * from product_component_version

-- Consulta Oracle SQL para conocer los roles y privilegios por roles

select * from role_sys_privs

-- Consulta Oracle SQL para conocer las reglas de integridad y columna a la que afectan

select constraint_name, column_name from sys.all_cons_columns

-- Consulta Oracle SQL para conocer las tablas de las que es propietario un usuario, en este caso "xxx"

SELECT table_owner, table_name from sys.all_synonyms where table_owner like '%xxx%'

-- Consulta Oracle SQL como la anterior, pero de otra forma más efectiva 
   (tablas de las que es propietario un usuario)

SELECT DISTINCT TABLE_NAME
FROM ALL_ALL_TABLES
WHERE OWNER LIKE '%HR%'

-- Consulta Oracle SQL para conocer el tamaño ocupado por una tabla concreta sin incluir los índices de la misma

select sum(bytes)/1024/1024 MB from user_segments
where segment_type='TABLE' and segment_name='NOMBRETABLA'

-- Consulta Oracle SQL para conocer el tamaño ocupado por una tabla concreta incluyendo los índices de la misma

select sum(bytes)/1024/1024 Table_Allocation_MB from user_segments
where segment_type in ('TABLE','INDEX') and
(segment_name='NOMBRETABLA' or segment_name in
(select index_name from user_indexes where table_name='NOMBRETABLA'))

-- Consulta Oracle SQL para conocer el tamaño ocupado por una columna de una tabla

select sum(vsize('NOMBRECOLUMNA'))/1024/1024 MB from NOMBRETABLA-- Consulta Oracle SQL para obtener todas las funciones de Oracle: NVL, ABS, LTRIM, ...

SELECT distinct object_name
FROM all_arguments
WHERE package_name = 'STANDARD'
order by object_name

Consultas para DBAs de Oracle

Consultas sobre vistas de sistema, normalmente requieren permisos de DBA o administrador

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

select * from v$instance

-- 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

-- 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

select * from v$system_parameter

-- Consulta Oracle SQL para conocer la Versión de Oracle

select value from v$system_parameter where name = 'compatible'

-- Consulta Oracle SQL para conocer la Ubicación y nombre del fichero spfile

select value from v$system_parameter where name = 'spfile'

-- Consulta Oracle SQL para conocer la Ubicación y número de ficheros de control

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

-- Consulta Oracle SQL para conocer el Nombre de la base de datos

select value from v$system_parameter where name = 'db_name'

-- Consulta Oracle SQL sobre la vista que muestra las conexiones actuales a Oracle 
-- Para visualizarla es necesario entrar con privilegios de administrador

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

-- Consulta Oracle SQL 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

-- Consulta Oracle SQL 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
-- Consulta Oracle SQL que muestra propietarios de objetos y número de objetos por propietario

select owner, count(owner) Numero
from dba_objects
group by owner

-- Consulta Oracle SQL para el DBA de Oracle que muestra los tablespaces, 
-- el espacio utilizado, el espacio libre y los ficheros de datos de los mismos

Select t.tablespace_name "Tablespace", t.status "Estado",
    ROUND(MAX(d.bytes)/1024/1024,2) "MB Tamaño",
    ROUND((MAX(d.bytes)/1024/1024) -
    (SUM(decode(f.bytes, NULL,0, f.bytes))/1024/1024),2) "MB Usados",
    ROUND(SUM(decode(f.bytes, NULL,0, f.bytes))/1024/1024,2) "MB Libres",
    t.pct_increase "% incremento",
    SUBSTR(d.file_name,1,80) "Fichero de datos"
FROM DBA_FREE_SPACE f, DBA_DATA_FILES d, DBA_TABLESPACES t
WHERE t.tablespace_name = d.tablespace_name AND
f.tablespace_name(+) = d.tablespace_name
AND f.file_id(+) = d.file_id GROUP BY t.tablespace_name,
d.file_name, t.pct_increase, t.status ORDER BY 1,3 DESC

-- Consulta Oracle SQL que muestra los usuarios de Oracle y datos suyos (fecha de creación, estado, id, nombre, tablespace temporal,...)
Select * FROM dba_users

-- Consulta Oracle SQL para conocer tablespaces y propietarios de los mismos

select owner, decode(partition_name, null, segment_name,
segment_name || ':' || partition_name) name,
segment_type, tablespace_name,bytes,initial_extent,
next_extent, PCT_INCREASE, extents, max_extents
from dba_segments
Where 1=1 And extents > 1 order by 9 desc, 3

-- Últimas consultas SQL ejecutadas en Oracle y usuario que las ejecutó

select distinct vs.sql_text, vs.sharable_mem,
vs.persistent_mem, vs.runtime_mem, vs.sorts,
vs.executions, vs.parse_calls, vs.module,
vs.buffer_gets, vs.disk_reads, vs.version_count,
vs.users_opening, vs.loads,
to_char(to_date(vs.first_load_time,
'YYYY-MM-DD/HH24:MI:SS'),'MM/DD HH24:MI:SS') first_load_time,
rawtohex(vs.address) address, vs.hash_value hash_value ,
rows_processed , vs.command_type, vs.parsing_user_id ,
OPTIMIZER_MODE , au.USERNAME parseuser
from v$sqlarea vs , all_users au
where (parsing_user_id != 0) AND
(au.user_id(+)=vs.parsing_user_id)
and (executions >= 1) order by buffer_gets/executions desc

-- Consulta Oracle SQL para conocer todos los tablespaces

select * from V$TABLESPACE

-- Consulta Oracle SQL para conocer la memoria Share_Pool libre y usada

select name,to_number(value) bytes
from v$parameter where name ='shared_pool_size'
union all
select name,bytes
from v$sgastat where pool = 'shared pool' and name = 'free memory'

-- Cursores abiertos por usuario

select b.sid, a.username, b.value Cursores_Abiertos
from v$session a,
v$sesstat b,
v$statname c
where c.name in ('opened cursors current')
and b.statistic# = c.statistic#
and a.sid = b.sid
and a.username is not null
and b.value >0
order by 3

-- Consulta Oracle SQL para conocer los aciertos de la caché (no debería superar el 1 por ciento)

select sum(pins) Ejecuciones, sum(reloads) Fallos_cache,
trunc(sum(reloads)/sum(pins)*100,2) Porcentaje_aciertos
from v$librarycache
where namespace in ('TABLE/PROCEDURE','SQL AREA','BODY','TRIGGER');

-- Sentencias SQL completas ejecutadas con un texto determinado en el SQL

SELECT c.sid, d.piece, c.serial#, c.username, d.sql_text
FROM v$session c, v$sqltext d
WHERE c.sql_hash_value = d.hash_value
and upper(d.sql_text) like '%WHERE CAMPO LIKE%'
ORDER BY c.sid, d.piece

-- Una sentencia SQL concreta (filtrado por sid)

SELECT c.sid, d.piece, c.serial#, c.username, d.sql_text
FROM v$session c, v$sqltext d
WHERE c.sql_hash_value = d.hash_value
and sid = 105
ORDER BY c.sid, d.piece

-- Consulta Oracle SQL para conocer el tamaño ocupado por la base de datos

select sum(BYTES)/1024/1024 MB from DBA_EXTENTS

-- Consulta Oracle SQL para conocer el tamaño de los ficheros de datos de la base de datos

select sum(bytes)/1024/1024 MB from dba_data_files

-- Consulta Oracle SQL para conocer el espacio ocupado por usuario

SELECT owner, SUM(BYTES)/1024/1024 MB FROM DBA_EXTENTS
group by owner

-- Consulta Oracle SQL para conocer el espacio ocupado por todos los objetos de la base de datos, muestra los objetos que más ocupan primero

SELECT SEGMENT_NAME, SUM(BYTES)/1024/1024 MB FROM DBA_EXTENTS
group by SEGMENT_NAME
order by 2 desc

-- Consulta Oracle SQL para conocer el espacio ocupado por los diferentes segmentos 
   (tablas, índices, undo, rollback, cluster, ...)

SELECT SEGMENT_TYPE, SUM(BYTES)/1024/1024 MB FROM DBA_EXTENTS
group by SEGMENT_TYPE

Libros de Administración Oracle (DBA) y PL/SQL

¿Quieres profundizar más en PL/SQL de Oracle o en administración de bases de datos Oracle? Puedes hacerlo consultando alguno de estos libros de Oracle.