DBA WARS: "It's an older code sir, but it checks out."

En esta entrada del blog vamos a dar salida a varios còdigos SQL Y PL/SQL, antiguos pero todavía válidos.

Problemas con de PL / SQL Developer de Allround Automations

Al usar este IDE e su versión 11.X, una consulta interna toma muchos ciclos de CPU en el servidor de la base de datos (100% de una CPU).

¿Es este tu problema?  Por favor, compruebe si hay una consulta al lanzar el editor con el siguiente formato de consulta:

select s.synonym_name object_name, o.object_type
from sys.all_synonyms s, sys.all_objects o where s.owner in ('PUBLIC', user) and o.owner = s.table_owner and o.object_name = s.table_name and o.object_type in ('TABLE', 'VIEW', 'PACKAGE','TYPE', 'PROCEDURE', 'FUNCTION', 'SEQUENCE');

Se debe a la opción Describir contexto del asistente de código. Para deshabilitar:

Mira en: Tools> Preferences> Code Assistant y deshabilitar la opción "Describe Context".
O comprarles la versión 12.X que ya está resuelto.

Depurar papelera de reciclaje anterior a ...

Tiene la papelera de reciclaje de la base de datos llena o su tamaño es considerablemente grande, pero no quiere hacer un "Borrado" completo?

¿Quieres eliminar solo más de x días (como más de 90 días)?

Una buena forma de hacerlo es:

# 90 días

Select 'purge table '||owner||'."'||OBJECT_NAME||'";' from dba_recyclebin where type='TABLE' and to_date(droptime,'YYYY-MM-DD:HH24:MI:SS')<sysdate-90;

# 90 minutos

Select 'purge table '||owner||'."'||OBJECT_NAME||'";' from dba_recyclebin where type='TABLE' and to_date(droptime,'YYYY-MM-DD:HH24:MI:SS')<sysdate-(90/(24*60));

Mirar esta nota de Metalink (Doc ID 1596147.1) por si teneis alguna qe otra duda.

Purga mejorada de la papelera de reciclaje Oracle

Desde que en la versión 10g de la base de datos , se introdujo el 'reciclaje' para segmentos. Si se establece el parámetro recyelebin = on, y se descarta una tabla, en realidad no se descarta; se renombra a un nombre generado por el sistema. Esto realmente puede ayudar en situaciones donde una tabla se cae accidentalmente, ya que se puede recuperar fácilmente. Sin embargo, sin monitorearlo, puede terminar causando problemas de muchos tipos, porque puede terminar hinchando el número total de segmentos en la base de datos a muchas veces más de lo que era antes.

Oracle solo tiene 4 métodos publicados para descartar lotes de objetos reciclados; siendo:

• El comando SQL> PURGE RECYCLE BIN emitido por un propietario de segmento, que purga todos los segmentos reciclados para ese usuario.,
• El comando SQL> PURGE DBA_RECYCLEBIN, emitido por un DBA, que purga todos los objetos reciclados en la base de dato.
• El comando SQL> PURGE TABLESPACE <tablespace_name> USER <schema_name>; que purga objetos propiedad del propietario dado.
• El comando SQL> PURGE TABLESPACE uwdata USUARIO uwclass; que purga los objetos propiedad del propietario y el espacio de tabla dados.

No hay comandos incorporados que permitan que solo los segmentos de papeleras de reciclaje se realicen antes de cierto tiempo, o que solo reciclen objetos de la papelera con más de un cierto número de versiones recicladas de la misma tabla. Es relativamente fácil crear un procedimiento almacenado para manejar estos casos; esto explica cómo se puede hacer eso.

En PL/SQL, tenemos:

create or replace procedure purge_user_recyclebin(

  p_purge_before_date   in date default=NULL,

  p_purge_keep_versions in number default=NULL,

  p_test_only           in varchar2 := 'Y'

)

is

  cursor c_purge_before_date is

    select object_name

    from  user_recyclebin

    where droptime > p_purge_before_time

    and   can_purge = 'YES'

    order by droptime;

  cursor c_purge_before_version is

     select * from 

       (select original_name,

               type,

               object_name,

               droptime,

               rank() over (partition by original_name,type order by droptime desc) as obj_rank

         from   user_recyclebin

         where  can_purge = 'YES')

       where obj_rank > p_purge_keep_versions

       order by droptime;

  v_sql varchar2(1024);

  exception e_bad_parameters;

  exception e_38302;

  pragma exception_init(e_38302,-38302);  

  procedure runsql(p_object_name in varchar2) 

  is

  begin

    v_sql := 'purge '||p_object_name;

    if (p_test_only = 'N') then

      begin

        execute immediate v_sql;

      exception

         when e_38302 then 

           dbms_output.put_line('Cuidado, el objeto '||p_object_name||' no existe ignóralo');

         when others then

           dbms_output.put_line('Error borrando '||p_object_name);

           dbms_output.put_line(dbms_utility.format_error_backtrace);

      end;

    else

      dbms_output.put_line(v_sql);

    end if;

  end;

begin

  if p_purge_before_date is not null and

     p_purge_keep_versions is null then

     for r in c_purge_before_date loop

       runsql(r.object_name);

     end loop;

  elsif p_purge_before_date is null and

     p_purge_keep_versions is not null then

     for r in c_purge_before_version loop

       runsql(r.object_name);

     end loop;

  else

     raise e_bad_parameters;

  end if;

end purge_user_recylebin;

/

Cómo usarlo

Si desea probar para ver qué haría, sin purgar nada, simplemente pase el parámetro 'p_test' al valor 'Y', y establezca la salida del servidor en el tamaño 1000000. Esto mostrará una lista de los comandos que se ejecutarían. pero en realidad no los ejecuta.

Purgar todos los objetos de la papelera de reciclaje antes de una fecha determinada

Esto purgará todos los objetos de reciclaje reciclados antes del 1 de noviembre, a medianoche.

execute purge_user_recylebin(to_date('20150801 00:00:00','YYYYMMDD HH24:MI:SS'),NULL,'Y');

Esto, cuando se ejecuta como propietario de un esquema, ayuda a mantener la cantidad de objetos reciclados a un nivel razonable, a la vez que se conserva, la capacidad de recuperar segmentos perdidos más recientemente.