TIP: ¿Que pasa si quiero trabajar con Import/export y el usuario SYS?

En más de una ocasión hemos querido volcar los datos de auditoría de una base de datos a otra  y más concretamente las tablas:

• AUD$
•  FGA_LOG$

Supongamos que tenemos una instalación de Oracle 10g R2 en concreto 10.2.0.1, (sí, las hay) y no disponemos de  DBMS_AUDIT_MGMT (nota de Oracle Support 731908.1) para purgar y gestionar la información de auditoría. 

Este paquete solo es para Audit Vault 10.2.0.3 y el uso fuera de este utilitario no esta soportada, sin embargo , desde la versión 11gr2 está disponible de forma libre, aunque fue liberada para la versión 11gr1 específicamente en el patchset 11.1.0.7, además está disponible para la versión 10.2.0.3 en el parche 6989148 , también disponible en la versión 10.2.0.4 en el parche 6996030.

 

¿Cómo hacerlo?

Estas tablas son propiedad de SYS, el usuario más poderoso dentro de una base de datos Oracle (vamos es como Hulk en Avengers) Recordemos que estas tablas no se pueden practicar particiones (mientras siga la tabla en el tablespace system.) y puede que queramos que desaparezcan del tablespace, porque su crecimiento desmesurado puede poner en riesgo el rendimiento de nuestro sistema.

La forma de utilizar a nuestro Hulk (usuario SYS) a la hora de importar los datos es usarlo de la siguiente manera:

$>imp \"sys/Menele#32 as sysdba\"  tables=fga_log$ file=/oracle/home/table.dmp ignore=Y buffer=1000000

$>imp \"sys/Menele#32 as sysdba\"  tables=aud$ file=/oracle/home/table.dmp ignore=Y buffer=1000000

La opción Ignore=Y, permite que no se recree la table y se importen solo los datos.

Para solo purgar datos podemos utilizar la siguiente estrategia:

SQL> connect / as sysdba Connected. SQL> truncate table fga_log$; Table truncated.

Puedes borrar determinados registros usando la columna  TIMESTAMP# (date) como sigue:

SQL> delete from fga_log$ where timestamp# < sysdate-14;

Superar la limitación de los N caracteres con el paquete DBMS_OUTPUT.PUT_LINE

 Si hablamos de versiones de de Oracle Database anteriores a 10g R2 

Oracle facilitó el paquete DBMS_OUTPUT para los desarrolladores de PL/SQL. La función PUT_LINE() es la característica más utilizada por los desarrolladores Oracle. PUT_LINE se usa para mostrar resultados en la pantalla o para mostrar resultados en el spool y sacarlo a un archivo. Pero esta función tiene un límite solo permite 255 caracteres y si te pasas se provoca una excepción PL/SQL.

declare
   v_str varchar2(1000); 
begin 
   for i in 1..275 
   loop 
      v_str := v_str || TO_CHAR(i); 
   end loop; 
   dbms_output.put_line( v_str);
end;
/

Si ejecutamos el código en un motor de base de datos anterior a Oracle 10g R2, nos devuelve el error: 

ORA-20000: ORU-10028: line length overflow, limit of 255 chars per line.

Para solventar este problema podremos crear un procedimiento almacenado que sobrescriba el comportamiento de esta función de tal manera que divida la información en paquetes de 255 caracteres y la una en la salida.

create or replace procedure rafa.custom_output(in_string in varchar2 )
is 
   out_string_in long default in_string; 
   str_len number; 
   loop_count number default 0; 
begin 
   str_len := length(out_string_in);
   while loop_count < str_len
   loop 
      dbms_output.put_line( substr( out_string_in, loop_count +1, 255 ) ); 
      loop_count := loop_count +255; 
   end loop; 
end;

Si ejecutamos este código en nuestro motor de versión anterior a Oracle 10g R2, no dará el error.

declare
   v_str varchar2(1000); 
begin 
   for i in 1..275 
   loop 
      v_str := v_str || TO_CHAR(i); 
   end loop; 
   rafa.custom_output( v_str);
end; 
/

Si hablamos de versiones de de Oracle Database posteriores a 10g R2 Oracle amplió el tamaño de los caracteres a 32,767 bytes (el mismo límite superior que tiene otro paquete UTL_FILE). Este código ya no da ningún error para versiones del motor 10g R2 y 11g.

set linesize 64
BEGIN
DBMS_OUTPUT.PUT_LINE( RPAD('x',255,'x') );
END;
/

Si ejecutamos este código en un motor Oracle 10g R2 o superior, dará el siguiente error:

BEGIN
DBMS_OUTPUT.PUT_LINE( RPAD('$',32767,'$') || 'incluso mas...' );
END;
/
BEGIN
*
ERROR at line 1:
ORA-20000: ORU-10028: line length overflow, limit of 32767
bytes per line
ORA-06512: at "SYS.DBMS_OUTPUT", line 32
ORA-06512: at "SYS.DBMS_OUTPUT", line 91
ORA-06512: at "SYS.DBMS_OUTPUT", line 112
ORA-06512: at line 2

SQL> set serveroutput on size unlimited 
SQL> show serveroutput
serveroutput ON SIZE UNLIMITED FORMAT WORD_WRAPPED

Para ver que la opción ilimitada funciona, intentaremos sacar una salida superior al límite del buffer para esta opción (1000000 bytes). El siguiente ejemplo mostrará 1048544 bytes.

SQL> set lines 100
 
SQL> BEGIN
  2     FOR i IN 1 .. ((1000000/32767)+1) LOOP
  3        DBMS_OUTPUT.PUT_LINE( RPAD('$',32767,'$') );
  4     END LOOP;
  5  END;
  6  /
 
PL/SQL procedure successfully completed.

Eso si si abusamos de este código saldrá este error entre otro, así que cuidado con los experimentos, amiguitos: 
ORA-04030: out of process memory when trying to allocate 4120 bytes.

TIP: SQL y fechas; Aquellos maravillosos años..

Si recordáis aquellas consultas megalíticas de los informes que nos hacían hacer con Oracle Reports o con otra herramienta arcana, cuando te pedían informes por trimestre (o “Quarters” si tenías un analista del tipo “Tom Cruise-Wannabe”), me echaba a temblar. Aquí hay una serie de consultas que a lo mejor sirven para algo, nunca se sabe.

• Número de mes en el trimestre

SQL>select trunc(to_char(sysdate,'mm')/to_char(sysdate,'Q')) fecha from dual;

•  Número de semana en el trimestre

SQL>select 1 + TRUNC((sysdate - TRUNC(sysdate, 'Q'))/7) fecha from dual; 

• Número de día en el trimestre

SQL>select trunc(sysdate-trunc(sysdate,'Q'))+1 fecha from dual;

• ¿La fecha seleccionada es fin de semana?

Tenemos esta tabla Prueba con estos datos y queremos saber si la fecha de inicio cae en fin de semana.

ID	DESCP	FEC_INICIO	FEC_FIN
1	prueba1	02/10/2012
2	prueba2	03/10/2012
3	prueba3	04/10/2012
4	prueba4	06/10/2012

Esta consulta nos devuelve el indicador de fila que se ha creado en fin de semana: 

SQL>Select id FROM rafa.pruebas WHERE MOD(TO_CHAR(fec_inicio, 'J'), 7) + 1 IN (6, 7);