Admitámoslo, administrar y velar por el correcto funcionamiento de una base de datos no es tarea de un día, y menos con el vasto repertorio de consultas y metodologías que podemos aplicar para ello.
A la hora de ensuciarse, puedes picar en la mina con un pico y una pala o puedes hacerlo con maquinaria pesada, de ti depende cuanto tiempo quieras invertir en ello o cuanto te quieras complicar la vida.
Por ello, impresiona a tu jefe con estas consultas de gestión de Base de Datos de Oracle y ahorra esfuerzo y tiempo. Estas queries convertirán tu pico en un taladro neumático de última generación y dejarán a tu superior pensando que eres el próximo Messi del SQL.
Pero, antes de empezar, asegúrate de tener privilegios de administrador en tu base de datos de confianza, ya que varias de estas consultas te darán un bonito y frustrante mensaje de error si no tienes dichos privilegios.
Como habrás imaginado, la mayoría de estas consultas no están sacadas de la chistera de la magia de mi intelecto, sino que son la recopilación de una amplia búsqueda en ese océano lleno de islas que llamamos internet. Si quieres, puedes calmar tus ansias de más conocimiento consultando webs como cibermanuales.com (Te dejo el link por aquí: Consultas sql de Oracle prácticas para administradores y usuarios avanzados (cibermanuales.com)) o deckerix.com (Consultas SQL útiles que todo DBA de Oracle debería conocer – El mundo de deckerix).
Ya ves, no voy a descubrirte nada que no hayan descubierto ya estos señores previamente, pero si tratar de ponerte un poco en la situación en la que podrías necesitar dichas consultas.
Otra cosa a añadir es que la mayoría de estas consultas, como, por ejemplo, ver las sesiones abiertas, se pueden hacer a golpe de click a través de la UI de Oracle, lo cual realmente es más “user friendly”. La finalidad de hacerlo a través de las consultas que vamos a exponer es, entre otras cosas, indagar un poco en las tablas de sistema que componen nuestra base de datos.
¡Empezamos!
Un día te despiertas tranquilamente, abres tu ordenador dispuesto a empezar una nueva y apasionante jornada como DBA y de repente sufres una amnesia repentina. Respira, no pasa nada, con:
select * from v$instance;
Podrás tener un acceso detallado a todas las instancias que se encuentran en tu base de datos; el nombre de la instancia, el nombre del host, la versión de Oracle, si está activa o no, etc.
Si tu amnesia es selectiva y no quieres el resultado de toda la consulta, puedes probar filtrando por el nombre de la instancia, el nombre del host y el status, por ejemplo:
select instance_name, host_name, database_status from v$instance;
Si esto no refresca tu memoria sobre la instancia con la que estás trabajando, ninguna otra query lo hará, así que yo que tu recurriría a ayuda profesional…
Otra cosa importante a saber son los parámetros del sistema, los cuales te ayudarán a identificar cómo y cuáles son estos parámetros, cómo estos configuran los datos de tu base, localizar los archivos de sistema y un largo etcétera que te dejo descubrir en el siguiente enlace:
Algunas de las queries para acceder a dichos parámetros son la siguientes:
Para ver los parámetros como instancias y aquellos que inhieren con las sesiones además de mostrarte información sobre los parámetros de inicio que están actualmente interactuando con la sesión.
select * from v$system_parameter;
Esta otra query te mostrará la información sobre el contenido de los archivos de parámetros del servidor. Por ejemplo, si un archivo de parámetro del servidor no es usado para iniciar la instancia, encontraremos un valor FALSE en la columna ISSPECIFIED.
select * from v$spparameter;
¿Cómo se aplican estas queries en la práctica? Excelente pregunta querido lector…
Supongamos por ejemplo que tu anterior amnesia te hace olvidar que versión de Oracle estás usando. Con:
select value from v$system_parameter where name = 'compatible';
Obtendrás en menos de lo que canta un gallo macaronésico la versión de tu amado sistema de gestión de base de datos.
¿Qué quieres saber dónde están tus ficheros de control? Pues:
select value from v$system_parameter where name = 'control_files';
Y a correr.
Vámonos a las sesiones ahora. Con la tabla de sistema V$session podrás saber cosas tan útiles como cuántas sesiones hay actualmente corriendo en la base de datos, qué consulta está ejecutando cada sesión o cuánto tarda dicha consulta en ejecutarse. En otras palabras, si estás buscando información sobre el rendimiento de tu base de datos la tabla de sesiones va a ser tu Pepito el Grillo particular.
Por ejemplo, si quieres saber quién está allanando la morada que es tu base de datos, con:
select osuser, username, machine, program from v$session order by osuser;
Podrás ver el nombre, el nombre de usuario, el nombre de la máquina que está usando y el programa que está corriendo.
Otra manera para saber los usuarios conectados y cuantas sesiones tienen abiertas es:
select username Usuario_Oracle, count(username) Numero_Sesiones
from v$session
group by username
order by Numero_Sesiones desc;
En Oracle podemos encontrar un diccionario de datos, el cual es el catálogo de objetos de nuestra base de datos. En dicho diccionario, podemos encontrar tablas, usuarios, roles, vistas, las columnas de las tablas, funciones y un etcétera más largo que un día sin pan. Llamarlo es muy sencillo, simplemente has de picar la siguiente query y disfrutar de la vasta inmensidad de objetos que componen nuestra base de datos:
select * from dictionary;
Vámonos ahora con algunas consultas útiles sobre el manejo de las propias tablas de datos de nuestra base.
Imaginemos, por ejemplo, lo contrario a la amnesia. Tienes tanta información en el cerebro que catalogarla y recurrir a ella te lleva varias horas de procesado mental. Sabes que la tabla tenía algo en el nombre como “FACTURAS”, pero no sabes si lo que buscas está en las entrantes, en las salientes, en las no cobradas, etc. Tienes dos opciones, o navegas pacientemente por el navegador de tablas hasta dar con la tuya o escribes la siguiente consulta:
select * from ALL_ALL_TABLES where upper(table_name) like '%FACTURAS%';
Esa consulta te dará todas las tablas que incluyan en su nombre la cadena ‘FACTURAS’. Y donde digo ‘FACTURAS’ digo cualquier cadena que se te venga a la cabeza. ¿Qué llevas la base de datos de una consulta veterinaria y quieres saber cuántas tablas están relacionadas con los hurones? Prueba a sustituir ‘FACTURAS’ por ‘HURONES’ 😉.
Pero la cosa no se queda ahí, imaginemos que, siendo un DBA tan aplicado y eficiente como eres, te has preocupado de describir los campos que componen una tabla para la mejor comprensión de los datos que la componen. Ahora ha llegado el momento de saber cuánta información sobre ‘HURONES’ tienen las tablas en general, y no solo las que se titulan como tal. Usa:
select * from ALL_COL_COMMENTS where upper(table_name) like '%HURONES%';
Y sorprende a tu veterinario de una vez por todas. Además, al igual que con la consulta de las tablas, puedes cambiar ‘HURONES’ por ‘SIDECARS’ si en lugar de trabajar para una clínica veterinaria trabajas para una fábrica de motos… ¡Las posibilidades son ilimitadas!
Por cierto, puedes también consultar cuanto te ocupa esa maldita tabla de sidecars en tu base de datos a través de la consulta:
select sum(bytes)/1024/1024 MB from user_segments
where segment_type='TABLE' and segment_name='SIDECARS';
Y si quieres incluir los índices:
select sum(bytes)/1024/1024 Table_Allocation_MB from user_segments
where segment_type in ('TABLE','INDEX') and
(segment_name='SIDECARS' or segment_name in
(select index_name from user_indexes where table_name='SIDECARS'))
Por último, con tanta información en el cerebro como tenemos los DBAs, es muy fácil olvidarse de alguna sentencia muy útil que hayamos ejecutado recientemente. O si tal vez, como yo, le has dejado tu ordenador a algún crack de SQL para que se luzca un poco, con esta super consulta podrás ver absolutamente todas las consultas hechas en tu base de datos en un tiempo determinado:
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;
Aunque para una búsqueda un poco más centrada en las queries que has hecho tú, yo recomendaría personalmente esta:
select v.SQL_TEXT,
v.PARSING_SCHEMA_NAME,
v.FIRST_LOAD_TIME,
v.DISK_READS,
v.ROWS_PROCESSED,
v.ELAPSED_TIME,
v.service
from v$sql v
where to_date(v.FIRST_LOAD_TIME,'YYYY-MM-DD hh24:mi:ss')>ADD_MONTHS(trunc(sysdate,'MM'),-2);.
Donde el “-2” es el número de meses de antigüedad en adelante que quieres que te muestre de las queries (suerte si consigues que sean más de dos).
Y con esto, para que asimiles bien la información y no liarte más, damos por finalizada esta pequeña compilación de consultas útiles para cualquier DBA de Oracle. Estate atento al blog, pues he dejado a propósito muchísimas más en el tintero para una más que posible segunda parte.
Muchísimas gracias por tu atención y ¡hasta la próxima! 😊
* Tu información será utilizada exclusivamente para contactarte en relación al Business Data Master. No hacemos spam ni compartimos datos con terceros.