Oracle: Trucos para DBA I

PARÁMETROS Y ESTADO DE LA BASE DE DATOS

INFORMACIÓN INSTANCIA
Información del estado de una instancia de base de datos: estado, versión, nombre, cuando se levanto, el nombre de la máquina, …

SELECT * 
  FROM v$instance;

NOMBRE DE LA BASE DE DATOS
A veces no sabemos donde estamos conectados, una forma es localizar el nombre de la base de datos

SELECT value 
  FROM v$system_parameter 
 WHERE name = 'db_name';

PARÁMETROS DE LA BASE DE DATOS
Vista que muestra los parámetros generales de Oracle:

SELECT * 
  FROM v$system_parameter;

o también

SHOW PARAMETERS valor_a_buscar

PRODUCTOS ORACLE INSTALADOS Y LA VERSIÓN

SELECT * 
  FROM product_component_version;

OBTENER LA IP DEL SERVIDOR DE LA BASE DE DATOS ORACLE DATABASE

SELECT utl_inaddr.get_host_address IP
  FROM DUAL;

UBICACIÓN DE FICHEROS

LOCALIZAR UBICACIÓN Y NOMBRE DEL FICHERO SPFILE
Como el fichero de parámetros puede haberse cambiado de lugar, se puede localizar de la siguiente manera

SELECT value 
  FROM v$system_parameter 
 WHERE name = 'spfile';

LOCALIZAR UBICACIÓN Y NOMBRE DE LOS FICHEROS DE CONTROL
Como el fichero de parámetros puede haberse cambiado de lugar, se puede localizar de la siguiente manera

Ubicación y número de ficheros de control:
SELECT value 
  FROM v$system_parameter 
 WHERE name = 'control_files';

TODOS LOS FICHEROS DE DATOS Y SU UBICACIÓN

SELECT * 
  FROM V$DATAFILE;

FICHEROS TEMPORALES

SELECT *
  FROM V$TEMPFILE;

FICHEROS DE REDO LOG

SELECT member
  FROM v$logfile;

FICHEROS DE ARCHIVE LOG

SHOW parameters archive_dest

VOLUMETRÍA

ESPACIO UTILIZADO POR LOS TABLESPACES
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;

TAMAÑO OCUPADO POR LA BASE DE DATOS

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;

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

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

TAMAÑO OCUPADO POR UNA COLUMNA DE UNA TABLA

SELECT SUM(vsize('Nombre_Columna'))/1024/1024 MB 
  FROM Nombre_Tabla;

ESPACIO OCUPADO POR USUARIO

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;

OCUPACIÓN DE TODOS LOS OBJETOS DE LA BASE DE DATOS
Espacio ocupado por todos los objetos de la base de datos, muestra primero los objetos que más ocupan

SELECT SEGMENT_NAME,
       SUM(BYTES)/1024/1024 
  FROM DBA_EXTENTS MB
 GROUP BY SEGMENT_NAME
 ORDER BY 2 DESC;

OBJETOS DE LA BASE DE DATOS

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;

MUESTRA LOS DISPARADORES (TRIGGERS) DE LA BASE DE DATOS ORACLE DATABASE

SELECT *
  FROM ALL_TRIGGERS;

REGLAS DE INTEGRIDAD Y COLUMNA A LA QUE AFECTAN

SELECT constraint_name,
       column_name 
  FROM sys.all_cons_columns;

TABLAS DE LAS QUE ES PROPIETARIO UN USUARIO DETERMINADO

SELECT table_owner, table_name 
  FROM sys.all_synonyms 
 WHERE table_owner = 'SCOTT';

INFORMACIÓN TABLESPACES

SELECT * 
  FROM V$TABLESPACE;

BUSQUEDAS DE CONSTRAINTS DESHABILITADAS

SELECT TABLE_NAME, 
       CONSTRAINT_NAME,
       STATUS
  FROM ALL_CONSTRAINTS   
 WHERE OWNER  <> 'SIEBEL'
   AND STATUS = 'DISABLED';

TABLAS CON MÁS DE UN NÚMERO DETERMINADO DE ÍNDICES

SELECT TABLE_NAME,
       COUNT(*) 
  FROM ALL_INDEXES
 WHERE OWNER='SIEBEL'
 GROUP BY TABLE_NAME
HAVING COUNT(*) > 5
 ORDER BY 2 DESC;

TABLAS SIN PRÍMARY KEY

SELECT TABLE_NAME 
  FROM ALL_TABLES T
 WHERE OWNER = 'SIEBEL'
   AND NOT EXISTS (SELECT 1
                     FROM ALL_CONSTRAINTS C   
                    WHERE T.OWNER  = C.OWNER
                      AND CONSTRAINT_TYPE = 'P');

OBJETOS NO VÁLIDOS (PAQUETES, PROCEDIMIENTOS, FUNCIONES, TRIGGERS, VISTAS,…)

SELECT OBJECT_NAME, OBJECT_TYPE
  FROM all_objects
 WHERE OWNER = 'SIEBEL'
   AND STATUS <> 'VALID';

OBJETOS CREADOS EN LA ÚLTIMA HORA

SELECT OBJECT_NAME,
       OBJECT_TYPE, 
       LAST_DDL_TIME, 
       CREATED, 
       TIMESTAMP, 
       STATUS
  FROM all_objects
 WHERE OWNER = 'SIEBEL'
   AND CREATED > sysdate - 1/24;

OBJETOS MODIFICADOS EN LA ÚLTIMA HORA

SELECT OBJECT_NAME,
       OBJECT_TYPE,
       LAST_DDL_TIME,
       CREATED,
       TIMESTAMP,
       STATUS
  FROM all_objects
 WHERE OWNER = 'SIEBEL'
   AND TIMESTAMP > sysdate - 1;

INFORMACIÓN DE COLUMNAS DE UNA TABLA

SELECT TABLE_NAME,
       COLUMN_NAME,
       DATA_TYPE,
       DATA_LENGTH,
       DATA_PRECISION,
       NULLABLE
  FROM ALL_TAB_COLUMNS
 WHERE OWNER = 'SIEBEL'
   AND TABLE_NAME = 'MI_TABLA'
 ORDER BY TABLE_NAME, COLUMN_ID;

OTROS ENLACES DE INTERES

Oracle: Trucos para DBA II
Trucos PL SQL