El Diccionario de Datos.

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 (UPDATE, DELETE o 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 o 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 (como TABLE, VIEW, INDEX, SEQUENCE, 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 comando DDL.

•STATUS: Estado del objeto (VALID, INVALID o 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 y TABLE_TYPE. Proporciona los nombres de todos los objetos INDEX, TABLE, CLUSTER, VIEW, SYNONYM, SEQUENCE o 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 y 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 columnas NUMBER.

•Si se permiten valores nulos (¿Hay una restricción NOT NULL en 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_CONSTRAINTS describe las definiciones de restricción de las tablas.

•USER_CONS_COLUMNS describe 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 y 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: 

•table es el nombre de la tabla.

•column es el nombre de la columna de una tabla.

•text es 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