Viernes, 12 Junio 2020 03:29

El Diccionario de Datos

Rate this item
(0 votes)

Objetivos:

  • Utilizar las vistas de diccionario de datos para investigar los datos de los objetos.
  • Consultar diferentes vistas de diccionario de datos.

NOTA: Usamos como ejemplo la base de datos: ORCL, la cual viene por defecto en cualquier versión de ORACLE.


El Diccionario de Datos

Las tablas de usuario son tablas creadas por el usuario y contienen datos de negocio; un ejemplo sería EMPLOYEES. Hay otra recopilación de tablas y vistas en la base de datos Oracle conocida como diccionario de datos. Esta recopilación la crea y la mantiene Oracle Server y contiene información sobre la base de datos. El diccionario de datos se estructura en tablas y vistas, exactamente igual que otros datos de diccionario. El diccionario de datos no sólo es parte central de cada base de datos Oracle, sino que se trata además de una herramienta importante para todos los usuarios, desde los usuarios finales hasta los diseñadores de aplicaciones y los administradores de base de datos. 

Las sentencias SQL se utilizan para acceder al diccionario de datos. Como el diccionario de datos es de sólo lectura, sólo se pueden emitir consultas en las tablas y vistas. 

Puede consultar las vistas de diccionario que se basen en las tablas de diccionario para buscar información como: 

  • Definiciones de todos los objetos de esquema de la base de datos (tablas, vistas, índices, sinónimos, secuencias, procedimientos, funciones, paquetes, disparadores, etc).
  • Valores por defecto de las columnas.
  • Información acerca de las restricciones de integridad.
  • Nombres de usuarios Oracle.
  • Privilegios y roles que se han otorgado a cada usuario.
  • Otra información general de la base de datos.

Estructura de los Diccionarios de Datos

Las tablas base subyacentes almacenan información sobre la base de datos asociada. Oracle Server es el único que debe escribir y leer estas tablas. Rara vez se accede a ellas directamente. 

Hay varias vistas que resumen y muestran la información almacenada en las tablas base del diccionario de datos. Estas vistas descodifican los datos de la tabla base en información útil (como nombres de usuario o de tabla), mediante uniones y cláusulas WHERE para simplificar la información. A la mayoría de los usuarios se le proporciona acceso a las vistas más que a las tablas base. 

El usuario Oracle SYS es propietario de todas las tablas base y las vistas accesibles para usuarios de los diccionarios de datos. Ningún usuario Oracle debe modificar nunca (UPDATEDELETE INSERT) ninguna fila ni objeto de esquema contenido en el esquema SYS, ya que tal actividad puede comprometer la integridad de datos.

Regla de nomenclatura de vistas:

Prefijo de la Vista

Objetivo

USER

Vista del usuario (lo que está en el esquema; lo que es de su propiedad)

ALL

Vista del usuario ampliada (a lo que puede acceder)

DBA

Vista del administrador de la base de datos (lo que hay en los esquemas de todos)

V$

Datos relacionados con el rendimiento

Los diccionarios de datos se componen de juegos de vistas. En muchos casos, un juego se compone de tres vistas que contienen información similar y que se diferencien entre sí por sus prefijos. Por ejemplo, hay una vista denominada USER_OBJECTS, otra ALL_OBJECTS y una tercera DBA_OBJECTS

Estas tres vistas contienen información similar sobre los objetos de la base de datos, excepto en que el ámbito es diferente. USER_OBJECTS contiene información sobre objetos que son de su propiedad o que ha creado. ALL_OBJECTS contiene información sobre todos los objetos a los que tiene acceso. DBA_OBJECTS contiene información de todos los objetos que son propiedad de todos los usuarios. Para las vistas que tienen los prefijos ALL DBA, suele existir una columna adicional en la vista denominada OWNER para identificar de quién es propiedad el objeto. 

También hay un juego de vistas con el prefijo V$. Estas vistas son dinámicas y contienen información sobre el rendimiento. Las tablas dinámicas de rendimiento no son verdaderas tablas y no deben ser accesibles para la mayoría de los usuarios. Sin embargo, los DBA pueden consultar y crear vistas en las tablas y otorgar acceso a esas vistas a otros usuarios. Este curso no entra en detalle sobre estas vistas. 


Uso de las Vistas de Diccionario

Para familiarizarse con las vistas de diccionario, puede utilizar la vista de diccionario denominada DICTIONARY. Contiene el nombre y una breve descripción de cada vista de diccionario a la que tiene acceso. 

Puede escribir consultas para buscar información de un nombre de vista en particular o buscar en la columna COMMENTS una palabra o una frase. 

Ejemplo:

DESCRIBE dictionary;

/*En este ejemplo que se muestra, se describe la vista DICTIONARY la cual tiene dos columnas. */

 

SELECT *
FROM  dictionary
WHERE table_name = 'USER_OBJECTS';

/*La sentencia SELECT recupera información sobre la vista de diccionario denominada USER_OBJECTS. La vista USER_OBJECTS contiene información sobre todos los objetos de su propiedad. */

 

SELECT table_name
FROM  dictionary
WHERE LOWER(comments) LIKE '%columns%';

/*Por ejemplo, la siguiente consulta devuelve los nombres de todas las vistas a las que el usuario puede acceder, y que en las que la columna COMMENT contenga la palabra columns: */

 

Nota: Los nombres del diccionario de datos van en mayúsculas.


Vista USER_OBJECTS

  • Describe todos los objetos de su propiedad.
  • Es un modo útil de obtener un listado de todos los nombres y los tipos de objeto del esquema, además de la siguiente información: 

–Fecha de creación.

–Fecha de la última modificación.

–Estado (válido o no válido).

  • También puede consultar la vista ALL_OBJECTS para ver un listado de todos los objetos a los que tiene acceso.

Puede consultar la vista USER_OBJECTS para ver los nombres y los tipos de todos los objetos del esquema. Hay varias columnas en esta vista: 

  • OBJECT_NAME:Nombre del objeto.
  • OBJECT_ID:Número de objeto de diccionario del objeto.
  • OBJECT_TYPE:Tipo de objeto (comoTABLEVIEWINDEXSEQUENCE, etc...).
  • CREATED:Registro de hora de la creación del objeto.
  • LAST_DDL_TIME: Registro de hora de la última modificación del objeto resultante de un comandoDDL.
  • STATUS: Estado del objeto (VALID,INVALIDo N/A).
  • GENERATED: ¿Ha generado el sistema el nombre del índice?(Y|N).

Vista CAT

Para ver una consulta y una salida simplificadas, puede consultar la vista CAT. Esta vista sólo contiene dos columnas: TABLE_NAME TABLE_TYPE. Proporciona los nombres de todos los objetos INDEXTABLECLUSTERVIEWSYNONYMSEQUENCE UNDEFINED.

 

Nota: Éste no es un listado completo de las columnas.

Ejemplos:

DESCRIBE user_objects;

SELECT object_name, object_type, created, status
FROM  user_objects
ORDER BY object_type;

/*Este ejemplo muestra los nombres, los tipos, las fechas de creación y el estado de todos los objetos que son propiedad de este usuario.*/


Vista USER_TABLES

Puede utilizar la vista USER_TABLES para obtener los nombres de todas las tablas. La vista USER_TABLES contiene información sobre las tablas. Además de proporcionar el nombre de la tabla, contiene información detallada sobre el almacenamiento. 

Nota: Para obtener un listado completo de las columnas de la vista USER_TABLES, consulte “USER_TABLES” en Oracle Database Reference

También puede consultar las vistas ALL_TABLES TABS para ver un listado de todas las tablas a las que tiene acceso: 

Ejemplos:

DESCRIBE user_tables;

SELECT table_name
FROM  user_tables;

SELECT table_name
FROM  tabs;

Vista USER_TAB_COLUMNS

Puede consultar la vista USER_TAB_COLUMNS para buscar información detallada sobre las columnas de las tablas. Mientras que la vista USER_TABLES proporciona información de los nombres de tabla y el almacenamiento, la información de columna detallada se encuentra en la vista USER_TAB_COLUMNS.

Esta vista contiene información como:

  • Nombres de columnas.
  • Tipos de datos de columnas.
  • Longitud de tipos de datos.
  • Precisión y escala para las columnasNUMBER.
  • Si se permiten valores nulos (¿Hay una restricciónNOT NULLen la columna?).
  • Valor por defecto Nota: Para obtener un listado completo y una descripción de las columnas de la vista.

Ejemplo:

SELECT column_name, data_type, data_default,
       data_precision, data_scale, nullable
FROM user_tab_columns
WHERE table_name = 'EMPLOYEES';

/*Este ejemplo muestra algunas informaciones de las columnas de la tabla employees.*/


Información de Restricción

  • USER_CONSTRAINTSdescribe las definiciones de restricción de las tablas.
  • USER_CONS_COLUMNSdescribe columnas de su propiedad y especificadas en restricciones.

Puede averiguar los nombres de las restricciones, el tipo de restricción, el nombre de tabla a la que se aplica la restricción, la condición para las restricciones de comprobación, información de restricción de clave ajena, la regla de supresión para restricciones de clave ajena, el estado y otros muchos tipos de información sobre las restricciones.

 

Columnas Útiles:

CONSTRAINT_NAME: nombre de la restricción.

CONSTRAINT_TYPE puede ser:

  • C (restricción de comprobación en una tabla)
  • P (clave primaria)
  • U (clave única)
  • R (integridad referencial)
  • V (con opción de comprobación, en una vista)
  • O (con sólo lectura, en una vista)

TABLA_NAME: nombre de la tabla en la cual se encuentra la columna con la restricción.

COLUMN_NAME: Columna a la cual pertenece la restricción.

DELETE_RULE puede ser:

  • CASCADE: Si el registro principal se suprime, los registros secundarios también

se suprimen.

  • NO ACTION: Sólo se puede suprimir un registro principal si no existen

registros secundarios.

STATUS puede ser:

  • ENABLED: La restricción está activa.
  • DISABLED: La restricción se hace no activa.

LAST_CHANGE: fecha de la ultima ves que dicha restricción fue deshabilitada o habilitada.

Ejemplo:

SELECT CC.constraint_name, C.constraint_type, CC.table_name,
       CC.column_name, C.delete_rule, C.status, C.last_change
FROM user_cons_columns CC, user_constraints C
WHERE CC.constraint_name = C.constraint_name
AND CC.table_name in ('EMPLOYEES','COUNTRIES','DEPARTMENTS');

/*En el anterior ejemplo, gracias a la columna constraint_name presente en ambas tablas, enlazamos USER_CONSTRAINTS USER_CONS_COLUMNS para así ver informaciones relacionas a los constraints presentes en las tablas: 'EMPLOYEES','COUNTRIES','DEPARTMENTS'.*/


Vistas del Diccionario de Datos

Una vez creada la vista, puede consultar la vista de diccionario de datos denominada USER_VIEWS para ver el nombre de la vista y la definición de vista.

Columnas Útiles:

  • VIEW_NAME: Nombre de las Vista.
  • TEXT_LENGTH: Cantidad de caracteres usados en la Vista(incluye los espacios).
  • TEXT: Script de la Vista.
  • READ_ONLY: Valores: Y=No permite modificación de los datos, N=Permite modificar los Datos.

Ejemplo:

SELECT text
FROM user_views
WHERE view_name = 'EMP_DETAILS_VIEW';

/*Acá vemos texto de la vista EMP_DETAILS_VIEW.*/


Vista USER_SEQUENCES

La vista USER_SEQUENCES describe todas las secuencias que son de su propiedad. Al crear la secuencia, especifica criterios que se almacenan en la vista USER_SEQUENCES. Las columnas de esta vista son: 

  • SEQUENCE_NAME: Nombre de la secuencia 
  • MIN_VALUE: Valor mínimo de la secuencia 
  • MAX_VALUE: Valor máximo de la secuencia 
  • INCREMENT_BY: Valor en el que se incrementa la secuencia 
  • CYCLE_FLAG: ¿Se ajustará la secuencia al alcanzar el límite? 
  • ORDER_FLAG: ¿Se generan los números de secuencia por orden? 
  • CACHE_SIZE: Número de números de secuencia que se almacenarán en caché 
  • LAST_NUMBER: Último número de secuencia escrito en disco. Si una secuencia utiliza el almacenamiento en caché, el número escrito en disco es el último que se coloca en la caché de la secuencia. Es probable que ese número sea mayor que el último número de secuencia que se haya utilizado.

Ejemplo:

SELECT sequence_name, min_value, max_value,
        increment_by, last_number
FROM user_sequences;

/*Este ejemplo muestra informaciones relevantes de las secuencias que pertenecen al Usuario conectado. Es recomendable ejecutar secuencia.NEXTVAL previamente para así asimilar mejor la info mostrada.*/


Vista USER_SYNONYMS

La vista de diccionario USER_SYNONYMS describe sinónimos privados (sinónimos que son de su propiedad).

Puede consultar esta vista para buscar sus sinónimos. Puede consultar ALL_SYNONYMS para averiguar el nombre de todos los sinónimos que tiene a su disposición y de los objetos sobre los que se aplican estos sinónimos.

Las columnas de esta vista son:

  • SYNONYM_NAME: Nombre del sinónimo.
  • TABLE_OWNER: Propietario del objeto al que hace referencia el sinónimo.
  • TABLE_NAME: Nombre del objecto al cual pertenece el sinónimo.
  • DB_LINK: Nombre de la referencia de enlace de base de datos (si existe alguno).

Ejemplo:

SELECT *
FROM user_synonyms;

Vista USER_SOURCE

La vista USER_SOURCE describe el código fuente de los objectos almacenados en la base de datos y que pertenecen al usuario actual.

 

Columnas de USER_SOURCE:

  • NAME: Nombre del objecto.
  • TYPE: Es el tipo de objecto:FUNCTION, JAVA SOURCE, PACKAGE, PACKAGE BODY, PROCEDURE, TRIGGER, TYPE, TYPE BODY.
  • LINE: Es el numero de linea.
  • TEXT: Código Fuente del Objeto.

Ejemplo:

SELECT text
FROM user_source
WHERE name = 'SECURE_DML';

/*El Query anterior muestra del código fuente del procedimiento: SECURE_DML.*/


Comentarios

Puede agregar un comentario de hasta 2.000 bytes sobre una columna, una tabla, una vista o una instantánea mediante la sentencia COMMENT. El comentario se almacena en el diccionario de datos y se puede ver en una de las siguientes vistas de diccionario de datos en la columna COMMENTS: 

  • ALL_COL_COMMENTS: Muestra los Comentarios de las columnas a las cuales el usuario tiene acceso.
  • USER_COL_COMMENTS: Muestra los Comentarios de las columnas del usuario.
  • ALL_TAB_COMMENTS: Muestra los Comentarios de las tablas a las cuales el usuario tiene acceso.
  • USER_TAB_COMMENTS: Muestra los Comentarios de las tablas del usuario.

Sintaxis:

COMMENT ON TABLE table | COLUMN table.column
IS 'text';

En la sintaxis: 

  • tablees el nombre de la tabla.
  • columnes el nombre de la columna de una tabla.
  • textes el texto del comentario.

Puede borrar un comentario de la base de datos definiéndolo en una cadena vacía ('').

Ejemplos:

COMMENT ON TABLE employees IS 'Almacena el detalle de los empleados de la Empresa.';

/*Agrega el Comentario a la tabla employees.*/

 

SELECT *
FROM user_tab_comments
WHERE LOWER(table_name) = 'employees';

/*Muestra el comentario agregado.*/

 

COMMENT ON TABLE employees IS '';

/*Remueve el Comentario de la tabla employees.*/


Algunas Vistas/Tablas Útiles del Diccionario de Datos

Información Sobre:

Tablas/Vistas

Bloques libres que podrían unirse:

dba_free_space_coalesced

Bloques libres:

dba_free_space, user_free_space

Campos de tablas: 

dba_cons_columns, user_cons_columns, all_cons_columns

Código de funciones y procedimientos: 

dba_source, user_source, all_source

Columnas de las tablas: 

dba_tab_columns, user_tab_columns, all_tab_columns

Columnas de los índices:

dba_ind_columns, user_ind_columns, all_ind_columns

Extensiones que forman los segmentos:

dba_extents, user_extents

Ficheros que componen los datafiles: 

dba_data_files

Índices:

dba_indexes, user_indexes, all_indexes

Información de objetos en general:

dba_objects, user_objects, all_objects

Límites de recursos en cuanto a espacio máximo en tablespaces: 

dba_ts_quotas, user_ts_quotas

Límites de recursos en cuanto a restricciones en claves:

user_password_limits

Límites de recursos:

user_resource_limits

Perfiles y sus límites de recursos asociados:

dba_profiles

Permisos sobre tablas asignados a roles o usuarios:

dba_tab_privs

Privilegios asignados a roles o usuarios:

dba_sys_privs

Restricciones/ constraints de todo tipo:

dba_constraints, user_constraints, all_constraints

Roles asignados a roles o usuarios:

dba_role_privs, user_role_privs

Roles:

dba_roles

Secuencias:

dba_sequences, user_sequences, all_sequences

Segmentos de Rollback: 

dba_rollback_segs

Segmentos:

dba_segments, user_segments, all_segments

Sinónimos:

dba_synonyms, user_synonyms, all_synonyms

Tablas :

dba_tables, user_tables, all_tables

Tablas, vistas, sinónimos y secuencias: 

dba_catalog, user_catalog, all_catalog

Tablespaces:

dba_tablespaces, user_tablespaces

Usuarios:

dba_users, user_users, all_users

Vistas:

dba_views, user_views, all_views


Link con un Archivo de ejercicios de práctica!!!


Fuente: Base de Datos Oracle 10g: Conceptos Fundamentales de SQL 1

Read 1419 times Last modified on Miércoles, 08 Julio 2020 15:12

Leave a comment

Make sure you enter all the required information, indicated by an asterisk (*). HTML code is not allowed.

Magic PL/SQL

Blog orientado al desarrollo de PL/SQL en el "Maravilloso Mundo ORACLE". Cursos Online y Tutoriales Gratis.