Tablas de sistema en Oracle, cómo buscar objetos de la base de datos

En alguna ocasión hemos tenido que hacer una consulta en Oracle sobre una tabla cuyo nombre no recordamos con exactitud y nos hemos tenido que pegar con la documentación del proyecto (si existe, claro) hasta encontrarla. Con el IDE TOAD for Oracle podemos usar el Schema Browser para encontrar rápidamente a través de los filtros de metadatos la tabla en cuestión, sin embargo, si estamos con cualquier otra herramienta que no disponga de un buscador para el diccionario de datos de  la base de datos. La forma más ágil de buscar entre todos los objetos es utilizando alguna de las tablas de sistema de Oracle que contienen los metadatos de todos los objetos. Ubicadas en el esquema SYSTEM (SYS), almacenan toda la información sobre los objetos de la BBDD (tablas, columnas, índices, etc).

Tablas de sistema en Oracle

Existe una tabla por cada nivel de acceso en función del usuario con el que nos hemos conectado, por ejemplo, las tablas que agrupan toda la información sobre los índices serían DBA_INDEXES, ALL_INDEXES y USER_INDEXES.

  • DBA_: contiene todos los objetos de la instancia a la que nos hemos conectado. Es necesario tener los privilegios suficientes.
  • ALL_: contiene todos los objetos accesibles por el usuario con el que nos hemos conectado, tanto los propios como sobre los que se tiene acceso (ya sean de otros usuarios o esquemas)
  • USER_: sólo contiene los objetos que pertenecen al usuario.

Estas tablas de sistema o metadatos nos permiten buscar rápidamente cualquier objeto de la base de datos. En la documentación oficial puedes consultar todas estas tablas; las que más utilizamos nosotros son:

  • SYS.DBA_TABLES ó SYS.DBA_ALL_TABLES (Tablas)
  • SYS.DBA_VIEWS (Vistas)
  • SYS.DBA_INDEXES (Índices)
  • SYS.DBA_TABLESPACES (Tablespaces)
  • SYS.DBA_USERS (Usuarios)
  • SYS.DBA_PROCEDURES (Procedures)
  • SYS.DBA_SEQUENCES (Secuencias)
  • SYS.DBA_TRIGGERS (Triggers o Disparadores)

Una consulta de ejemplo con la que obtendríamos el nombre completo de una tabla sería:

SELECT OWNER, TABLE_NAME, TABLESPACE_NAME
FROM SYS.DBA_TABLES 
WHERE TABLE_NAME LIKE '%EMPLEADOS%';

En relación a las tablas DBA_TABLES y DBA_ALL_TABLES, mencionar que la diferencia radica en que la primera sólo contiene información de las tablas, mientras que la segunda incluye también metadatos de los Object Tables, que en Oracle son tipos de datos definidos por el propio usuario (son estructuras o arrays que pueden incorporarse como columnas de una tabla o incluso permiten crear nuevas tablas instanciando estos objetos; para crearlos se utiliza la sentencia CREATE TYPE < nombre > AS OBJECT).

SYS.ALL_TAB_COLS: buscar tablas por sus columnas en Oracle

Otra tabla de sistema en Oracle que nos puede ayudar a la hora de buscar información en los metadatos es  SYS.DBA_TAB_COLS. Contiene las columnas de cada objeto Table de nuestra base de datos, de forma que buscando por el campo COLUMN_NAME podremos saber en qué tablas se encuentra (útil cuando sabemos el campo de destino y queremos saber saber de donde viene o por donde pasa).

Por ejemplo, para obtener todas las tablas del modelo de datos que incluyen la columna FECHA_ALTA podríamos utilizar esta query:

SELECT OWNER, TABLE_NAME, COLUMN_NAME
FROM SYS.ALL_TAB_COLS
WHERE COLUMN_NAME = 'FECHA_ALTA';

SYS.DBA_TAB_PRIVS: Ver los permisos o privilegios de una tabla en Oracle

La forma más rápida de ver los permisos de una o varias tablas es utilizando la tabla de sistema SYS.DBA_TAB_PRIVS. Filtrando por el nombre de la tabla recuperaremos una tupla con cada permiso otorgado (select, alter, insert, update, delete, execute, etc.), el rol al que se le ha asignado y el usuario propietario. Aunque la nomenclatura de la vista incluye _TAB, se pueden consultar los permisos de cualquier objeto de la BBDD: tablas, vistas, secuencias, etc.

SELECT GRANTEE, OWNER, TABLE_NAME, GRANTOR, PRIVILEGE
FROM SYS.DBA_TAB_PRIVS
WHERE TABLE_NAME LIKE '%EMPLEADOS%'

2 comentarios

Deja un comentario

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *

What is 15 + 6 ?
Please leave these two fields as-is: