PLSQL Básico2

Más ejemplos sobre PLSQL, en este post veamos como podemos crear procedimientos que utilicen arrays,collections y estructuras de datos derivadas de estos

Ejemplo 1: Muestra el uso de arrays asociativos
Definimos un tipo de la forma: TYPE miHashType IS TABLE OF NUMBER INDEX BY VARCHAR2(6);
La clausula TYPE especifica que vamos a definir un nuevo tipo.
El código miHashType es el nombre del nuevo tipo, es como una nueva clase
La clausula IS TABLE OF NUMBER indica que el arreglo asociativo va a tener valores de tipo NUMBER
La clausula BY VARCHAR2(5) indica que los valores de los indices van a ser del tipo VARCHAR2(6)

CREATE OR REPLACE PROCEDURE collection01 IS 

TYPE miHashType IS TABLE OF NUMBER INDEX BY VARCHAR2(6);
arreglo1 miHashType;
arreglo2 miHashType;

TYPE miEmployeeType IS TABLE OF employees%rowtype INDEX BY PLS_INTEGER;
empleadosArray miEmployeeType;

-- Los tipos anidados pueden almacenar un número ilimitado de elementos
TYPE miTableType IS TABLE OF VARCHAR2(20);
arregloV1 miTableType;
arregloV2 miTableType;

-- Los tipos varray tienen un número máximo de elementos
TYPE miArregloType IS VARRAY(4) OF VARCHAR2(10);
varreglo1 miArregloType;
varreglo2 miArregloType;

BEGIN
  -- Agregamos 3 entradas al primer arreglo:
  arreglo1('clave1') := 666;
  arreglo1('clave2') := 667;
  arreglo1('clave3') := 668;
  
  -- Agregamos 3 entradas al primer arreglo:
  arreglo2('clave1') := 100;
  arreglo2('clave2') := 101;
  arreglo2('clave3') := 102;
  arreglo2('clave4') := 103;
  arreglo2('clave5') := 104;
  arreglo2('clave6') := 105;
  
  -- Ahora mostramos los valores deacuerdo a su clave:
  dbms_output.put_line('El valor del arreglo asociativo arreglo2(''clave2''):'||arreglo2('clave2'));
  
  -- Obtenemos los datos de un empleado:
  SELECT * INTO empleadosArray(100) FROM employees WHERE employee_id = 100;
  dbms_output.put_line('Nombre del empleado: '||empleadosArray(100).first_name);
  dbms_output.put_line('Apellidos del empleado: '||empleadosArray(100).last_name);
  dbms_output.put_line('Email del empleado: '||empleadosArray(100).email);
  
  -- Ahora asignamos los valores a los arreglos:
  arregloV1 := miTableType('carlos','alberto','cacique','yupanqui','certificado','en','java','y','oracle');
  arregloV2 := miTableType('zaida','angelica');
  varreglo1 := miArregloType('uno','dos');
  -- Con el metodo EXTEND extendemos el arreglo dinamico, el primer argument 
  -- especifica cuantos elementos más creamos y el segundo el valor que se va a 
  -- copiar de los elementos existentes a los elementos nuevos
  varreglo1.EXTEND(2,1);
  --varreglo1(3) := 'tres';
  --varreglo1(4) := 'cuatro';
  varreglo2 := miArregloType('primero','segundo','tercero','cuarto');
  
  -- Ahora mostramos los valores de los tipos anidados;
  FOR i IN arregloV1.FIRST..arregloV1.LAST LOOP
   dbms_output.put_line('arregloV1('||i||'):'||arregloV1(i));
  END LOOP;
  FOR i IN arregloV2.FIRST..arregloV2.LAST LOOP
     dbms_output.put_line('arregloV2('||i||'):'||arregloV2(i));
  END LOOP;
  
  
  -- Ahora mostramos los valores de los tipos varrays
  FOR i IN varreglo1.FIRST..varreglo1.LAST LOOP
     dbms_output.put_line('varreglo1('||i||'):'||varreglo1(i));
  END LOOP;
  FOR i IN varreglo2.FIRST..varreglo2.LAST LOOP
       dbms_output.put_line('varreglo2('||i||'):'||varreglo2(i));
  END LOOP;
  
END collection01;

Ejemplo 2:
Ahora veremos como declarar un tipo complejo con TYPE... IS RECORD, despues creamos un tipo anidado con el tipo creado anteriormente, una vez creado el tipo anidado, creamos una variable del tipo anidado, todo esto para poder mantener los datos que más tarde nos retornará un cursor.
Después declaramos un tipo cursor y creamos una variable, más adelante en el cuerpo del procedimiento abrimos el cursor para un SELECT específico y cargamos la variable coleccion con los datos del cursor por medio de la clausula FETCH <cursor> BULK COLLECTION INTO <coleccion>, al final del procedimiento solo mostramos los datos pero desde la colección.

CREATE OR REPLACE PROCEDURE collection02 IS

  -- Declaramos un tipo de dato complejo
  TYPE emp_name_rec IS RECORD (
    firstname    employees.first_name%TYPE,
    lastname     employees.last_name%TYPE,
    hiredate     employees.hire_date%TYPE
  );
    
  -- Tipo Tabla que puede mantener información acerca de los empleados
  TYPE EmpList_tab IS TABLE OF emp_name_rec;
  SeniorSalespeople EmpList_tab;   
   
  -- Declaramos una referencia a un cursor para seleccionar un subconjunto de columnas.
  EndCounter NUMBER := 10;
  TYPE EmpCurTyp IS REF CURSOR;
  emp_cv EmpCurTyp; 
   
BEGIN
  -- Abrimos el cursor con la sentencia SELECT indicada
  OPEN emp_cv FOR SELECT first_name, last_name, hire_date FROM employees 
  WHERE job_id = 'SA_REP' ORDER BY hire_date;

  -- Sacamos los datos del cursor y los colocamos dentro de la coleccion
  FETCH emp_cv BULK COLLECT INTO SeniorSalespeople;
  
  -- Cerramos el cursor
  CLOSE emp_cv;

  -- Para este ejemplo mostramos un máximo de 10 empleados
  IF SeniorSalespeople.LAST > 0 THEN
    IF SeniorSalespeople.LAST < 10 THEN EndCounter := SeniorSalespeople.LAST; 
    END IF;
    FOR i in 1..EndCounter LOOP
      DBMS_OUTPUT.PUT_LINE(SeniorSalespeople(i).lastname || ', ' 
      || SeniorSalespeople(i).firstname || ', ' || SeniorSalespeople(i).hiredate);
    END LOOP;
  END IF;
END collection02;

Ejemplo 3: En este ejemplo se muestra la eficiencia de la clausula FORALL a diferencia de la clausula FOR LOOP con tipos anidados:

CREATE OR REPLACE PROCEDURE COLLECTION03 IS
  -- Definimos des tipos de datos anidados
  TYPE NumTab IS TABLE OF parts1.pnum%TYPE INDEX BY PLS_INTEGER;
  TYPE NameTab IS TABLE OF parts1.pname%TYPE INDEX BY PLS_INTEGER;
  -- Declaramos variables de los tipos definidos anteriormente
  pnums  NumTab;
  pnames NameTab;
  -- Cuantas veces vamos a iterar
  iterations CONSTANT PLS_INTEGER := 10000;
  -- Variables que nos van a ayudar a medir el tiempo
  t1 INTEGER;
  t2 INTEGER;
  t3 INTEGER;
BEGIN
  FOR j IN 1..iterations LOOP  -- load index-by tables
     pnums(j) := j;
     pnames(j) := 'Part No. ' || TO_CHAR(j);
  END LOOP;
  t1 := DBMS_UTILITY.get_time;
  FOR i IN 1..iterations LOOP  -- use FOR loop
     INSERT INTO parts1 VALUES (pnums(i), pnames(i));
  END LOOP;
  t2 := DBMS_UTILITY.get_time;
  FORALL i IN 1..iterations  -- use FORALL statement
     INSERT INTO parts2 VALUES (pnums(i), pnames(i));
  t3 := DBMS_UTILITY.get_time;
  DBMS_OUTPUT.PUT_LINE('Tiempo de ejecución en segundos');
  DBMS_OUTPUT.PUT_LINE('---------------------');
  DBMS_OUTPUT.PUT_LINE('FOR LOOP: ' || TO_CHAR((t2 - t1)));
  DBMS_OUTPUT.PUT_LINE('FORALL:   ' || TO_CHAR((t3 - t2)));
  COMMIT;
END COLLECTION03;

Ejemplo 4: Ejemplo de uso de operaciones de comparacion con operadores SET:

CREATE OR REPLACE PROCEDURE COLLECTION04 IS

  TYPE nested_typ IS TABLE OF NUMBER;
    nt1 nested_typ := nested_typ(1,2,3);
    nt2 nested_typ := nested_typ(3,2,1);
    nt3 nested_typ := nested_typ(2,3,1,3);
    nt4 nested_typ := nested_typ(1,2,4);
    respuesta BOOLEAN;
    cantidad NUMBER;
    
    -- Definimos un procedimiento que mostrará una cadena de caracteres y una cantidad
    PROCEDURE imprimir(truth BOOLEAN DEFAULT NULL, quantity NUMBER DEFAULT NULL) IS
    BEGIN
      IF truth IS NOT NULL THEN
        DBMS_OUTPUT.PUT_LINE(CASE truth WHEN TRUE THEN 'True' WHEN FALSE THEN 'False' END);
      END IF;
      IF quantity IS NOT NULL THEN
          DBMS_OUTPUT.PUT_LINE(quantity);
      END IF;
    END;
  BEGIN
    -- La variable anidada nt1 se encuentra en el subconjunto especificado por IN?
    -- Sí, nt1 coincide con nt2 que se encuentra en la clausula IN
    respuesta := nt1 IN (nt2,nt3,nt4); 
    imprimir(truth => respuesta);
    
    -- La variable anidada nt1 es un subconjunto de la variable nt3
    -- Sí, todos los elementos coinciden
    respuesta := nt1 SUBMULTISET OF nt3; 
    imprimir(truth => respuesta);
    
    -- La variable anidada no es un subconjunto de la variable anidada nt4?
    -- Sí, la variable NO lo es.
    respuesta := nt1 NOT SUBMULTISET OF nt4;
    imprimir(truth => respuesta);
    
    -- CARDINALITY retorna el numero de elementos de la variable anidada nt3
    cantidad := CARDINALITY(nt3);
    imprimir(quantity => cantidad);
    
    -- CARDINALITY(SET(?)) muestra el número de elementos distintos en la variable
    cantidad := CARDINALITY(SET(nt3));
    imprimir(quantity => cantidad);
    
    -- El elemento con valor 4 es un miembro de la variable nt1?
    -- No, ese elemento no existe en la variable anidada.
    respuesta := 4 MEMBER OF nt1;
    imprimir(truth => respuesta);
    
    -- La variable nt3 es un SET, es decir no tiene duplicados?
    -- NO, la variable tiene duplicados, por lo tanto no es un SET
    respuesta := nt3 IS A SET;
    imprimir(truth => respuesta);
    
    -- La variable nt3 no es un SET?
    -- Sí dicha variable no es un SET porque contiene elementos duplicados.
    respuesta := nt3 IS NOT A SET;
    imprimir(truth => respuesta);
    
    -- La variable nt1 no tiene elementos?
    -- falso, dicha variable si tiene elementos.
    respuesta := nt1 IS EMPTY;
    imprimir(truth => respuesta);
   
END COLLECTION05;

Ejemplo 5: Operaciones con los diferentes métodos implícitos de la colecciones.

• lista.EXIST(n), lista, es una variable de tipo anidada o varray y n es el indice de un elemento en la lista
  
• lista.COUNT, retorna el número de elementos que la lista actualmente contiene.
  
• lista.LIMIT, retorna el tamaño máximo de la tabla anidada.
  
• lista.TRIM, remueve un elemento del final de la lista, si le especificasmo un argumentoentonces removerá la cantidad especificada.
  

CREATE OR REPLACE PROCEDURE COLLECTION05 IS

  -- Definimos un tipo anidado de enteros
  TYPE listaNumerosType IS TABLE OF INTEGER;
  lista listaNumerosType := listaNumerosType(1,3,5,7);
   
  BEGIN
   dbms_output.put_line('Cantidad de elementos de la lista 1:'||lista.COUNT);
   -- Borramos el segundo elemento:
   lista.DELETE(2); 
   dbms_output.put_line('Cantidad de elementos de la lista 2:'||lista.COUNT);
   
   -- Si en la lista existe el elemento con indice 1 entonces hacer:
   IF lista.EXISTS(1) THEN
      DBMS_OUTPUT.PUT_LINE('El element #1 existe.');
   END IF;
   -- Si en la lista no existe el elemento con indice 2 entonces hacer
   IF lista.EXISTS(2) = FALSE THEN
      DBMS_OUTPUT.PUT_LINE('El elemento #2 ha sido borrado.');
   END IF;
   
   -- Agregamos un par de elementos más a la lista y volvemos a hacer COUNT
   lista.EXTEND(5);
   lista(5) := 9;
   dbms_output.put_line('Cantidad de elementos de la lista 3:'||lista.COUNT);
   dbms_output.put_line('Limite de elementos de la lista :'||lista.LIMIT);
   -- Removera 2 elementos del final de la coleccion, ojo que TRIM solo removerá uno
   lista.TRIM(2);
   dbms_output.put_line('Cantidad de elementos de la lista despues de TRIM:'||lista.COUNT);
   dbms_output.put_line('Limite de elementos de la lista despues de TRIM:'||lista.LIMIT);
   
   -- Usando los metodos first, prior, next y last
   dbms_output.put_line('lista.FIRST:'||lista.FIRST);
   dbms_output.put_line('lista.LAST:'||lista.LAST);
   dbms_output.put_line('lista.PRIOR(2):'||lista.PRIOR(2));
   dbms_output.put_line('lista.NEXT(2):'||lista.NEXT(2));
   
END COLLECTION05;

PLSQL Básico1

Encontré varios ejemplos de hace varios años cuando aprendía plsql, allí les dejo algunos ejemplitos, ojalá les sirvan:

Ejemplo 1: Lo unico que hace es mostrar una linea en consola.

CREATE OR REPLACE PROCEDURE proc01 IS
BEGIN
  dbms_output.put_line('Hola mundo con los procedimientos de Oracle');
END;

Ejemplo 2: Igual que el anterior pero ahora la ultima línea finaliza con el nombre del procedimiento... buena practica!

CREATE OR REPLACE PROCEDURE proc02 IS
BEGIN
  dbms_output.put_line('Otro procedimiento pero ahora con otro texto');
END proc02;

Ejemplo 3: Uso de la clausula %TYPE y %ROWTYPE, la clausula %TYPE sirve para asignar los tipos de datos heredados de otras variables en tiempo de ejecución lo mismo para %ROWTYPE que actua a nivel de fila

CREATE OR REPLACE PROCEDURE proc03 IS
  v_fecha_sistema DATE;
  v_fecha_contratacion v_fecha_sistema%TYPE;
  v_salario employees.salary%TYPE;
  reg_empleados employees%ROWTYPE; 
BEGIN
  SELECT sysdate INTO v_fecha_sistema FROM dual;
  SELECT * INTO reg_empleados FROM employees WHERE employee_id=200;
  v_salario := reg_empleados.salary;
  v_fecha_contratacion := reg_empleados.hire_date;
  dbms_output.put_line('El nombre del empleado es:'||reg_empleados.first_name);
  dbms_output.put_line('El salario del empleado es:'||v_salario);
  dbms_output.put_line('La fecha de contratación del empleado es:'||v_fecha_contratacion);
  dbms_output.put_line('La fecha del sistema es:'||v_fecha_sistema);
END proc03;

Ejemplo 4: Muestra el uso de etiquetas para resolucion de nombres de variables, analiza el ámbito de la variable v_cantidad

CREATE OR REPLACE PROCEDURE proc04 IS
BEGIN
  <<ambito_externo>>
  DECLARE 
  v_cantidad NUMBER := 100;
  BEGIN
    DECLARE
    v_cantidad NUMBER := 150;
    BEGIN
    -- Inicio de un bloque de codigo interno
    dbms_output.put_line('Valor de la variable interna:'||v_cantidad);
    dbms_output.put_line('Valor de la variable externa:'||ambito_externo.v_cantidad);
    END;
    -- Aqui podemos continuar con los datos de la variable etiquetada externamente
    dbms_output.put_line('Valor de la variable:'||v_cantidad);
  END;
END proc04;

Ejemplo 5: Podemos crear funciones dentro del procedimiento y usarlas en el mismo procedimiento.

CREATE OR REPLACE PROCEDURE proc05 IS
  
  -- Funcion sumar:
  FUNCTION sumar(ope1 IN NUMBER,ope2 IN NUMBER) RETURN NUMBER IS
  BEGIN
    RETURN ope1+ope2;
  END sumar;
  
  -- Funcion restar:
  FUNCTION restar(ope1 IN NUMBER,ope2 IN NUMBER) RETURN NUMBER IS
  BEGIN
    RETURN ope1-ope2;
  END restar;
  
  -- Funcion multiplicar:
  FUNCTION multiplicar(ope1 IN NUMBER,ope2 IN NUMBER) RETURN NUMBER IS
  BEGIN
    RETURN ope1*ope2;
  END multiplicar;
  
  -- Funcion dividir:
  FUNCTION dividir(ope1 IN NUMBER,ope2 IN NUMBER) RETURN NUMBER IS
  BEGIN
    RETURN ope1/ope2;
  END dividir;
  
BEGIN
  dbms_output.put_line('Suma de 15 y 45: '||sumar(15,45));
  dbms_output.put_line('Resta de 15 y 45: '||restar(15,45));
  dbms_output.put_line('Multiplicacion de 15 y 45: '||multiplicar(15,45));
  dbms_output.put_line('Division de 15 y 45: '||dividir(15,45));
END proc05;

Ejemplo 6: Muestra el uso de las estructuras de control condicional IF-THEN e IF-THEN-ELSE

CREATE OR REPLACE
PROCEDURE proc06(vi_cantidad_minima IN INTEGER) IS
  v_sueldo_base NUMBER := 4000;
  v_cantidad_empleados INTEGER := 0;
BEGIN
  --Obtenemos la cantidad de empleados que tienen un sueldo mayor al sueldo base
  SELECT COUNT(employee_id) INTO v_cantidad_empleados FROM employees 
  WHERE salary > v_sueldo_base;
  
  -- Testeamos si la cantidad de empleados es mayor a una constante
  dbms_output.put_line('La cantidad de empleados es:'||v_cantidad_empleados); 
  dbms_output.put_line('-----------------------------------------------------'); 
  dbms_output.put_line('Uso de las estructura if-then'); 
  IF v_cantidad_empleados > vi_cantidad_minima THEN
    dbms_output.put_line('La cantidad de empleados:'||v_cantidad_empleados||', ES MAYOR a la minima');  
  END IF;
  
  dbms_output.put_line('Uso de las estructura if-then-else'); 
  IF v_cantidad_empleados > vi_cantidad_minima THEN
    dbms_output.put_line('La cantidad de empleados:'||v_cantidad_empleados||', ES MAYOR a la minima');  
  ELSE
    dbms_output.put_line('La cantidad de empleados:'||v_cantidad_empleados||', NO ES MAYOR a la minima');  
  END IF;
  
END proc06;

Ejemplo 7: Muestra el uso de las estructuras de control iterativas LOOP y EXIT

CREATE OR REPLACE
PROCEDURE proc07(vi_cantidad_iteraciones IN INTEGER) IS
  v_contador1 INTEGER := 0;
  v_contador2 INTEGER := 0;
BEGIN
  -- Primero con una declaración EXIT para salir de un bucle.
  dbms_output.put_line('Iteracion con clausula EXIT');
  LOOP
    IF v_contador1 = vi_cantidad_iteraciones THEN
      EXIT;
    ELSE
      dbms_output.put_line('Iterando con EXIT');    
    END IF;
    v_contador1 := v_contador1 + 1;
  END LOOP;
  
  --Ahora iteramos pero con la clausula EXIT WHEN
  v_contador1 := 0;
  dbms_output.put_line('Iteracion con la clausula EXIT WHEN');
  LOOP
  v_contador1 := v_contador1 + 1;
  dbms_output.put_line('Iterando con EXIT WHEN');    
    EXIT WHEN v_contador1 >= 20;
  END LOOP;
  
  --Ahora iteramos con la clausula EXIT WHEN con etiquetas
  v_contador1 := 0;
  v_contador2 := 0;
  dbms_output.put_line('Iteracion con la clausula EXIT WHEN CON ETIQUETAS');
  <<externo>>
  LOOP
    v_contador1 := v_contador1 + 1;
    dbms_output.put_line('Iterando con EXIT WHEN');    
    v_contador2 := 0;
    <<interno>>
    LOOP
     v_contador2 := v_contador2 + 1;
 dbms_output.put_line('Iterando con EXIT WHEN ANIDADO');
        EXIT externo WHEN (v_contador1 = 2 AND v_contador2 = 3);  
        EXIT interno WHEN v_contador2 >= 3;
    END LOOP;
    EXIT externo WHEN v_contador1 >= 3;
  END LOOP;
    
END proc07;

Ejemplo 8: Muestra el uso de estructuras de iteracion WHILE-LOOP-END LOOP con EXIT y etiquetas

CREATE OR REPLACE
PROCEDURE proc08(vi_cantidad_iteraciones IN INTEGER) IS
  v_contador1 INTEGER := 0;
  v_contador2 INTEGER := 0;
  v_contador3 INTEGER := 0;
BEGIN  
  --Ahora iteramos con la clausula EXIT WHEN con etiquetas
  v_contador1 := 0;
  v_contador2 := 0;
  dbms_output.put_line('Iteracion con la clausula EXIT WHEN CON ETIQUETAS');
  <<externo>>
  LOOP
    v_contador1 := v_contador1 + 1;
    dbms_output.put_line('Iterando con EXIT WHEN');    
    v_contador2 := 0;
    <<interno>>
    LOOP
     v_contador2 := v_contador2 + 1;
 dbms_output.put_line('Iterando con EXIT WHEN ANIDADO'); 
 v_contador3 := 0;
 WHILE (v_contador3 < 5 ) LOOP
   v_contador3 := v_contador3 + 1;
   dbms_output.put_line('Iterando con EXIT WHEN ANIDADO CON WHILE-LOOP-END LOOP');
 END LOOP;
        EXIT externo WHEN (v_contador1 = 2 AND v_contador2 = 3);  
        EXIT interno WHEN v_contador2 >= 3;
    END LOOP;
    EXIT externo WHEN v_contador1 >= 3;
  END LOOP;   
END proc08;

OCA: Auditoria en base de datos Oracle.

Auditoria significa capturar y almacenar información acerca de lo que está sucediendo en el sistema, este proceso por lo tanto aumenta la cantidad de trabajo que el sistema debe de hacer. La auditoria debe ser enfocada a solo aquellos eventos que son de interes, una auditoria apropiadamente direccionada tiene un mínimo impacto sobre el desempeño del sistema, pero una auditoria no adecuada puede tener un impacto significativo negativo sobre el desempeño del sistema. El monitoreo y la auditoria debe ser una parte integral de tus procedimientos de seguridad, los cuales incluyen:

Auditoria obligatoria:

Todas las bases de datos Oracle auditan ciertas acciones a pesar de que las opciones ó parámetros de auditoria esten desactivados, la razón para una auditoria obligatoria es que la base de datos necesita registrar algunas actividades de base de datos críticas como son el inicio ó apagado del sistema.

Auditoria standard

Este tipo de auditoria es una caracteristica que se usa a nivel del sistema, usando un parámetro de inicialización, dicho parámetro es AUDIT_TRAIL. Después de que habilitaste la auditoria, ahora debes de seleccionar los objetos y privilegios que quieras auditar.

Auditoria basada en valores

Este tipo de auditoría extiende la auditoría standard, capturando no solo los eventos de auditoria que han ocurrido sino tambien los valores actuales que han sido insertados, actualizados o borrados, la auditoría basada en valores es implementada a través de triggers de base de datos.

Auditoría detallada

La auditoria detallada extiende la auditoria standard, capturando las declaraciones SQL actuales que han sido ejecutadas y no solo el evento que ha ocurrido.

Mucha teoria, veamos algo de practica sobre como podemos implementar la auditoría detallada en Oracle:

Supongamos que tú como DBA quieres saber cuando un usuario a seleccionado ciertos registros que son de vital importancia para la empresa, los registros los vamos a crear de la siguiente manera:

Objetos a crear:

Una tabla en el schema hr llamada empleados, el ddl es el siguiente:

create table hr.empleados(

codigo char(2) constraint nn_codigo_emp not null,

nombres varchar2(40) constraint nn_nombres_emp not null,

apellidos varchar2(40) constraint nn_apellidos_emp not null,

sueldo number(10,2) constraint nn_sueldo_emp not null,

primary key(codigo)

);

-- y el dml el siguiente:

insert into hr.empleados values('01','nombres1','apellidos1',1000);

insert into hr.empleados values('02','nombres2','apellidos2',2000);

insert into hr.empleados values('03','nombres3','apellidos3',3000);

insert into hr.empleados values('04','nombres4','apellidos4',4000);

insert into hr.empleados values('05','nombres5','apellidos5',5000);

Una vez creada la tablita vamos a auditar(seguir los pasos) de los usuarios que seleccionan los registros de los empleados que ganan más de S/.3000 (campo sueldo de la tabla hr.empleados)

Creamos un par de usuarios y le asignamos los privilegios de conexion y consulta sobre la tabla creada anteriormente:

create user sapo01 identified by sapo01;

create user sapo02 identified by sapo02;

grant connect to sapo01,sapo02;

grant select on hr.empleados to sapo01,sapo02;

Usamos el paquete dbms_fga para crear una politica de auditoria sobre una tabla o vista determinada(en nuestro caso la tabla hr.empleados), si cualquiera de las filas retornadas desde una consulta(query sql) ejecutadas por cualquier usuario coincide con la columna auditada y la condición de auditoria especificada entonces un evento de auditoria será creado y almacenado en las pistas de auditoria de Oracle. Opcionalmente, el evento de auditoria puede tambien ejecutar un procedimiento, La auditoria detallada se enfoca en un nivel declarativo(sql) es así que una declaración select que retorna cientos de filas generará solo un registro de auditoria.

El bloque pl/sql que crea la politica de auditoria es el siguiente:

begin

dbms_fga.add_policy (

object_schema=>'hr',

object_name=>'empleados',

policy_name=>'auditoria_sueldo_empleado',

audit_condition=>'sueldo>3000',

audit_column=>'sueldo',

handler_schema=>null,

handler_module=>null,

enable=>true,

statement_types=>'select');

end;

Ahora entramos con cualquiera de los usuarios creados enteriormente desde sqlplus y ejecutamos una consulta sobre la tabla que ya hemos configurado para que sea auditada:

C:\Documents and Settings\Pc>sqlplus /nolog

SQL*Plus: Release 10.2.0.1.0 - Production on Sßb Ago 1 22:27:18 2009

Copyright (c) 1982, 2005, Oracle. All rights reserved.

nolog> connect sapo01/sapo01@optimus;

Conectado.

SAPO01: OPTIMUS> select * from hr.empleados where sueldo=4000;

CO NOMBRES

-- ----------------------------------------

APELLIDOS SUELDO

---------------------------------------- ----------

04 nombres4

apellidos4 4000

SAPO01: OPTIMUS>

Ahora como usuario dba vemos los registros de auditoria creados, consultando a la vista dba_fga_audit_trail vemos algunos datos relevantes, entre los que destacan:

DB_USER, el usuario que realizó la consulta

OS_USER, el usuario desde donde se inició la sesión del sistema operatico cliente

USERHOST, nombre de la máquina del usuario que realizó la consulta

OBJECT_SCHEMA, el nombre del esquema auditado(hr en nuestro caso)

OBJECT_NAME, nombre de la tabla ó vista auditada(empleados en nuestro caso)

POLICY_NAME, nombre de la politica creada(auditoria_sueldo_empleado)

SQL_TEXT, el sql que ejecutó el usuario

El proceso de auditoría creado es bastante simple, en tu trabajo quizás quieras auditar tablas o vistas críticas que involucran miles de accesos por minuto, por lo que estos tipos de registros de auditoria debes de hacerlo con mucho cuidado y solo en casos en que lo amerite, como dije al comienzo una auditoría mal creada puede devenir en un performance negativo.

Arquitectura de una Base de Datos Oracle

Un servidor Oracle consiste de una "instancia Oracle" (memoria=SGA+PGA) y de una "base de datos Oracle" (disco=datafiles) esta base de datos en ejecución está asociada con una instancia.

Es así que cuando una base de datos es iniciada sobre un servidor de base de datos, el software de oracle asigna un área de memoria compartida llamada "System Global Area (SGA)" ó "Area Global del Sistema" e inicia varios procesos en background de Oracle, esta combinación del SGA y los procesos Oracle conforma la instancia.

Después de iniciar una instancia, el software de Oracle asocia la instancia con una base de datos específica, dicho proceso es llamado el proceso de montar(STARTUP MOUNT) la base de datos, ahora la base de datos está entonces lista para ser abierta(STARTUP OPEN), lo cual la hace accesible a usuarios autorizados.

Multiples instancias pueden ejecutarse concurrentemente sobre la misma computadora, cada una accesando su propia base de datos física. La arquitectura de la base de datos se puede entonces observar como varios componentes estructurales interrelacionados.

Una base de datos Oracle usa estructuras de memoria y procesos para gestionar y accesar la base de datos, todas estas estructuras de memoria existen en la memoria principal(RAM) del servidor(HW) que constituye el servidor de base de datos.

Los procesos son hilos que trabajan en la memoria de estos servidores(HW), asi un proceso es definido como un hilo de control o un mecanismo en un sistema operativo que puede ejecutar una serie de tareas.

Las estructuras de memoria básicas asociadas con una instancia Oracle incluyen:

System Global Area (SGA): Memoria compartida por todos los procesos.

Program Global Area (PGA): Memoria privada a cada proceso de servicio y de fondo, solo hay una PGA por cada proceso.

El SGA es un área de memoria que contiene datos e información de control para la instancia, incluye las siguientes estructuras de datos:

Database buffer cache: Memoria cache(de rápido acceso) de los bloques de datos recibidos desde la base de datos, es decir cuando queremos obtener un dato y este no está en memoria entonces es primero obtenida del disco y copiada a este buffer para una posterior obtención más rápida, es decir el próximo proceso ya no se vaya a disco sino al buffer.

Redo log buffer: Memoria cache(de rápido acceso) de la información "redo" hasta que pueda ser escrita a los archivos físicos "redo logs" que se encuentran en disco. Cada transacción que realizamos es primero copiada a este buffer y después a los archivos "redo logs" en disco.

Shared pool: Memoria cache(de rápido acceso) que almacena varias estructuras que pueden ser compartidas entre procesos de usuario.

Large pool: Es un área opcional que proporciona grandes asignaciones de memoria para ciertos procesos largos, como son operaciones de backups y de recuperación así como tambien procesos de entrada y salida al disco.

Java pool: Es usado para todas las sesiones específicas a código java y datos dentro de la máquina virtual java.

Streams pool: Es usado por Oracle Streams.

Cuando iniciamos una instancia la cantidad de memoria asignada para el SGA es mostrada en la interface de comandos de sqlplus o en Enterprise Manager, esta información es mostrada en bytes.