Trabajando con JOB en oracle 11g

hola, este dia en mi trabajo se me asigno la tarea de crear un job, para mi no es problema, pero que pasa si alguien no sabe?? , entonces les dejo este pequenio manual, ha sido probado en la version 11g.

1) primero vamos a crear una tabla la que llenaremos por medio del JOB

CREATE TABLE TMP_BORRAME_PRUEBA
(
  DESCRIPCION  VARCHAR2(100 BYTE),
  FECHA        DATE
);


2) creamos un procedimiento PL/SQL para ser llamado desde el JOB

CREATE OR REPLACE procedure prc_tmp_borrame
is
v_contador number(5) := null;
begin
    select count(1)
       into v_contador
         from tmp_borrame_prueba;
       
   insert into tmp_borrame_prueba
              (descripcion, fecha)
      values  ('Registros contados :'|| to_char(v_contador), sysdate);
      commit;          
end;
/


3) lo siguiente , puedes usarlo de acuerdo a tu conveniencia

--- para ver todos los jobs del esquema ONDE EJSTOY connected
SELECT JOB,
       SCHEMA_USER,
       LAST_DATE,
       NEXT_DATE,
       INTERVAL,
       WHAT
  FROM DBA_JOBS
     WHERE SCHEMA_USER = (SELECT USER FROM DUAL);



BEGIN
DBMS_JOB.BROKEN( <NUMERO JOB>, FALSE );  -- pone estado on line el JON [dependiendo tu caso]
DBMS_JOB.BROKEN( <NUMERO JOB>, TRUE );  -- pone estado OFF line el JON [dependiendo tu caso]
COMMIT;
END;



BEGIN
DBMS_JOB.REMOVE ( <NUMERO JOB> );  ---BORRA ELJOB
COMMIT;
END;


-- forma para crear un job.....
DECLARE
  X NUMBER;
BEGIN
    SYS.DBMS_JOB.SUBMIT
    ( JOB       => X
     ,WHAT      => -- colocamos entre [BEGIN] ..... [ END; ] el codigo pl que deseamos ejecutar
                   'begin
                    prc_tmp_borrame;
                    end;'
     ,NEXT_DATE => TO_DATE('31/10/2014 14:36:37','dd/mm/yyyy hh24:mi:ss') --- fecha y hora de inicio
     ,INTERVAL  => 'SYSDATE+1/1440'  --- intervalo de ejecucion
   --,interval  => 'TRUNC(SYSDATE+1)+3.5/24'  -- para ejecutarlo todos los dias a las 3:30 .....
     ,NO_PARSE  => FALSE  --- vale chonga :D
    );
    SYS.DBMS_OUTPUT.PUT_LINE('Job Number is: ' || TO_CHAR(X));
  COMMIT;
END;
/

UPDATE, EN TABLA CON VARIOS MILLONES DE REGISTROS???

hola amig@ , este día se me encomendó realizar una actualización para una tabla destino que contiene 4.5 millones de registros, desde una tabla origen que contiene 8.5 millones de registros.

bueno para eso probé varias opciones desde hacer un cursor hasta hacer un merge pero los tiempos de ejecución fueron muy lentos, buscando información me encontré con un documento de ORACLE, aca les dejo el ejemplo, los conceptos a comprender son facilites; también se manejan los errores, por cuestión de tiempo no los incorporo a una tabla para manejar una estadística, ya eso seria opción de ustedes ;)

no se olviden de dejar sus comentarios....

CREATE OR REPLACE PROCEDURE SLDUSR.UPDATE_ALL_ROWS ( P_LOAÑO IN number ,

                                                                                               P_LOMES  IN number,

                                                                                               P_LODIA  IN number )

AS                                                                                  

       TYPE V_TCCCLI    IS TABLE OF NOV_L1CTLOG.TCCCLI%TYPE  INDEX BY PLS_INTEGER;

       TYPE V_TCNCON  IS TABLE OF NOV_L1CTLOG.TCNCON%TYPE  INDEX BY PLS_INTEGER;

       TYPE V_TCNFOL   IS TABLE OF NOV_L1CTLOG.TCNFOL%TYPE  INDEX BY PLS_INTEGER;

       TYPE V_LOTANT   IS TABLE OF NOV_L1CTLOG.LOTANT%TYPE INDEX BY PLS_INTEGER;

       TYPE V_LOTDES   IS TABLE OF NOV_L1CTLOG.LOTDES%TYPE INDEX BY PLS_INTEGER;

        

       TO_V_TCCCLI    V_TCCCLI;

       TO_V_TCNCON  V_TCNCON;

       TO_V_TCNFOL   V_TCNFOL;

       TO_V_LOTANT   V_LOTANT;

       TO_V_LOTDES   V_LOTDES;

   

      X_BAD_ITERATION EXCEPTION;

      PRAGMA EXCEPTION_INIT (X_BAD_ITERATION, -24381 );

    

   BEGIN

   

      SELECT  TRIM(TCCCLI)    ,

                   TRIM(TCNCON)  ,

                   TRIM(TCNFOL)   ,

                   TRIM(LOTANT)   ,

                   TRIM(LOTDES)    

      BULK COLLECT INTO  TO_V_TCCCLI   ,

                                     TO_V_TCNCON ,

                                     TO_V_TCNFOL   ,

                                     TO_V_LOTANT   ,

                                     TO_V_LOTDES    

      FROM NOV_L1CTLOG

            WHERE  LOAÑO   = P_LOAÑO AND

                           LOMES = P_LOMES AND

                          LODIA   = P_LODIA ;

   

      FORALL I IN TO_V_TCNFOL.FIRST .. TO_V_TCNFOL.LAST SAVE EXCEPTIONS

         UPDATE ASSET2

         SET TELEFONO = TO_V_LOTDES(I)

         WHERE ID_ANE_BILLING = TO_V_TCNFOL(I);

   

     DBMS_OUTPUT.PUT_LINE ( TO_CHAR(SQL%ROWCOUNT) || ' records updated.' );

     COMMIT;

   

   EXCEPTION

   

      WHEN X_BAD_ITERATION 

      THEN

         DBMS_OUTPUT.PUT_LINE ('SUCCESSFUL UPDATE OF ' || TO_CHAR(SQL%ROWCOUNT) || ' RECORDS.' );

         DBMS_OUTPUT.PUT_LINE ( 'FAILED ON ' || TO_CHAR(SQL%BULK_EXCEPTIONS.COUNT) || ' RECORDS.' );

               

         FOR I IN 1 .. SQL%BULK_EXCEPTIONS.COUNT 

         LOOP

            DBMS_OUTPUT.PUT_LINE (

                                                       'ERROR OCCURRED ON ITERATION ' ||

                                                          SQL%BULK_EXCEPTIONS(I).ERROR_INDEX ||

                                                             ' DUE TO ' ||

                                                                SQLERRM (

                                                                   -1 * SQL%BULK_EXCEPTIONS(I).ERROR_CODE)

                                                     );

                                                     

            DBMS_OUTPUT.PUT_LINE (

                                                       'NOV_L1CTLOG = ' ||

                                                          TO_CHAR (

                                                             TO_V_TCNFOL (

                                                                SQL%BULK_EXCEPTIONS(I).ERROR_INDEX )) ||

                                                       ' VALUE = ' ||

                                                          TO_CHAR (

                                                             TO_V_LOTANT (

                                                                SQL%BULK_EXCEPTIONS(I).ERROR_INDEX )) 

                                                     );  

         END LOOP;

      END UPDATE_ALL_ROWS;

SQLLOADER DE BASE A BASE???

Me encuentro trabajando para un cliente en el que debemos migrar una data desde AS400 hacia ORACLE 11g..... jijiji , actualmente se hace un pl para hacer el insert y manejar los errores, pues la cosa no deberia aser asi!!!!

les dejo como hacer un sql loader de base a base, para lo cual se creo un DBLINK (pero ese es otro tema)

Paso 1) creamos la tabla que poblaremos con los registros probenientes de la tabla a migrar

             

paso 2)  haremos un pequenio bloque anonimo PL en el cual se crea una tabla donde se manejan los errores

      

        BEGIN

          DBMS_ERRLOG.CREATE_ERROR_LOG( ' <nombre de la tabla> ' ) ;

        END;

        Esta acción crea otra tabla con el acronimo ERR$_<nombre de la tabla>

paso 3)  posteriormente procedemos a realizar un insert into \

  

INSERT INTO <tabla destino> (campo1    ,

                                                   campo2        ,

                                                   campo3    ,

                                                   campo4    ,

                                                   campo5      )

                                   SELECT    campo1    ,

                                                   campo2        ,

                                                   campo3    ,

                                                   campo4    ,

                                                   campo5  

    FROM   <tabla origen>@<nombre del dblink>

       where      campo1   = parametro1

            and     campo2   = parametro2

            and     campo3   = parametro3

    LOG ERRORS REJECT LIMIT UNLIMITED;

paso 4) para comprobar si existieron errores deben hacer un select a la tabla creada en el paso 2

     

            SELECT * FROM ERR$_<nombre de la tabla>; 

PASO 5) HAY ME CUENTAN COMO LES FUE...

      

Listar los direcorios en linux en forma de Arbol

este dia me toco hacer una lista de los directorios que se encuentran bajo mi instalación, yo recuerdo que un amigo (Ing. Luis Eduardo Solorzano) me mostró el uso del comando sed, pero no me acordaba como usarlo así que entre a la ayuda (man sed) para ver los parámetros y usarlo correctamente, el resultado fue este comando de linea:

ls -R | grep ":$" | sed -e 's/:$//' -e 's/[^-][^\/]*\//--/g' -e 's/^/ /' -e 's/-/|/'

lo divertido del caso es que ya existe mucha información al respecto en la red, jajajaja, lo mejor de todo es que he recordado cosas del PODEROSO UNIX...

El usuario no Ejecuta el proceso dinamico???

este dia me tope con un problema a un usuario se le creo un proceso para que de forma dinamica asigne permisos a roles o roles a usuarios pero NO FUNCIONO cuando lo ejecuta el usuario final????
por que sera????
el mensaje de error era :

ORA-01031: insufficient privileges while issuing an EXECUTE IMMEDIATE

pos en una literatura encontré lo siguiente:
Un Procedure o Package se ejecuta con los permisos del que lo ejecuta o con los permisos del esquema ONDE (donde) esta almacenado. 

http://docs.oracle.com/cd/B28359_01/appdev.111/b28370/create_package.htm#i2065285

AUTHID CURRENT_USER

Specify CURRENT_USER to indicate that the package executes with the privileges of CURRENT_USER. This clause creates an invoker's rights package.

This clause also specifies that external names in queries, DML operations, and dynamic SQL statements resolve in the schema of CURRENT_USER. External names in all other statements resolve in the schema in which the package resides.

AUTHID DEFINER

Specify DEFINER to indicate that the package executes with the privileges of the owner of the schema in which the package resides and that external names resolve in the schema where the package resides. This is the default and creates a definer's rights package.

Esta clausula puede ser omitida y por defecto tomara el valor de AUTHID DEFINER pero para mi criterio es mejor colocarlo, un par de letras mas no dañan a nadie

AUTHID DEFINER (default)
Esta opcion es por defecto si no se especifica nada. En este caso el procedimiento o paquete se va a ejecutar con el dueño del esquema en que fue creado el procedimiento o paquete.
AUTHID CURRENT_USER
En este caso el procedure se ejecuta con el usuario que realiza la ejecucion y dentro de su esquema independientemente de quien sea el dueño del procedimiento o paquete.

Nota: en ambos casos se le debe dar grant de execute al usuario/esquema o rol que quiera utilizar el procedimiento o paquete

les debo el ejemplo :D :D :D

Script para Compilar los Trigger, Funciones, Proc., Paquetes...

MIÉRCOLES, 19 DE MARZO DE 2014

hola,
este dia he tenido que realizar una actividad para verificar y compilar todos los trigger de una base de datos oracle 12c, para lo cual cree un script basado en el contenido de la tabla sys.dba_objects, pero me surgio una duda??......
como puedo compilar las vistas, trigger, definicion de paquetes, cuerpos de paquetes, procedimientos de una sola vez..

para este caso he creado el siguiente script que espero les sea de utilidad, no olvides dejar tus comentarios

set serveroutput on size 9999
begin
for c1 in (select  CASE
        WHEN  object_type='TRIGGER'     THEN  'ALTER TRIGGER '||owner||'.'||object_name||' COMPILE'
        WHEN object_type='PROCEDURE' THEN  'ALTER PROCEDURE '||owner||'.'||object_name||' COMPILE'
        WHEN  object_type='FUNCTION'   THEN  'ALTER FUNCTION '||owner||'.'||object_name||' COMPILE'
        WHEN  object_type='VIEW'          THEN  'ALTER VIEW '||owner||'.'||object_name||' COMPILE' 
        WHEN  object_type='PACKAGE'       THEN  'ALTER PACKAGE '||owner||'.'||object_name||' COMPILE'
        WHEN  object_type='PACKAGE BODY'       THEN  'ALTER PACKAGE '||owner||'.'||object_name||' COMPILE BODY'
        END sqltext,
        object_type,
        object_name,
        owner
from dba_objects
where object_type IN ( 'TRIGGER' , 'PROCEDURE', 'FUNCTION', 'VIEW','PACKAGE','PACKAGE BODY'  )
and status = 'INVALID'
and owner in ('ESQUEMA1', 'ESQUEMA2', 'ESQUEMA3', etc, etc.....)
ORDER BY object_type
) loop
begin
execute immediate c1.sqltext;
dbms_output.put_line('COMPILADO '||c1.owner||'.'||c1.object_name);
exception
when others then
dbms_output.put_line('--- ERROR DE COMPILACION VERIFICAR EN : '||
                                      C1.object_type|| '  ESQUEMA '||C1.owner||'.'||
                                      c1.object_name);
end;
end loop;
end;

Exporta DDL Oracle 12c Dedicado a Extraer la metadata por esquema o de toda la base de datos; Las pruebas han sido realizadas en Oracle 12c/UNIX

Primera entrega, 
publicado el MIÉRCOLES, 2 DE ABRIL DE 2014
COMO EXPORTAR EL DDL de mi base de datos ORACLE??

lo primero que debemos tener claro es:
DDL es := Lenguaje de definición de datos
con este lenguaje se puede:
Crear, Eliminar y Modificar Objetos de uno o varios esquemas.
Otorgar y revocar privilegios y roles
Analizar la información en una tabla, un índice o un clúster
Establecer las opciones de auditoría (proximamente algo interesante)
Añadir comentarios al diccionario de datos

al final todo se resume en un script sumamente facil  para la version 12c,
eso si yo ejecute este script en unix/sqlplus

clear screen

accept ESKEMA prompt 'Digite el nombre del ESKEMA : '

accept ARCHIVO prompt  ' Digite el nombre del directorio/ archivo : '

spool &&ARCHIVO..gen

SET LONG 2000000 PAGESIZE 0 head off verify off feedback off linesize 132

SELECT dbms_metadata.get_ddl('USER','&&ESKEMA') FROM dual;

SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','&&ESKEMA') from dual;

SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT','&&ESKEMA') from dual;

SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT','&&ESKEMA') from dual;

spool off

hey no olvides dejar tus comentarios!!!!!

Bendiciones

Creacion de Sinonimos publicos de forma masiva....

este dia (MIÉRCOLES, 2 DE ABRIL DE 2014) me fue asignado crear los sinonimos publicos a todos los esquemas de trabajo, para lo cual cree un codigo pl,
espero que a tod@s les sea de utilidad, no olviden dejar sus comentarios

DECLARE 
CURSOR CREAR_SINONIMO IS
SELECT CASE WHEN OBJECT_TYPE IN (' FUNCTION','PACKAGE','PROCEDURE','SEQUENCE','TABLE','VIEW')
   THEN  'CREATE OR REPLACE PUBLIC SYNONYM '|| OBJECT_NAME||' FOR '||OWNER||'.'||OBJECT_NAME 
   ELSE ''
   END AS PERMISO
     FROM DBA_OBJECTS
     WHERE OWNER IN ('ESKEMA1','ESKEMA2','ESKEMA3')
     AND OBJECT_TYPE IN (' FUNCTION','PACKAGE','PROCEDURE','SEQUENCE','TABLE','VIEW') ;
    BEGIN
      FOR EJECUTA_PERMISO IN CREAR_SINONIMO
      LOOP
      BEGIN
      EXECUTE IMMEDIATE EJECUTA_PERMISO.PERMISO;
       DBMS_OUTPUT.PUT_LINE('SINONIMO CON EXITO '||EJECUTA_PERMISO.PERMISO);
      EXCEPTION WHEN OTHERS
      THEN
       DBMS_OUTPUT.PUT_LINE('ERROR '||EJECUTA_PERMISO.PERMISO);
      END;
      END LOOP; 
    END;