Oracle: Trucos para DBA II

OPTIMIZACIÓN

IDENTIFICA TABLESPACES Y PROPIETARIOS DE LOS MISMOS. SI TIENE DEMASIADOS EXTENT, EL TAMAÑO DE LOS EXTENT PUEDE NO SER OPTIMO

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 extents > 1
 ORDER BY 9 DESC, 3;

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

AUDITORÍA

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;

SESIONES / USUARIOS

OBTENER LOS USUARIOS DE UNA BASE DE DATOS

SELECT USERNAME 
  FROM DBA_USERS;

CONTRASEÑAS POR DEFECTO DE ORACLE
SYS: CHANGE_ON_INSTALL
SYSTEM: MANAGER
SCOTT: TIGER
ADAMS: WOOD
JONES: STEEL
BLAKE: PAPER
FORD: CAR
KING: GOLD

OBTENER LOS ROLES EXISTENTES EN ORACLE DATABASE

SELECT * 
  FROM DBA_ROLES;

ROLES Y PRIVILEGIOS POR ROLES

SELECT * 
  FROM role_sys_privs;

INFORMACIÓN DE CONEXIÓN A LA BASE DE DATOS

SELECT SID,            -- Identificador de sesión
       osuser,         -- Usuario de sistema operativo
       username,       -- Usuario de base de datos
       machine,        -- Máquina desde la que se realiza la conexión
       program,        -- Programa, que realiza la conexión
       logon_time,     -- Hora de conexión
       Lockwait        -- Identifica si hay bloqueo
  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;

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;

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;

OBTENER LOS PRIVILEGIOS OTORGADOS A UN ROL DE ORACLE

SELECT privilege 
  FROM dba_sys_privs 
 WHERE grantee = 'NOMBRE_ROL';

INFORMACIÓN SENTENCIAS SQL

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

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.piece999;

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;

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

COMPROBAR SI LA AUDITORÍA DE LA BASE DE DATOS ORACLE ESTÁ ACTIVADA

SELECT name,
       value
  FROM v$parameter
 WHERE name LIKE 'audit_trail';

LENGUAJE

UTILIZAR «,» PARA MILLARES Y «.» PARA DECIMALES PARA REPRESENTACIÓN NÚMERICA DENTRO DE UN BLOQUE PL/SQL

BEGIN
    Dbms_session.set_nls ('NLS_NUMERIC_CHARACTERS','",."');
END;

OTROS ENLACES DE INTERES

Oracle: Trucos para DBA I
Trucos PL SQL