Algunas consultas SQL muy útiles para el administrador de Oracle (DBA)

Os mostramos algunas consultas SQL muy útiles para el administrador de Oracle Database: estado de la base de datos Oracle, parámetros generales, ficheros de control, conexiones actuales a Oracle, nombre del ejecutable que se utiliza, nombre del usuario, diccionario de datos (vistas y tablas), IP del servidor, tamaño de la base de datos, versión de Oracle, …

Definición SQL.
Consultas SQL útiles para obtener información sobre Oracle Database.

Definición SQL

SQL (Lenguaje de consulta estructurado) es un lenguaje declarativo de acceso a bases de datos relacionales que permite especificar diversos tipos de operaciones en éstas. Una de sus características es el manejo del álgebra y el cálculo relacional permitiendo efectuar consultas con el fin de recuperar, de una forma relativamente sencilla, información de interés de una base de datos, así como también hacer cambios sobre ella. Es un lenguaje de cuarta generación (4GL).

Consultas SQL útiles para obtener información sobre Oracle Database

Vista que muestra el estado de la base de datos:

select * from v$instance

1	select * from v$instance

Consulta que muestra si la base de datos está abierta:

select status from v$instance

1	select status from v$instance

Vista que muestra los parámetros generales de Oracle:

select * from v$system_parameter

1	select * from v$system_parameter

Versión de Oracle:

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

select value

from v$system_parameter

where name = 'compatible'

Ubicación y nombre del fichero spfile:

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

select value

from v$system_parameter

where name = 'spfile'

Ubicación y número de ficheros de control:

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

select value

from v$system_parameter

where name = 'control_files'

Nombre de la base de datos

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

select value

from v$system_parameter

where name = 'db_name'

Vista que muestra las conexiones actuales a Oracle:

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

select osuser, username, machine, program

from v$session

order by osuser

Vista 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

select program Aplicacion, count(program) Numero_Sesiones

from v$session

group by program

order by Numero_Sesiones desc

Vista 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

select username Usuario_Oracle, count(username) Numero_Sesiones

from v$session

group by username

order by Numero_Sesiones desc

Propietarios de objetos y número de objetos por propietario

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

select owner, count(owner) Numero

from dba_objects

group by owner

order by Numero desc

Diccionario de datos (incluye todas las vistas y tablas de la Base de Datos):

select * from dictionary
select table_name from dictionary

1 2	select * from dictionary select table_name from dictionary

Muestra los datos de una tabla especificada (en este caso todas las tablas que lleven la cadena «EMPLO»):

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

select *

from ALL_ALL_TABLES

where upper(table_name) like '%EMPLO%'

Muestra los disparadores (triggers) de la base de datos Oracle Database:

select *<br>from ALL_TRIGGERS

1	select *<br>from ALL_TRIGGERS

Tablas propiedad del usuario actual:

select * from user_tables

1	select * from user_tables

Todos los objetos propiedad del usuario conectado a Oracle:

select * from user_catalog

1	select * from user_catalog

Consulta 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

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

Productos Oracle instalados y la versión:

select * from product_component_version

1	select * from product_component_version

Roles y privilegios por roles:

select * from role_sys_privs

1	select * from role_sys_privs

Reglas de integridad y columna a la que afectan:

select constraint_name, column_name 
from sys.all_cons_columns

1 2	select constraint_name, column_name from sys.all_cons_columns

Tablas de las que es propietario un usuario, en este caso «HR»:

SELECT table_owner, table_name 
from sys.all_synonyms 
where table_owner like 'HR'

SELECT table_owner, table_name

from sys.all_synonyms

where table_owner like 'HR'

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'

SELECT DISTINCT TABLE_NAME

FROM ALL_ALL_TABLES

WHERE OWNER LIKE 'HR'

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

Usuarios de Oracle y todos sus datos (fecha de creación, estado, id, nombre, tablespace temporal,…):

Select  * FROM dba_users

1	Select * FROM dba_users

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

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

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

Todos los ficheros de datos y su ubicación:

select * from V$DATAFILE

1	select * from V$DATAFILE

Ficheros temporales:

select * from V$TEMPFILE

1	select * from V$TEMPFILE

Tablespaces:

select * from V$TABLESPACE

1	select * from V$TABLESPACE

Otras vistas muy interesantes:

select * from V$BACKUP
select * from V$ARCHIVE   
select * from V$LOG   
select * from V$LOGFILE    
select * from V$LOGHIST          
select * from V$ARCHIVED_LOG    
select * from V$DATABASE

select * from V$BACKUP

select * from V$ARCHIVE

select * from V$LOG

select * from V$LOGFILE

select * from V$LOGHIST

select * from V$ARCHIVED_LOG

select * from V$DATABASE

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'

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

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

Aciertos de la caché (no debe 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');

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

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

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

Tamaño ocupado por la base de datos

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

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

Tamaño de los ficheros de datos de la base de datos:

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

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

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'

select sum(bytes)/1024/1024 MB

from user_segments

where segment_type='TABLE' and segment_name='NOMBRETABLA'

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

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

Tamaño ocupado por una columna de una tabla:

select sum(vsize('NOMBRECOLUMNA'))/1024/1024 MB 
from NOMBRETABLA

1 2	select sum(vsize('NOMBRECOLUMNA'))/1024/1024 MB from NOMBRETABLA

Espacio ocupado por usuario:

SELECT owner, SUM(BYTES)/1024/1024 
FROM DBA_EXTENTS MB
GROUP BY owner

SELECT owner, SUM(BYTES)/1024/1024

FROM DBA_EXTENTS MB

GROUP BY owner

Espacio ocupado por los diferentes segmentos (tablas, índices, undo, rollback, cluster, …):

SELECT SEGMENT_TYPE, SUM(BYTES)/1024/1024 
FROM DBA_EXTENTS MB
GROUP BY SEGMENT_TYPE

SELECT SEGMENT_TYPE, SUM(BYTES)/1024/1024

FROM DBA_EXTENTS MB

GROUP BY SEGMENT_TYPE

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 
FROM DBA_EXTENTS MB
GROUP BY SEGMENT_NAME
ORDER BY 2 DESC

SELECT SEGMENT_NAME, SUM(BYTES)/1024/1024

FROM DBA_EXTENTS MB

GROUP BY SEGMENT_NAME

ORDER BY 2 DESC

Obtener todas las funciones de Oracle: NVL, ABS, LTRIM, …:

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

SELECT distinct object_name

FROM all_arguments

WHERE package_name = 'STANDARD'

order by object_name

Obtener los roles existentes en Oracle Database:

select * from DBA_ROLES

1	select * from DBA_ROLES

Obtener los privilegios otorgados a un rol de Oracle:

select privilege 
from dba_sys_privs 
where grantee = 'NOMBRE_ROL'

select privilege

from dba_sys_privs

where grantee = 'NOMBRE_ROL'

Obtener la IP del servidor de la base de datos Oracle Database:

select utl_inaddr.get_host_address IP
from dual

1 2	select utl_inaddr.get_host_address IP from dual

Mostrar datos de auditoría de la base de datos Oracle (inicio y desconexión de sesiones):

select username, action_name, priv_used, returncode
from dba_audit_trail

1 2	select username, action_name, priv_used, returncode from dba_audit_trail

Comprobar si la auditoría de la base de datos Oracle está activada:

select name, value
from v$parameter
where name like 'audit_trail'

select name, value

from v$parameter

where name like 'audit_trail'

Algunas consultas SQL muy útiles para el administrador de Oracle (DBA)

Definición SQL

Consultas SQL útiles para obtener información sobre Oracle Database

Sobre el autor

alonsojpd

Comentar Cancelar la respuesta

Sesión

Videotutoriales

Post foros

Respuestas foros

Descargas

Proyecto A Obtener Procesos WMI en CSharp de Visual Studio .NET

Ejercicios resueltos programación funcional y excepciones en Python

Ejercicios resueltos módulos, paquetes y Programación Orientada a Objetos en Python

Ejercicios resueltos áreas, máximo, mínimo, media, listas en Python

Código fuente Python y ejecutables Linux y Windows de script EstadoSitio.py

Wiki

Descarga de la IOS de Cisco para router C7200 c7200-advipservicesk9-mz.152-4.S5.bin

Descarga de la IOS de Cisco para router C3725 c3725-adventerprisek9-mz.124-25d.bin

Turbo Pascal 7.0

BDE Borland Database Engine 5.2

ProyectoA Envío email TLS SSL v.1.6.8.69 con código fuente completo en Delphi 6

Indy Internet Direct 10.0.52 source code para instalar en Delphi desde 4 a 8 .Net XE RAD Studio C++Builder

Driver/Controlador en formato ZIP con el XML y el VIB para VMware ESXi de NIC Realtek RTL8168

Proyecto y aplicación web en Django de Python de sitio web completo para eliminar modificar y crear contactos en BD MySQL MariaDB

AjpdSoft Generador y Lector códigos QR Código Fuente VB Net

Ejemplo contenido fichero my.ini de MySQL en instalación sobre Windows con AppServ en unidad D

Algunas consultas SQL muy útiles para el administrador de Oracle (DBA)

Definición SQL

Consultas SQL útiles para obtener información sobre Oracle Database

Sobre el autor

Entradas relacionadas

Comentar Cancelar la respuesta

Sesión

Videotutoriales

Post foros

Respuestas foros

Descargas

Wiki

Etiquetas