Los cursores se utilizan en PL/SQL para manejar las sentencias SELECT. Un cursor esta formado por un conjunto de registros devueltos por una instrucción SQL del tipo SELECT. Desde un punto de visto interno a la base de datos Oracle, los cursores son segmentos de memoria utilizados para realizar operaciones con los registros devueltos tras ejecutar una sentencia SELECT.Se pueden distinguir dos tipos de cursores:
- Cursores implícitos: Se utilizan cuando la sentencia SELECT devuelve un solo registro y su formato es como sigue:
DECLARE
lsalario empleados.salario%TYPE;
ldni empleados.dni%TYPE;
BEGIN
SELECT salario, dni
INTO lsalario, ldni
FROM empleados
WHERE nombre = 'Juan'
AND apellidos = 'Rodrigo Comas';
/* Resto de sentencias del bloque */
END;
Nota: Mucha gente considera que las sentencias UPDATE, dentro de un bloque PLSQL, son también cursores implícitos, no obstante, yo prefiero no incluirlas dentro de este concepto.
- Cursores explícitos: Se utilizan cuando la sentencia SELECT puede devolver varios registros. También se pueden utilizar en consultas que devuelvan un solo registro por razones de eficiencia con respecto a los cursores implícitos, eficiencia que mejorará especialmente si el cursor explícito se tiene que ejecutar varias veces dentro del bloque de código PL/SQL.
Un cursor explícito tiene que ser definido previamente como cualquier otra variable PLSQL y debe serle asignado un nombre. Veamos un ejemplo que muestra el DNI y el salario de los trabajadores incluidos en la tabla empleados:
DECLARE
CURSOR cemp IS
SELECT salario, dni
FROM empleados;
cepm_rec cemp%ROWTYPE;
BEGIN
FOR cemp_rec IN cemp
LOOP
DBMS_OUTPUT.PUT_LINE
(cemp_rec.dni || ' ' || cemp_rec.salario);
END LOOP;
END;
Los cursores explícitos admiten el uso de parámetros. Los parámetros deben declararse junto con el cursor. Por ejemplo:
DECLARE
CURSOR cemp(pnombre IN VARCHAR2) IS
SELECT salario, dni
FROM empleados
WHERE nombre = pnombre;
cepm_rec cemp%ROWTYPE;
vnombre VARCHAR2(20);
BEGIN
vnombre := 'Juan';
DBMS_OUTPUT.PUT_LINE
('Sueldo de los empleados con nombre ' || vnombre);
FOR cemp_rec IN cemp(vnombre)
LOOP
DBMS_OUTPUT.PUT_LINE
(cemp_rec.dni || ' ' || cemp_rec.salario);
END LOOP;
END;
En los ejemplos anteriores los cursores se han controlado con la sentencia FOR pero también pueden controlarse mediante el uso de las sentencias OPEN, FETCH y CLOSE. La sentencia OPEN identifica el cursor que se tiene que utilizar. La sentencia FETCH pone, registro a registro, los valores devueltos por el cursor en las variables correspondientes, variables que pueden estar constituidas por una lista de variables o un registro PLSQL (este es el caso de los ejemplos que incluyo en este artículo). Por último, la sentencia CLOSE cierra el cursor y libera la memoria reservada. Veamos como quedaría nuestro ejemplo utilizando este tipo de sentencias en lugar de utilizar la sentencia FOR:
DECLARE
CURSOR cemp(pnombre IN VARCHAR2) IS
SELECT salario, dni
FROM empleados
WHERE nombre = pnombre;
cepm_rec cemp%ROWTYPE;
vnombre VARCHAR2(20);
BEGIN
vnombre := 'Juan';
DBMS_OUTPUT.PUT_LINE
('Sueldo de los empleados con nombre ' || vnombre);
OPEN cemp(vnombre);
LOOP
FETCH cemp INTO cemp_rec;
DBMS_OUTPUT.PUT_LINE
(cemp_rec.dni || ' ' || cemp_rec.salario);
EXIT WHEN cemp%NOTFOUND; -- Último registro.
END LOOP;
DBMS_OUTPUT.PUT_LINE
('Número de empleados procesados ' || cemp%ROWCOUNT);
CLOSE cemp;
END;
Sólo me queda señalar que existe una tercera opción para manejar cursores que a mí, particularmente, no me gusta utilizar pero que no quiero omitir:
DECLARE
TYPE ecursor IS REF CURSOR RETURN empleados%ROWTYPE;
cemp ecursor;
cepm_rec empleados%ROWTYPE;
BEGIN
OPEN cemp FOR SELECT * FROM empleados;
FOR cemp_rec IN cemp
LOOP
DBMS_OUTPUT.PUT_LINE
(cemp_rec.dni || ' ' || cemp_rec.salario);
END LOOP;
END;
Finalmente sólo mencionar que existen cuatro tipos de atributos que nos permiten controlar la ejecución de un cursor:
- %ISOPEN: Devuelve "true" si el cursor está abierto.
- %FOUND: Devuelve "true" si el registro fue satisfactoriamente procesado.
- %NOTFOUND: Devuelve "true" si el registro no pudo ser procesado. Normalmente esto ocurre cuando ya se han procesado todos los registros devueltos por el cursor.
- %ROWCOUNT: Devuelve el número de registros que han sido procesados hasta ese momento.
Suscribirse a Ger-7
Entradas
Comentarios
domingo, 7 de agosto de 2011
Funcion Trigger
Un trigger (o disparador) en una Base de datos , es un procedimiento que se ejecuta cuando se cumple una condición establecida al realizar una operación. Dependiendo de la base de datos, los triggers pueden ser de inserción (INSERT), actualización (UPDATE) o borrado (DELETE). Algunas bases de datos pueden ejecutar triggers al crear, borrar o editar usuarios, tablas, bases de datos u otros objetos.
Ejemplo
Un sencillo ejemplo (para SQL Server) sería crear un Trigger para insertar un pedido de algún producto cuando la cantidad de éste, en nuestro almacén, sea inferior a un valor dado.
El estándar SQL:2003 requiere que los disparadores den a los programadores acceso a las variables de un registro utilizando una sintaxis como REFERENCING NEW AS n. Por ejemplo, si un disparador está monitoreando los cambios en la columna salario, podría escribirse un disparador como:
Ejemplo
Un sencillo ejemplo (para SQL Server) sería crear un Trigger para insertar un pedido de algún producto cuando la cantidad de éste, en nuestro almacén, sea inferior a un valor dado.
BEFORE UPDATE ON tabla_almacen
FOR ALL records
IF :NEW.producto < 100 THEN
INSERT INTO tabla_pedidos(producto) VALUES ('1000');
END IF;
SELECT DBO.POLVE.TEST
END
Disparadores en MySQL
Los disparadores son soportados en MySQL a partir de la versión 5.0.2. Algunos de los soportes existentes son los disparadores para las sentencias INSERT, UPDATE y DELETEEl estándar SQL:2003 requiere que los disparadores den a los programadores acceso a las variables de un registro utilizando una sintaxis como REFERENCING NEW AS n. Por ejemplo, si un disparador está monitoreando los cambios en la columna salario, podría escribirse un disparador como:
CREATE TRIGGER ver_salario
BEFORE UPDATE ON empleados
REFERENCING NEW ROW AS n, OLD ROW AS o
FOR EACH ROW
IF n.salario <> o.salario THEN
END IF;
Procedimientos Y Funciones
Los procedimientos PL/SQL son subprogramas compuestos por un conjunto de sentencias SQL. Funciones y procedimientos PL/SQL no son muy diferentes. Un procedimiento o función está constituido por un conjunto de sentencias SQL y PL/SQL lógicamente agrupados para realizar una tarea específica. Los procedimientos y funciones almacenados constituyen un bloque de código PLSQL que ha sido compilado y almacenado en las tablas del sistema de la base de datos Oracle.
Los procedimientos o funciones PLSQL son dinámicos ya que admiten parámetros que les pueden ser pasados antes de su ejecución. Por lo tanto, un procedimiento o función puede realizar diferentes tareas dependiendo de los parámetros que le hayan pasado.
Los procedimientos y funciones Oracle están compuestos por una parte en la que se definen de variables y cursores, otra parte ejecutable compuesta por sentencias SQL y PLSQL, y otra parte opcional enfocada a manejar las excepciones y errores ocurridos durante la ejecución.
Los pasos que sigue Oracle para ejecutar un procedimiento o función son los siguientes:
- Verificar si el usuario tiene permiso de ejecución.
- Verificar la validez del procedimiento o función.
- Y finalmente ejecutarlo.
Las ventajas de usar procedimientos y funciones en lugar de scripts SQL son:
- Facilidad para gestionar la seguridad.
- Mejor rendimiento al estar compilados y almacenados en la base de datos.
- Mejor gestión de la memoria.
- Mayor productividad e integridad.
La diferencia más importante entre los procedimientos y las funciones es que una función, al final de su ejecución, devuelve un valor al bloque PL/SQL que la llamó. Sin embargo, en los procedimientos esto no es posible, aunque si que podemos definir múltiples parámetros de salida que se devolverían al bloque PL/SQL desde el que se ejecutó el procedimiento (esto último también es posible en las funciones).
Sintaxis de un procedimiento PLSQL:
CREATE OR REPLACE PROCEDURE [esquema].nombre-procedim
(nombre-parámetro {IN, OUT, IN OUT} tipo de dato, ..) {IS, AS}
Declaración de variables;
Declaración de constantes;
Declaración de cursores;
BEGIN
Cuerpo del subprograma PL/SQL;
EXCEPTION
Bloque de excepciones PL/SQL;
END;
Sintaxis de una función PLSQL:
CREATE OR REPLACE FUNCTION [esquema].nombre-funcion
(nombre-parámetro {IN, OUT, IN OUT} tipo-de-dato, ..)
RETURN tipo-de-dato {IS, AS}
Declaración de variables;
Declaración de constantes;
Declaración de cursores;
BEGIN
Cuerpo del subprograma PL/SQL;
EXCEPTION
Bloque de excepciones PL/SQL;
END;
Aclaraciones sobre la sintaxis:
Nombre-parámetro: es el nombre que nosotros queramos dar al parámetro. Podemos utilizar múltiples parámetros. En caso de no necesitarlos podemos omitir los paréntesis.
IN: especifica que el parámetro es de entrada y que por tanto dicho parámetro tiene que tener un valor en el momento de llamar a la función o procedimiento. Si no se especifica nada, los parámetros son por defecto de tipo entrada.
OUT: especifica que se trata de un parámetro de salida. Son parámetros cuyo valor es devuelto después de la ejecución el procedimiento al bloque PL/SQL que lo llamó. Las funciones PLSQL no admiten parámetros de salida.
IN OUT: Son parámetros de entrada y salida a la vez.
Tipo-de-dato: Indica el tipo de dato PLSQL que corresponde al parámetro (NUMBER, VARCHAR2, etc).
Ejemplos prácticos
Ejemplo de creación de un procedimiento PL/SQL:
CREATE OR REPLACE PROCEDURE
procedimiento1 (a IN NUMBER, b IN OUT NUMBER) IS
vmax NUMBER;
BEGIN
SELECT salario, maximo
INTO b, vmax
FROM empleados
WHERE empleado_id=a;
IF b < vmax THEN
b:=b+100;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
b:=-1;
RETURN;
WHEN OTHERS THEN
RAISE;
END;
Ejemplo de SQL script que llama a un procedimiento PLSQL:
DECLARE
vsalario NUMBER;
BEGIN
procedimiento1 (3213, vsalario)
dbms_output.put_line
('El salario del empleado 3213 es ', vsalario);
END;
Ejemplo de ejecución desde SQL de un procedimiento PL/SQL que sólo utiliza parámetros de entrada:
sql> exec proc_solo_parametros_entrada (2000, 2, 'Pepe');
Los procedimientos o funciones PLSQL son dinámicos ya que admiten parámetros que les pueden ser pasados antes de su ejecución. Por lo tanto, un procedimiento o función puede realizar diferentes tareas dependiendo de los parámetros que le hayan pasado.
Los procedimientos y funciones Oracle están compuestos por una parte en la que se definen de variables y cursores, otra parte ejecutable compuesta por sentencias SQL y PLSQL, y otra parte opcional enfocada a manejar las excepciones y errores ocurridos durante la ejecución.
Los pasos que sigue Oracle para ejecutar un procedimiento o función son los siguientes:
- Verificar si el usuario tiene permiso de ejecución.
- Verificar la validez del procedimiento o función.
- Y finalmente ejecutarlo.
Las ventajas de usar procedimientos y funciones en lugar de scripts SQL son:
- Facilidad para gestionar la seguridad.
- Mejor rendimiento al estar compilados y almacenados en la base de datos.
- Mejor gestión de la memoria.
- Mayor productividad e integridad.
La diferencia más importante entre los procedimientos y las funciones es que una función, al final de su ejecución, devuelve un valor al bloque PL/SQL que la llamó. Sin embargo, en los procedimientos esto no es posible, aunque si que podemos definir múltiples parámetros de salida que se devolverían al bloque PL/SQL desde el que se ejecutó el procedimiento (esto último también es posible en las funciones).
Sintaxis de un procedimiento PLSQL:
CREATE OR REPLACE PROCEDURE [esquema].nombre-procedim
(nombre-parámetro {IN, OUT, IN OUT} tipo de dato, ..) {IS, AS}
Declaración de variables;
Declaración de constantes;
Declaración de cursores;
BEGIN
Cuerpo del subprograma PL/SQL;
EXCEPTION
Bloque de excepciones PL/SQL;
END;
Sintaxis de una función PLSQL:
CREATE OR REPLACE FUNCTION [esquema].nombre-funcion
(nombre-parámetro {IN, OUT, IN OUT} tipo-de-dato, ..)
RETURN tipo-de-dato {IS, AS}
Declaración de variables;
Declaración de constantes;
Declaración de cursores;
BEGIN
Cuerpo del subprograma PL/SQL;
EXCEPTION
Bloque de excepciones PL/SQL;
END;
Aclaraciones sobre la sintaxis:
Nombre-parámetro: es el nombre que nosotros queramos dar al parámetro. Podemos utilizar múltiples parámetros. En caso de no necesitarlos podemos omitir los paréntesis.
IN: especifica que el parámetro es de entrada y que por tanto dicho parámetro tiene que tener un valor en el momento de llamar a la función o procedimiento. Si no se especifica nada, los parámetros son por defecto de tipo entrada.
OUT: especifica que se trata de un parámetro de salida. Son parámetros cuyo valor es devuelto después de la ejecución el procedimiento al bloque PL/SQL que lo llamó. Las funciones PLSQL no admiten parámetros de salida.
IN OUT: Son parámetros de entrada y salida a la vez.
Tipo-de-dato: Indica el tipo de dato PLSQL que corresponde al parámetro (NUMBER, VARCHAR2, etc).
Ejemplos prácticos
Ejemplo de creación de un procedimiento PL/SQL:
CREATE OR REPLACE PROCEDURE
procedimiento1 (a IN NUMBER, b IN OUT NUMBER) IS
vmax NUMBER;
BEGIN
SELECT salario, maximo
INTO b, vmax
FROM empleados
WHERE empleado_id=a;
IF b < vmax THEN
b:=b+100;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
b:=-1;
RETURN;
WHEN OTHERS THEN
RAISE;
END;
Ejemplo de SQL script que llama a un procedimiento PLSQL:
DECLARE
vsalario NUMBER;
BEGIN
procedimiento1 (3213, vsalario)
dbms_output.put_line
('El salario del empleado 3213 es ', vsalario);
END;
Ejemplo de ejecución desde SQL de un procedimiento PL/SQL que sólo utiliza parámetros de entrada:
sql> exec proc_solo_parametros_entrada (2000, 2, 'Pepe');
Bloque Anonimo
Bloques PL/SQL
Un programa de PL/SQL está compuesto por bloques. Un programa está compuesto como mínimo de un bloque.
Los bloques de PL/SQL pueden ser de los siguientes tipos:
• Bloques anónimos
• Subprogramas
Estructura de un Bloque
Los bloques PL/SQL presentan una estructura específica compuesta de tres partes bien diferenciadas:
• La sección declarativa en donde se declaran todas las constantes y variables que se van a utilizar en la ejecución del bloque.
• La sección de ejecución que incluye las instrucciones a ejecutar en el bloque PL/SQL.
• La sección de excepciones en donde se definen los manejadores de errores que soportará el bloque PL/SQL.
Cada una de las partes anteriores se delimita por una palabra reservada, de modo que un bloque PL/SQL se puede representar como sigue:
[ declare | is | as ]
/*Parte declarativa*/
begin
/*Parte de ejecucion*/
[ exception ]
/*Parte de excepciones*/
end;
De las anteriores partes, únicamente la sección de ejecución es obligatoria, que quedaría delimitada entre las cláusulas BEGIN y END. Veamos un ejemplo de bloque PL/SQL muy genérico. Se trata de un bloque anónimos, es decir no lo identifica ningún nombre. Los bloques anónimos identifican su parte declarativa con la palabra reservada DECLARE.
DECLARE
/*Parte declarativa*/
nombre_variable DATE;
BEGIN
/*Parte de ejecucion
* Este código asigna el valor de la columna "nombre_columna"
* a la variable identificada por "nombre_variable"
*/
SELECT SYSDATE
INTO nombre_variable
FROM DUAL;
EXCEPTION
/*Parte de excepciones*/
WHEN OTHERS THEN
dbms_output.put_line('Se ha producido un error');
END;
A continuación vamos a ver cada una de estas secciones
Sección de Declaración de Variables
En esta parte se declaran las variables que va a necesitar nuestro programa. Una variable se declara asignandole un nombre o "identificador" seguido del tipo de valor que puede contener. También se declaran cursores, de gran utilidad para la consulta de datos, y excepciones definidas por el usuario. También podemos especificar si se trata de una constante, si puede contener valor nulo y asignar un valor inicial.
La sintaxis generica para la declaracion de constantes y variables es:
nombre_variable [CONSTANT] [NOT NULL][:=valor_inicial]
donde:
• tipo_dato: es el tipo de dato que va a poder almacenar la variable, este puede ser cualquiera de los tipos soportandos por ORACLE, es decir NUMBER , DATE , CHAR , VARCHAR, VARCHAR2, BOOLEAN ... Además para algunos tipos de datos (NUMBER y VARCHAR) podemos especificar la longitud.
• La cláusula CONSTANT indica la definición de una constante cuyo valor no puede ser modificado. Se debe incluir la inicialización de la constante en su declaración.
• La cláusula NOT NULL impide que a una variable se le asigne el valor nulo, y por tanto debe inicializarse a un valor diferente de NULL.
• Las variables que no son inicializadas toman el valor inicial NULL.
• La inicialización puede incluir cualquier expresión legal de PL/SQL, que lógicamente debe corresponder con el tipo del identificador definido.
• Los tipos escalares incluyen los definidos en SQL más los tipos VARCHAR y BOOLEAN. Este último puede tomar los valores TRUE, FALSE y NULL, y se suele utilizar para almacenar el resultado de alguna operación lógica. VARCHAR es un sinónimo de CHAR.
• También es posible definir el tipo de una variable o constante, dependiendo del tipo de otro identificador, mediante la utilización de las cláusulas %TYPE y %ROWTYPE. Mediante la primera opción se define una variable o constante escalar, y con la segunda se define una variable fila, donde identificador puede ser otra variable fila o una tabla. Habitualmente se utiliza %TYPE para definir la variable del mismo tipo que tenga definido un campo en una tabla de la base de datos, mientras que %ROWTYPE se utiliza para declarar varibales utilizando cursores.
Ejemplos:
Estructura de un bloque anónimo.
DECLARE
/* Se declara la variable de tipo VARCHAR2(15) identificada por v_location y se le asigna
el valor "Granada"*/
v_location VARCHAR2(15) := ’Granada’;
/*Se declara la constante de tipo NUMBER identificada por PI y se le asigna
el valor 3.1416*/
PI CONSTANT NUMBER := 3.1416;
/*Se declara la variable del mismo tipo que tenga el campo nombre de la tabla tabla_empleados
identificada por v_nombre y no se le asigna ningún valor */
v_nombre tabla_empleados.nombre%TYPE;
/*Se declara la variable del tipo registro correspondiente a un supuesto cursor, llamado
micursor, identificada por reg_datos*/
reg_datos micursor%ROWTYPE;
BEGIN
/*Parte de ejecucion*/
EXCEPTION
/*Parte de excepciones*/
END;
Estructura de un subprograma:
CREATE PROCEDURE simple_procedure IS
/* Se declara la variable de tipo VARCHAR2(15) identificada por v_location y se le asigna
el valor "Granada"*/
v_location VARCHAR2(15) := ’Granada’;
/*Se declara la constante de tipo NUMBER identificada por PI y se le asigna
el valor 3.1416*/
PI CONSTANT NUMBER := 3.1416;
/*Se declara la variable del mismo tipo que tenga el campo nombre de la tabla tabla_empleados
identificada por v_nombre y no se le asigna ningún valor */
v_nombre tabla_empleados.nombre%TYPE;
/*Se declara la variable del tipo registro correspondiente a un supuesto cursor, llamado
micursor, identificada por reg_datos*/
reg_datos micursor%ROWTYPE;
BEGIN
/*Parte de ejecucion*/
EXCEPTION
/*Parte de excepciones*/
END;
Un programa de PL/SQL está compuesto por bloques. Un programa está compuesto como mínimo de un bloque.
Los bloques de PL/SQL pueden ser de los siguientes tipos:
• Bloques anónimos
• Subprogramas
Estructura de un Bloque
Los bloques PL/SQL presentan una estructura específica compuesta de tres partes bien diferenciadas:
• La sección declarativa en donde se declaran todas las constantes y variables que se van a utilizar en la ejecución del bloque.
• La sección de ejecución que incluye las instrucciones a ejecutar en el bloque PL/SQL.
• La sección de excepciones en donde se definen los manejadores de errores que soportará el bloque PL/SQL.
Cada una de las partes anteriores se delimita por una palabra reservada, de modo que un bloque PL/SQL se puede representar como sigue:
[ declare | is | as ]
/*Parte declarativa*/
begin
/*Parte de ejecucion*/
[ exception ]
/*Parte de excepciones*/
end;
De las anteriores partes, únicamente la sección de ejecución es obligatoria, que quedaría delimitada entre las cláusulas BEGIN y END. Veamos un ejemplo de bloque PL/SQL muy genérico. Se trata de un bloque anónimos, es decir no lo identifica ningún nombre. Los bloques anónimos identifican su parte declarativa con la palabra reservada DECLARE.
DECLARE
/*Parte declarativa*/
nombre_variable DATE;
BEGIN
/*Parte de ejecucion
* Este código asigna el valor de la columna "nombre_columna"
* a la variable identificada por "nombre_variable"
*/
SELECT SYSDATE
INTO nombre_variable
FROM DUAL;
EXCEPTION
/*Parte de excepciones*/
WHEN OTHERS THEN
dbms_output.put_line('Se ha producido un error');
END;
A continuación vamos a ver cada una de estas secciones
Sección de Declaración de Variables
En esta parte se declaran las variables que va a necesitar nuestro programa. Una variable se declara asignandole un nombre o "identificador" seguido del tipo de valor que puede contener. También se declaran cursores, de gran utilidad para la consulta de datos, y excepciones definidas por el usuario. También podemos especificar si se trata de una constante, si puede contener valor nulo y asignar un valor inicial.
La sintaxis generica para la declaracion de constantes y variables es:
nombre_variable [CONSTANT] [NOT NULL][:=valor_inicial]
donde:
• tipo_dato: es el tipo de dato que va a poder almacenar la variable, este puede ser cualquiera de los tipos soportandos por ORACLE, es decir NUMBER , DATE , CHAR , VARCHAR, VARCHAR2, BOOLEAN ... Además para algunos tipos de datos (NUMBER y VARCHAR) podemos especificar la longitud.
• La cláusula CONSTANT indica la definición de una constante cuyo valor no puede ser modificado. Se debe incluir la inicialización de la constante en su declaración.
• La cláusula NOT NULL impide que a una variable se le asigne el valor nulo, y por tanto debe inicializarse a un valor diferente de NULL.
• Las variables que no son inicializadas toman el valor inicial NULL.
• La inicialización puede incluir cualquier expresión legal de PL/SQL, que lógicamente debe corresponder con el tipo del identificador definido.
• Los tipos escalares incluyen los definidos en SQL más los tipos VARCHAR y BOOLEAN. Este último puede tomar los valores TRUE, FALSE y NULL, y se suele utilizar para almacenar el resultado de alguna operación lógica. VARCHAR es un sinónimo de CHAR.
• También es posible definir el tipo de una variable o constante, dependiendo del tipo de otro identificador, mediante la utilización de las cláusulas %TYPE y %ROWTYPE. Mediante la primera opción se define una variable o constante escalar, y con la segunda se define una variable fila, donde identificador puede ser otra variable fila o una tabla. Habitualmente se utiliza %TYPE para definir la variable del mismo tipo que tenga definido un campo en una tabla de la base de datos, mientras que %ROWTYPE se utiliza para declarar varibales utilizando cursores.
Ejemplos:
Estructura de un bloque anónimo.
DECLARE
/* Se declara la variable de tipo VARCHAR2(15) identificada por v_location y se le asigna
el valor "Granada"*/
v_location VARCHAR2(15) := ’Granada’;
/*Se declara la constante de tipo NUMBER identificada por PI y se le asigna
el valor 3.1416*/
PI CONSTANT NUMBER := 3.1416;
/*Se declara la variable del mismo tipo que tenga el campo nombre de la tabla tabla_empleados
identificada por v_nombre y no se le asigna ningún valor */
v_nombre tabla_empleados.nombre%TYPE;
/*Se declara la variable del tipo registro correspondiente a un supuesto cursor, llamado
micursor, identificada por reg_datos*/
reg_datos micursor%ROWTYPE;
BEGIN
/*Parte de ejecucion*/
EXCEPTION
/*Parte de excepciones*/
END;
Estructura de un subprograma:
CREATE PROCEDURE simple_procedure IS
/* Se declara la variable de tipo VARCHAR2(15) identificada por v_location y se le asigna
el valor "Granada"*/
v_location VARCHAR2(15) := ’Granada’;
/*Se declara la constante de tipo NUMBER identificada por PI y se le asigna
el valor 3.1416*/
PI CONSTANT NUMBER := 3.1416;
/*Se declara la variable del mismo tipo que tenga el campo nombre de la tabla tabla_empleados
identificada por v_nombre y no se le asigna ningún valor */
v_nombre tabla_empleados.nombre%TYPE;
/*Se declara la variable del tipo registro correspondiente a un supuesto cursor, llamado
micursor, identificada por reg_datos*/
reg_datos micursor%ROWTYPE;
BEGIN
/*Parte de ejecucion*/
EXCEPTION
/*Parte de excepciones*/
END;
lunes, 4 de julio de 2011
Restricciones
Hemos visto que una de las alternativas que Oracle ofrece para asegurar la integridad de datos es el uso de restricciones (constraints). Aprendimos que las restricciones se establecen en tablas y campos asegurando que los datos sean válidos y que las relaciones entre las tablas se mantengan.
Vimos tres tipos de restricciones:
primary key, unique y check. Ahora veremos "foreign key".
Con la restricción "foreign key" se define un campo (o varios) cuyos valores coinciden con la clave primaria de la misma tabla o de otra, es decir, se define una referencia a un campo con una restricción "primary key" o "unique" de la misma tabla o de otra.
La integridad referencial asegura que se mantengan las referencias entre las claves primarias y las externas. Por ejemplo, controla que si se agrega un código de editorial en la tabla "libros", tal código exista en la tabla "editoriales".
También controla que no pueda eliminarse un registro de una tabla ni modificar la clave primaria si una clave externa hace referencia al registro. Por ejemplo, que no se pueda eliminar o modificar un código de "editoriales" si existen libros con dicho código.
La siguiente es la sintaxis parcial general para agregar una restricción "foreign key":
alter table NOMBRETABLA1
add constraint NOMBRERESTRICCION
foreign key (CAMPOCLAVEFORANEA)
references NOMBRETABLA2 (CAMPOCLAVEPRIMARIA);
Analicémosla:
- NOMBRETABLA1 referencia el nombre de la tabla a la cual le aplicamos la restricción,
- NOMBRERESTRICCION es el nombre que le damos a la misma,
- luego de "foreign key", entre paréntesis se coloca el campo de la tabla a la que le aplicamos la restricción que será establecida como clave foránea,
- luego de "references" indicamos el nombre de la tabla referenciada y el campo que es clave primaria en la misma, a la cual hace referencia la clave foránea. El campo de la tabla referenciada debe tener definida una restricción "primary key" o "unique"; si no la tiene, aparece un mensaje de error.
Para agregar una restricción "foreign key" al campo "codigoeditorial" de "libros", tipeamos:
alter table libros
add constraint FK_libros_codigoeditorial
foreign key (codigoeditorial)
references editoriales(codigo);
En el ejemplo implementamos una restricción "foreign key" para asegurarnos que el código de la editorial de la de la tabla "libros" ("codigoeditorial") esté asociada con un código válido en la tabla "editoriales" ("codigo").
Cuando agregamos cualquier restricción a una tabla que contiene información, Oracle controla los datos existentes para confirmar que cumplen con la restricción, si no los cumple, la restricción no se aplica y aparece un mensaje de error. Por ejemplo, si intentamos agregar una restricción "foreign key" a la tabla "libros" y existe un libro con un valor de código para editorial que no existe en la tabla "editoriales", la restricción no se agrega.
Actúa en inserciones. Si intentamos ingresar un registro (un libro) con un valor de clave foránea (codigoeditorial) que no existe en la tabla referenciada (editoriales), Oracle muestra un mensaje de error. Si al ingresar un registro (un libro), no colocamos el valor para el campo clave foránea (codigoeditorial), almacenará "null", porque esta restricción permite valores nulos (a menos que se haya especificado lo contrario al definir el campo).
Actúa en eliminaciones y actualizaciones. Si intentamos eliminar un registro o modificar un valor de clave primaria de una tabla si una clave foránea hace referencia a dicho registro, Oracle no lo permite (excepto si se permite la acción en cascada, tema que veremos posteriormente). Por ejemplo, si intentamos eliminar una editorial a la que se hace referencia en "libros", aparece un mensaje de error.
Esta restricción (a diferencia de "primary key" y "unique") no crea índice automáticamente.
La cantidad y tipo de datos de los campos especificados luego de "foreign key" DEBEN coincidir con la cantidad y tipo de datos de los campos de la cláusula "references".
Esta restricción se puede definir dentro de la misma tabla (lo veremos más adelante) o entre distintas tablas.
Una tabla puede tener varias restricciones "foreign key".
No se puede eliminar una tabla referenciada en una restricción "foreign key", aparece un mensaje de error.
Vimos tres tipos de restricciones:
primary key, unique y check. Ahora veremos "foreign key".
Con la restricción "foreign key" se define un campo (o varios) cuyos valores coinciden con la clave primaria de la misma tabla o de otra, es decir, se define una referencia a un campo con una restricción "primary key" o "unique" de la misma tabla o de otra.
La integridad referencial asegura que se mantengan las referencias entre las claves primarias y las externas. Por ejemplo, controla que si se agrega un código de editorial en la tabla "libros", tal código exista en la tabla "editoriales".
También controla que no pueda eliminarse un registro de una tabla ni modificar la clave primaria si una clave externa hace referencia al registro. Por ejemplo, que no se pueda eliminar o modificar un código de "editoriales" si existen libros con dicho código.
La siguiente es la sintaxis parcial general para agregar una restricción "foreign key":
alter table NOMBRETABLA1
add constraint NOMBRERESTRICCION
foreign key (CAMPOCLAVEFORANEA)
references NOMBRETABLA2 (CAMPOCLAVEPRIMARIA);
Analicémosla:
- NOMBRETABLA1 referencia el nombre de la tabla a la cual le aplicamos la restricción,
- NOMBRERESTRICCION es el nombre que le damos a la misma,
- luego de "foreign key", entre paréntesis se coloca el campo de la tabla a la que le aplicamos la restricción que será establecida como clave foránea,
- luego de "references" indicamos el nombre de la tabla referenciada y el campo que es clave primaria en la misma, a la cual hace referencia la clave foránea. El campo de la tabla referenciada debe tener definida una restricción "primary key" o "unique"; si no la tiene, aparece un mensaje de error.
Para agregar una restricción "foreign key" al campo "codigoeditorial" de "libros", tipeamos:
alter table libros
add constraint FK_libros_codigoeditorial
foreign key (codigoeditorial)
references editoriales(codigo);
En el ejemplo implementamos una restricción "foreign key" para asegurarnos que el código de la editorial de la de la tabla "libros" ("codigoeditorial") esté asociada con un código válido en la tabla "editoriales" ("codigo").
Cuando agregamos cualquier restricción a una tabla que contiene información, Oracle controla los datos existentes para confirmar que cumplen con la restricción, si no los cumple, la restricción no se aplica y aparece un mensaje de error. Por ejemplo, si intentamos agregar una restricción "foreign key" a la tabla "libros" y existe un libro con un valor de código para editorial que no existe en la tabla "editoriales", la restricción no se agrega.
Actúa en inserciones. Si intentamos ingresar un registro (un libro) con un valor de clave foránea (codigoeditorial) que no existe en la tabla referenciada (editoriales), Oracle muestra un mensaje de error. Si al ingresar un registro (un libro), no colocamos el valor para el campo clave foránea (codigoeditorial), almacenará "null", porque esta restricción permite valores nulos (a menos que se haya especificado lo contrario al definir el campo).
Actúa en eliminaciones y actualizaciones. Si intentamos eliminar un registro o modificar un valor de clave primaria de una tabla si una clave foránea hace referencia a dicho registro, Oracle no lo permite (excepto si se permite la acción en cascada, tema que veremos posteriormente). Por ejemplo, si intentamos eliminar una editorial a la que se hace referencia en "libros", aparece un mensaje de error.
Esta restricción (a diferencia de "primary key" y "unique") no crea índice automáticamente.
La cantidad y tipo de datos de los campos especificados luego de "foreign key" DEBEN coincidir con la cantidad y tipo de datos de los campos de la cláusula "references".
Esta restricción se puede definir dentro de la misma tabla (lo veremos más adelante) o entre distintas tablas.
Una tabla puede tener varias restricciones "foreign key".
No se puede eliminar una tabla referenciada en una restricción "foreign key", aparece un mensaje de error.
Consultas avanzadas
C
Consulta Oracle SQL para conocer Vista que muestra el estado de la base de datos:
select * from v$instance
•• Consulta Oracle SQL para conocer Consulta que muestra si la base de datos está abierta
select status from v$instance
•• Consulta Oracle SQL para conocer Vista que muestra los parámetros generales de Oracle
select * from v$system_parameter
•• Consulta Oracle SQL para conocer Versión de Oracle
select value from v$system_parameter where name = 'compatible'
•• Consulta Oracle SQL para conocer Ubicación y nombre del fichero spfile
select value from v$system_parameter where name = 'spfile'
•• Consulta Oracle SQL para conocer Ubicación y número de ficheros de control
select value from v$system_parameter where name = 'control_files'
•• Consulta Oracle SQL para conocer Nombre de la base de datos
select value from v$system_parameter where name = 'db_name'
•• Consulta Oracle SQL para conocer Vista que muestra las conexiones actuales a Oracle Para visualizarla es necesario entrar con privilegios de administrador
select osuser, username, machine, program
from v$session
order by osuser
•• Consulta Oracle SQL para conocer Vista que muestra el número de conexiones actuales a Oracle agrupado por aplicación que realiza la conexión
select program Aplicacion, count(program) Numero_Sesiones
from v$session
group by program
order by Numero_Sesiones desc
•• Consulta Oracle SQL para conocer Vista que muestra los usuarios de Oracle conectados y el número de sesiones por usuario
select username Usuario_Oracle, count(username) Numero_Sesiones
from v$session
group by username
order by Numero_Sesiones desc
Propietarios de objetos y número de objetos por propietario
select owner, count(owner) Numero
from dba_objects
group by owner
order by Numero desc
•• Consulta Oracle SQL para conocer Diccionario de datos (incluye todas las vistas y tablas de la Base de Datos)
select * from dictionary
•• Consulta Oracle SQL para conocer Muestra los datos de una tabla especificada (en este caso todas las tablas que lleven la cadena "XXX"
select * from ALL_ALL_TABLES where upper(table_name) like '%XXX%'
•• Consulta Oracle SQL para conocer Tablas propiedad del usuario actual
select * from user_tables
•• Consulta Oracle SQL para conocer Todos los objetos propiedad del usuario conectado a Oracle
select * from user_catalog
•• Consulta Oracle SQL para conocer Consulta SQL para el DBA de Oracle que muestra los tablespaces, el espacio utilizado, el espacio libre y los ficheros de datos de los mismos:
Select t.tablespace_name "Tablespace", t.status "Estado",
ROUND(MAX(d.bytes)/1024/1024,2) "MB Tamaño",
ROUND((MAX(d.bytes)/1024/1024) -
(SUM(decode(f.bytes, NULL,0, f.bytes))/1024/1024),2) "MB Usados",
ROUND(SUM(decode(f.bytes, NULL,0, f.bytes))/1024/1024,2) "MB Libres",
t.pct_increase "% incremento",
SUBSTR(d.file_name,1,80) "Fichero de datos"
FROM DBA_FREE_SPACE f, DBA_DATA_FILES d, DBA_TABLESPACES t
WHERE t.tablespace_name = d.tablespace_name AND
f.tablespace_name(+) = d.tablespace_name
AND f.file_id(+) = d.file_id GROUP BY t.tablespace_name,
d.file_name, t.pct_increase, t.status ORDER BY 1,3 DESC
•• Consulta Oracle SQL para conocer Productos Oracle instalados y la versión:
select * from product_component_version
•• Consulta Oracle SQL para conocer Roles y privilegios por roles:
select * from role_sys_privs
•• Consulta Oracle SQL para conocer Reglas de integridad y columna a la que afectan:
select constraint_name, column_name from sys.all_cons_columns
•• Consulta Oracle SQL para conocer Tablas de las que es propietario un usuario, en este caso "xxx":
SELECT table_owner, table_name from sys.all_synonyms where table_owner like 'xxx'
•• Consulta Oracle SQL para conocer Otra forma más efectiva (tablas de las que es propietario un usuario):
SELECT DISTINCT TABLE_NAME
FROM ALL_ALL_TABLES
WHERE OWNER LIKE 'HR'
Parámetros de Oracle, valor actual y su descripción:
SELECT v.name, v.value value, decode(ISSYS_MODIFIABLE, 'DEFERRED',
'TRUE', 'FALSE') ISSYS_MODIFIABLE, decode(v.isDefault, 'TRUE', 'YES',
'FALSE', 'NO') "DEFAULT", DECODE(ISSES_MODIFIABLE, 'IMMEDIATE',
'YES','FALSE', 'NO', 'DEFERRED', 'NO', 'YES') SES_MODIFIABLE,
DECODE(ISSYS_MODIFIABLE, 'IMMEDIATE', 'YES', 'FALSE', 'NO',
'DEFERRED', 'YES','YES') SYS_MODIFIABLE , v.description
FROM V$PARAMETER v
WHERE name not like 'nls%' ORDER BY 1
•• Consulta Oracle SQL para conocer Usuarios de Oracle y todos sus datos (fecha de creación, estado, id, nombre, tablespace temporal,...):
Select * FROM dba_users
•• Consulta Oracle SQL para conocer Tablespaces y propietarios de los mismos:
select owner, decode(partition_name, null, segment_name,
segment_name || ':' || partition_name) name,
segment_type, tablespace_name,bytes,initial_extent,
next_extent, PCT_INCREASE, extents, max_extents
from dba_segments
Where 1=1 And extents > 1 order by 9 desc, 3
Últimas consultas SQL ejecutadas en Oracle y usuario que las ejecutó:
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
•• Consulta Oracle SQL para conocer todos los Tablespaces:
select * from V$TABLESPACE
•• Consulta Oracle SQL para conocer Memoria Share_Pool libre y usada
select name,to_number(value) bytes
from v$parameter where name ='shared_pool_size'
union all
select name,bytes
from v$sgastat where pool = 'shared pool' and name = 'free memory'
Cursores abiertos por usuario
select b.sid, a.username, b.value Cursores_Abiertos
from v$session a,
v$sesstat b,
v$statname c
where c.name in ('opened cursors current')
and b.statistic# = c.statistic#
and a.sid = b.sid
and a.username is not null
and b.value >0
order by 3
•• Consulta Oracle SQL para conocer Aciertos de la caché (no debería superar el 1 por ciento)
select sum(pins) Ejecuciones, sum(reloads) Fallos_cache,
trunc(sum(reloads)/sum(pins)*100,2) Porcentaje_aciertos
from v$librarycache
where namespace in ('TABLE/PROCEDURE','SQL AREA','BODY','TRIGGER');
Sentencias SQL completas ejecutadas con un texto determinado en el SQL
SELECT c.sid, d.piece, c.serial#, c.username, d.sql_text
FROM v$session c, v$sqltext d
WHERE c.sql_hash_value = d.hash_value
and upper(d.sql_text) like '%WHERE CAMPO LIKE%'
ORDER BY c.sid, d.piece
Una sentencia SQL concreta (filtrado por sid)
SELECT c.sid, d.piece, c.serial#, c.username, d.sql_text
FROM v$session c, v$sqltext d
WHERE c.sql_hash_value = d.hash_value
and sid = 105
ORDER BY c.sid, d.piece
•• Consulta Oracle SQL para conocer Tamaño ocupado por la base de datos
select sum(BYTES)/1024/1024 MB from DBA_EXTENTS
•• Consulta Oracle SQL para conocer Tamaño de los ficheros de datos de la base de datos
select sum(bytes)/1024/1024 MB from dba_data_files
•• Consulta Oracle SQL para conocer Tamaño ocupado por una tabla concreta sin incluir los índices de la misma
select sum(bytes)/1024/1024 MB from user_segments
where segment_type='TABLE' and segment_name='NOMBRETABLA'
•• Consulta Oracle SQL para conocer Tamaño ocupado por una tabla concreta incluyendo los índices de la misma
select sum(bytes)/1024/1024 Table_Allocation_MB from user_segments
where segment_type in ('TABLE','INDEX') and
(segment_name='NOMBRETABLA' or segment_name in
(select index_name from user_indexes where table_name='NOMBRETABLA'))
•• Consulta Oracle SQL para conocer Tamaño ocupado por una columna de una tabla
select sum(vsize('NOMBRECOLUMNA'))/1024/1024 MB from NOMBRETABLA
•• Consulta Oracle SQL para conocer Espacio ocupado por usuario
SELECT owner, SUM(BYTES)/1024/1024 FROM DBA_EXTENTS MB
group by owner
•• Consulta Oracle SQL para conocer Espacio ocupado por los diferentes segmentos (tablas, índices, undo, rollback, cluster, ...)
SELECT SEGMENT_TYPE, SUM(BYTES)/1024/1024 FROM DBA_EXTENTS MB
group by SEGMENT_TYPE
•• Consulta Oracle SQL para conocer Obtener todas las funciones de Oracle: NVL, ABS, LTRIM, ...
SELECT distinct object_name
FROM all_arguments
WHERE package_name = 'STANDARD'
order by object_name
•• Consulta Oracle SQL para conocer Espacio ocupado por todos los objetos de la base de datos, muestra los objetos que más ocupan primero
SELECT SEGMENT_NAME, SUM(BYTES)/1024/1024 FROM DBA_EXTENTS MB
group by SEGMENT_NAME
order by 2 desc
select * from v$instance
•• Consulta Oracle SQL para conocer Consulta que muestra si la base de datos está abierta
select status from v$instance
•• Consulta Oracle SQL para conocer Vista que muestra los parámetros generales de Oracle
select * from v$system_parameter
•• Consulta Oracle SQL para conocer Versión de Oracle
select value from v$system_parameter where name = 'compatible'
•• Consulta Oracle SQL para conocer Ubicación y nombre del fichero spfile
select value from v$system_parameter where name = 'spfile'
•• Consulta Oracle SQL para conocer Ubicación y número de ficheros de control
select value from v$system_parameter where name = 'control_files'
•• Consulta Oracle SQL para conocer Nombre de la base de datos
select value from v$system_parameter where name = 'db_name'
•• Consulta Oracle SQL para conocer Vista que muestra las conexiones actuales a Oracle Para visualizarla es necesario entrar con privilegios de administrador
select osuser, username, machine, program
from v$session
order by osuser
•• Consulta Oracle SQL para conocer Vista que muestra el número de conexiones actuales a Oracle agrupado por aplicación que realiza la conexión
select program Aplicacion, count(program) Numero_Sesiones
from v$session
group by program
order by Numero_Sesiones desc
•• Consulta Oracle SQL para conocer Vista que muestra los usuarios de Oracle conectados y el número de sesiones por usuario
select username Usuario_Oracle, count(username) Numero_Sesiones
from v$session
group by username
order by Numero_Sesiones desc
Propietarios de objetos y número de objetos por propietario
select owner, count(owner) Numero
from dba_objects
group by owner
order by Numero desc
•• Consulta Oracle SQL para conocer Diccionario de datos (incluye todas las vistas y tablas de la Base de Datos)
select * from dictionary
•• Consulta Oracle SQL para conocer Muestra los datos de una tabla especificada (en este caso todas las tablas que lleven la cadena "XXX"
select * from ALL_ALL_TABLES where upper(table_name) like '%XXX%'
•• Consulta Oracle SQL para conocer Tablas propiedad del usuario actual
select * from user_tables
•• Consulta Oracle SQL para conocer Todos los objetos propiedad del usuario conectado a Oracle
select * from user_catalog
•• Consulta Oracle SQL para conocer Consulta SQL para el DBA de Oracle que muestra los tablespaces, el espacio utilizado, el espacio libre y los ficheros de datos de los mismos:
Select t.tablespace_name "Tablespace", t.status "Estado",
ROUND(MAX(d.bytes)/1024/1024,2) "MB Tamaño",
ROUND((MAX(d.bytes)/1024/1024) -
(SUM(decode(f.bytes, NULL,0, f.bytes))/1024/1024),2) "MB Usados",
ROUND(SUM(decode(f.bytes, NULL,0, f.bytes))/1024/1024,2) "MB Libres",
t.pct_increase "% incremento",
SUBSTR(d.file_name,1,80) "Fichero de datos"
FROM DBA_FREE_SPACE f, DBA_DATA_FILES d, DBA_TABLESPACES t
WHERE t.tablespace_name = d.tablespace_name AND
f.tablespace_name(+) = d.tablespace_name
AND f.file_id(+) = d.file_id GROUP BY t.tablespace_name,
d.file_name, t.pct_increase, t.status ORDER BY 1,3 DESC
•• Consulta Oracle SQL para conocer Productos Oracle instalados y la versión:
select * from product_component_version
•• Consulta Oracle SQL para conocer Roles y privilegios por roles:
select * from role_sys_privs
•• Consulta Oracle SQL para conocer Reglas de integridad y columna a la que afectan:
select constraint_name, column_name from sys.all_cons_columns
•• Consulta Oracle SQL para conocer Tablas de las que es propietario un usuario, en este caso "xxx":
SELECT table_owner, table_name from sys.all_synonyms where table_owner like 'xxx'
•• Consulta Oracle SQL para conocer Otra forma más efectiva (tablas de las que es propietario un usuario):
SELECT DISTINCT TABLE_NAME
FROM ALL_ALL_TABLES
WHERE OWNER LIKE 'HR'
Parámetros de Oracle, valor actual y su descripción:
SELECT v.name, v.value value, decode(ISSYS_MODIFIABLE, 'DEFERRED',
'TRUE', 'FALSE') ISSYS_MODIFIABLE, decode(v.isDefault, 'TRUE', 'YES',
'FALSE', 'NO') "DEFAULT", DECODE(ISSES_MODIFIABLE, 'IMMEDIATE',
'YES','FALSE', 'NO', 'DEFERRED', 'NO', 'YES') SES_MODIFIABLE,
DECODE(ISSYS_MODIFIABLE, 'IMMEDIATE', 'YES', 'FALSE', 'NO',
'DEFERRED', 'YES','YES') SYS_MODIFIABLE , v.description
FROM V$PARAMETER v
WHERE name not like 'nls%' ORDER BY 1
•• Consulta Oracle SQL para conocer Usuarios de Oracle y todos sus datos (fecha de creación, estado, id, nombre, tablespace temporal,...):
Select * FROM dba_users
•• Consulta Oracle SQL para conocer Tablespaces y propietarios de los mismos:
select owner, decode(partition_name, null, segment_name,
segment_name || ':' || partition_name) name,
segment_type, tablespace_name,bytes,initial_extent,
next_extent, PCT_INCREASE, extents, max_extents
from dba_segments
Where 1=1 And extents > 1 order by 9 desc, 3
Últimas consultas SQL ejecutadas en Oracle y usuario que las ejecutó:
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
•• Consulta Oracle SQL para conocer todos los Tablespaces:
select * from V$TABLESPACE
•• Consulta Oracle SQL para conocer Memoria Share_Pool libre y usada
select name,to_number(value) bytes
from v$parameter where name ='shared_pool_size'
union all
select name,bytes
from v$sgastat where pool = 'shared pool' and name = 'free memory'
Cursores abiertos por usuario
select b.sid, a.username, b.value Cursores_Abiertos
from v$session a,
v$sesstat b,
v$statname c
where c.name in ('opened cursors current')
and b.statistic# = c.statistic#
and a.sid = b.sid
and a.username is not null
and b.value >0
order by 3
•• Consulta Oracle SQL para conocer Aciertos de la caché (no debería superar el 1 por ciento)
select sum(pins) Ejecuciones, sum(reloads) Fallos_cache,
trunc(sum(reloads)/sum(pins)*100,2) Porcentaje_aciertos
from v$librarycache
where namespace in ('TABLE/PROCEDURE','SQL AREA','BODY','TRIGGER');
Sentencias SQL completas ejecutadas con un texto determinado en el SQL
SELECT c.sid, d.piece, c.serial#, c.username, d.sql_text
FROM v$session c, v$sqltext d
WHERE c.sql_hash_value = d.hash_value
and upper(d.sql_text) like '%WHERE CAMPO LIKE%'
ORDER BY c.sid, d.piece
Una sentencia SQL concreta (filtrado por sid)
SELECT c.sid, d.piece, c.serial#, c.username, d.sql_text
FROM v$session c, v$sqltext d
WHERE c.sql_hash_value = d.hash_value
and sid = 105
ORDER BY c.sid, d.piece
•• Consulta Oracle SQL para conocer Tamaño ocupado por la base de datos
select sum(BYTES)/1024/1024 MB from DBA_EXTENTS
•• Consulta Oracle SQL para conocer Tamaño de los ficheros de datos de la base de datos
select sum(bytes)/1024/1024 MB from dba_data_files
•• Consulta Oracle SQL para conocer Tamaño ocupado por una tabla concreta sin incluir los índices de la misma
select sum(bytes)/1024/1024 MB from user_segments
where segment_type='TABLE' and segment_name='NOMBRETABLA'
•• Consulta Oracle SQL para conocer Tamaño ocupado por una tabla concreta incluyendo los índices de la misma
select sum(bytes)/1024/1024 Table_Allocation_MB from user_segments
where segment_type in ('TABLE','INDEX') and
(segment_name='NOMBRETABLA' or segment_name in
(select index_name from user_indexes where table_name='NOMBRETABLA'))
•• Consulta Oracle SQL para conocer Tamaño ocupado por una columna de una tabla
select sum(vsize('NOMBRECOLUMNA'))/1024/1024 MB from NOMBRETABLA
•• Consulta Oracle SQL para conocer Espacio ocupado por usuario
SELECT owner, SUM(BYTES)/1024/1024 FROM DBA_EXTENTS MB
group by owner
•• Consulta Oracle SQL para conocer Espacio ocupado por los diferentes segmentos (tablas, índices, undo, rollback, cluster, ...)
SELECT SEGMENT_TYPE, SUM(BYTES)/1024/1024 FROM DBA_EXTENTS MB
group by SEGMENT_TYPE
•• Consulta Oracle SQL para conocer Obtener todas las funciones de Oracle: NVL, ABS, LTRIM, ...
SELECT distinct object_name
FROM all_arguments
WHERE package_name = 'STANDARD'
order by object_name
•• Consulta Oracle SQL para conocer Espacio ocupado por todos los objetos de la base de datos, muestra los objetos que más ocupan primero
SELECT SEGMENT_NAME, SUM(BYTES)/1024/1024 FROM DBA_EXTENTS MB
group by SEGMENT_NAME
order by 2 desc
Diccionario de datos
Diccionario de datos
1.- Introducción
Un diccionario de datos es un conjunto de metadatos que contiene las características lógicas de los datos que se van a utilizar en el sistema que se programa, incluyendo nombre, descripción, alias, contenido y organización.
Estos diccionarios se desarrollan durante el análisis de flujo de datos y ayuda a los analistas que participan en la determinación de los requerimientos del sistema, su contenido también se emplea durante el diseño del proyecto.
2.-Diccionario de la base de datos
En base de datos se utiliza para llevar un recuento detallado de todas las tablas dentro de de la base de datos que han sido creadas por el usuario, por el diseñador o por ambos.
Este contiene todos los nombres y características de atributo de cada una de las tablas en el sistema.
En ocasiones, el diccionario de datos se describe como “la base de datos del diseñador de la base de datos” ya que registra las decisiones de diseño sobre las tablas y sus estructuras.
Bibliografia
Suscribirse a:
Entradas (Atom)