Diccionario de Oracle (III) - USER_TABLES - USER_IND_COLUMNS - USER_TAB_COLUMNS

Para ver las tablas del diccionario que podemos consultar para ver información de nuestras tablas, realizamos esta consulta:

SELECT * FROM DICT WHERE TABLE_NAME LIKE '%TABLES%'

• USER_TABLES Description of the user's own relational tables

Y vemos lo que tenemos en USER_TABLES

SELECT * FROM USER_TABLES

Entre otros campos, podemos ver:
TABLE_NAME : Nombre de la tabla
NUM_ROWS : Número de filas de la tabla
LAST_ANALYZED : Fecha en que se analizo por última vez.


Si queremos ver información de las columnas

SELECT * FROM DICT WHERE TABLE_NAME LIKE '%COLUMN%'

• USER_IND_COLUMNS COLUMNs comprising user's INDEXes and INDEXes on user's TABLES
• USER_TAB_COLUMNS Columns of user's tables, views and clusters

Y miramos las tablas que nos interesan:

SELECT * FROM USER_IND_COLUMNS

Entre otros campos, tenemos:
INDEX_NAME: Indice
TABLE_NAME: Tabla sobre la que está el índice
COLUMN_NAME: Columna indexada


SELECT * FROM USER_TAB_COLUMNS

Entre otros campos tenemos:
TABLE_NAME: Nombre de la tabla o vista
COLUMN_NAME: Nombre de la columna
DATA_TYPE: Tipo de datos de la columna
DATA_LENGTH: Longitud definida en la columna en bytes

Diccionario de Oracle (II) - USER_INDEXES y Indices basados en función

SELECT * FROM DICT WHERE TABLE_NAME LIKE '%INDEX%'



Entre otros, vemos:

• USER_INDEXES Description of the user's own indexes
• ALL_INDEXES Descriptions of indexes on tables accessible to the user

SELECT * FROM USER_INDEXES

Podemos ver información sobre los índices, entre otros:
INDEX_NAME: nombre del indice
TABLE_NAME: Tabla indexada
STATUS: Si es valido o no
LAST_ANALYZED: Fecha en que se analizo por última vez.
FUNCIDX_STATUS: ENABLED o DISABLED. Indica si la funcion en que se basa un índice esta correctamente compilada o no.


Si tenemos un índice basado en función, eso es,
un índice que en su definición contenga la llamada a una función, por ej:

CREATE INDEX indx_mes ON mi_tabla(
TO_CHAR(fecha,'MM'));

CREATE INDEX indx_nif ON mi_tabla (
PQT_UTILIDADES.FNT_NORMALIZA_NIF(nif));

En el primer caso no vamos a tener ningún problema ya que es una función de Oracle (To_Char), pero en el segundo caso, es una función definida por nosotros en un paquete PL/SQL que podría quedar descompilado.

Eso lo podríamos ver con esta Query:

SELECT * FROM USER_INDEXES
WHERE INDEX_TYPE ='FUNCTION-BASED NORMAL'
AND FUNCIDX_STATUS ='DISABLED'

Si esto pasa, el índice se tiene que volver a crear, o bien con el código CREATE INDEX o bien con la sentencia REBUILD:

ALTER INDEX indx_nif REBUILD;

Tuvimos un problema con un índice basado en función.

Era una función que estaba dentro de un paquete con muchas otras funciones,
Siempre que modificábamos cualquier función (aunque no fuera la que utilizaba el índice), este quedaba descompilado.

Por eso es recomendable tener la función a parte.

Si modificas la función, obviamente tendrás que reconstruir el índice,
pero no te pasará que modificando cualquier función del paquete dónde se encuentra la utilizada por el índice, este deje de funcionar.

Diccionario de Oracle (I) - USER_SOURCE

Podemos ver el diccionario de Oracle con estas consultas

SELECT * FROM SYS.DICTIONARY SELECT * FROM DICT

Con una SELECT en la vista del diccionario de Oracle
podemos ver los objetos de Oracle que podemos consultar.

Unos ejemplos de los que más he utilizado:

Por ejemplo para buscar el código de las funciones o procedimientos almacenados
SELECT * FROM DICT WHERE TABLE_NAME LIKE '%SOURCE%'

USER_SOURCE: Source of stored objects accessible to the USER

SELECT * FROM USER_SOURCE

Las columnas que podemos ver son:
NAME: Nombre del objeto
TYPE: Tipo del objeto (FUNCTION,PROCEDURE,PACKAGE,...)
LINE: Numero de línea dentro del código
TEXT: Código

Ejemplo práctico.

• Creamos una función:

CREATE OR REPLACE FUNCTION FNT_AHORA RETURN DATE IS FECHA DATE; BEGIN SELECT SYSDATE INTO FECHA FROM DUAL; RETURN FECHA; END;

• Buscamos en la tabla USER_SOURCE

SELECT * FROM USER_SOURCE WHERE NAME='FNT_AHORA'


En esta tabla podemos buscar cualquier línea de código.

Es muy útil cuando tenemos decenas o centenares de funciones y procedimientos, en distintos paquetes, etc..

Nos permite localizar de forma rápida todas las líneas de código dónde aparece un texto en concreto (para localizar dónde utilizamos llamadas a cierta función, o dónde se nombra una tabla o una columna en concreto,...)