CONN ASTDBA10G/MAREA@PROAPP1;

CREATE OR REPLACE PACKAGE ASTDBA10G.PKG_ESTADISTICAS IS
   compute_threshold_table number := 40;
   compute_threshold_index number := 20;
   PROCEDURE PROC_CALC_ESTAT(p_schema varchar2, p_table varchar2);
END PKG_ESTADISTICAS; -- Package spec
/

CREATE OR REPLACE PACKAGE BODY "PKG_ESTADISTICAS" IS
   PROCEDURE PROC_CALC_ESTAT(p_schema varchar2, p_table varchar2) IS
   ERR_NUM NUMBER;  
   ERR_MSG VARCHAR2(255);
   BEGIN
       for s in ( SELECT owner, segment_name, segment_type,
                         case when (segment_type = 'TABLE') and (bytes/(1024*1024) > 1.2 * compute_threshold_table)
                                then 1 + trunc(100*(compute_threshold_table*1024*1024/bytes))
                              when (segment_type = 'INDEX') and (bytes/(1024*1024) > 1.2 * compute_threshold_index)
                                then 1 + trunc(100*(compute_threshold_index*1024*1024/bytes))
                              else 100
                         end sample_size
                    FROM dba_segments
                   WHERE owner = p_schema
                     AND segment_name like '' || p_table || '')
                     --AND segment_name = 'PK_INCIDENT_ALERT'
                --ORDER BY segment_name )
       loop
         -- If the sample_size is 100, then compute statistics.
         if s.sample_size = 100 then
           if s.segment_type = 'TABLE' then
              begin
                  dbms_application_info.set_action( action_name  => 'Compute table stats' );
                  dbms_application_info.set_client_info( client_info  => 'Table = ' || s.segment_name );
                  dbms_stats.gather_table_stats( ownname  => s.owner,
                                                 tabname  => s.segment_name,
                                                 partname => NULL );

                  --INSERT INTO TOOL_ORA.T_LOG_STATS (KEYFECHA, KEYSCHEMA, KEYOBJETO, TIPO_OBJETO, TIPO_CALCULO, MUESTRA, OBSERVACIONES)
                  --VALUES (SYSDATE, P_SCHEMA, s.segment_name, 'TABLA', 'COMPUTED', 100, 'EXITO');
                  --dbms_output.put_line( 'Computed statistiscs for table ' || s.segment_name || '.' );
              exception
                  when others then
                       ERR_NUM := SQLCODE;  
                       ERR_MSG := SQLERRM;
                       
                       /*INSERT INTO TOOL_ORA.T_LOG_STATS (KEYFECHA, KEYSCHEMA, KEYOBJETO, TIPO_OBJETO, TIPO_CALCULO, MUESTRA, OBSERVACIONES)
                       VALUES (SYSDATE, P_SCHEMA, s.segment_name, 'TABLA', 'COMPUTED', 100, substr('ERROR:' || ERR_NUM || '-' || ERR_MSG,1,255));*/
              end;                 
           elsif s.segment_type = 'INDEX' then
              begin
                  dbms_application_info.set_action( action_name  => 'Compute index stats' );
                  dbms_application_info.set_client_info( client_info  => 'Index = ' || s.segment_name );
                  dbms_stats.gather_index_stats( ownname  => s.owner,
                                                 indname  => s.segment_name,
                                                 partname => NULL );

                  --INSERT INTO TOOL_ORA.T_LOG_STATS (KEYFECHA, KEYSCHEMA, KEYOBJETO, TIPO_OBJETO, TIPO_CALCULO, MUESTRA, OBSERVACIONES)
                  --VALUES (SYSDATE, P_SCHEMA, s.segment_name, 'INDICE', 'COMPUTED', 100, 'EXITO');
                  --dbms_output.put_line( 'Computed statistiscs for index ' || s.segment_name || '.' );
              exception
                  when others then
                       ERR_NUM := SQLCODE;  
                       ERR_MSG := SQLERRM;

                       /*INSERT INTO TOOL_ORA.T_LOG_STATS (KEYFECHA, KEYSCHEMA, KEYOBJETO, TIPO_OBJETO, TIPO_CALCULO, MUESTRA, OBSERVACIONES)
                       VALUES (SYSDATE, P_SCHEMA, s.segment_name, 'INDICE', 'COMPUTED', 100, substr('ERROR:' || ERR_NUM || '-' || ERR_MSG,1,255));*/
              end;                 
           end if;
         -- If the sample_size < 100, then estimate statistics.
         else
           if s.segment_type = 'TABLE' then
              begin
                  dbms_application_info.set_action( action_name  => 'Estimate table stats' );
                  dbms_application_info.set_client_info( client_info  => 'Table = ' || s.segment_name );
                  dbms_stats.gather_table_stats( ownname  => s.owner,
                                                 tabname  => s.segment_name,
                                                 partname => NULL,
                                                 estimate_percent => s.sample_size );
                                                 
                  --INSERT INTO TOOL_ORA.T_LOG_STATS (KEYFECHA, KEYSCHEMA, KEYOBJETO, TIPO_OBJETO, TIPO_CALCULO, MUESTRA, OBSERVACIONES)
                  --VALUES (SYSDATE, P_SCHEMA, s.segment_name, 'TABLA', 'ESTIMATED', s.sample_size, 'EXITO');
                  --dbms_output.put_line( 'Estimated statistiscs for table ' || s.segment_name || ', sample size ' || s.sample_size ||'%.' );
              exception
                  when others then
                       ERR_NUM := SQLCODE;  
                       ERR_MSG := SQLERRM;

                       /*INSERT INTO TOOL_ORA.T_LOG_STATS (KEYFECHA, KEYSCHEMA, KEYOBJETO, TIPO_OBJETO, TIPO_CALCULO, MUESTRA, OBSERVACIONES)
                       VALUES (SYSDATE, P_SCHEMA, s.segment_name, 'TABLA', 'ESTIMATED', s.sample_size, substr('ERROR:' || ERR_NUM || '-' || ERR_MSG,1,255));*/
              end;                 
           elsif s.segment_type = 'INDEX' then
              begin
                  dbms_application_info.set_action( action_name  => 'Estimate index stats' );
                  dbms_application_info.set_client_info( client_info  => 'Index = ' || s.segment_name );
                  dbms_stats.gather_index_stats( ownname  => s.owner,
                                                 indname  => s.segment_name,
                                                 partname => NULL,
                                                 estimate_percent => s.sample_size );
                                                 
                  --INSERT INTO TOOL_ORA.T_LOG_STATS (KEYFECHA, KEYSCHEMA, KEYOBJETO, TIPO_OBJETO, TIPO_CALCULO, MUESTRA, OBSERVACIONES)
                  --VALUES (SYSDATE, P_SCHEMA, s.segment_name, 'INDICE', 'ESTIMATED', s.sample_size, 'EXITO');
                  --dbms_output.put_line( 'Estimated statistiscs for index '  || s.segment_name || ', sample size ' || s.sample_size ||'%.' );
              exception
                  when others then
                       ERR_NUM := SQLCODE;  
                       ERR_MSG := SQLERRM;

                       /*INSERT INTO TOOL_ORA.T_LOG_STATS (KEYFECHA, KEYSCHEMA, KEYOBJETO, TIPO_OBJETO, TIPO_CALCULO, MUESTRA, OBSERVACIONES)
                       VALUES (SYSDATE, P_SCHEMA, s.segment_name, 'INDICE', 'ESTIMATED', s.sample_size, substr('ERROR:' || ERR_NUM || '-' || ERR_MSG,1,255));*/
              end;                 
           end if;
         end if;
       end loop;
       COMMIT;
   EXCEPTION
       WHEN OTHERS THEN
            COMMIT;
   END PROC_CALC_ESTAT;
END;
/

DISCONN;