Noetix: Manually Rebuilding Cached KFF Data

Sometimes it's necessary to rebuild the cached KFF data that Noetix uses to speed up it's queries. These are typically the tables under the NOETIX_SYS schema that start with KFF_ followed by a code representing the data to be refreshed (i.e. GL_ACCT for General Ledger Account Code Combinations).

This refresh can take quite a time and it's useful (if you are in a highly controlled environment) to be able to hand over to people some statistics regarding the rebuild. Hence the script below.

The script looks at the packages that have been built under your Noetix schema and then into the tables the packages update to determine the current number of rows then it performs the initial upload (a full refresh) and then re-checks the number of records in the table - hopefully this will have changed!

Here is the script;

declare
  -- Local variables here
  v_Item varchar2(255);
begin
  -- Test statements here
  for v_Data in (SELECT ao.object_name,
                        SUBSTR(ao.object_name,
                               7,
                               LENGTH(ao.object_name) - 10) item_name
                   FROM all_objects ao
                  WHERE ao.owner = 'NOETIX_SYS'
                    AND ao.object_name LIKE 'N_KFF_%_PKG'
                    AND ao.object_type = 'PACKAGE') loop
    dbms_output.put_line(RPAD('===== ', 60, '= ') ||
                         TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS'));
    dbms_output.put_line('Processing ' || v_Data.item_name);
    dbms_output.put_line('  - Package name      : N_KFF_' ||
                         v_Data.item_name || '_PKG');
    dbms_output.put_line('  - Data Table name   : N_KFF_' ||
                         v_Data.item_name);
    EXECUTE IMMEDIATE 'SELECT TO_CHAR(COUNT(*)) FROM N_KFF_' ||
                      v_Data.item_name
      INTO v_Item;
    dbms_output.put_line('  - Cache Table Count : ' || v_Item || ' (BEFORE)');
    dbms_output.put_line(RPAD('== Initial Upload == ', 20, '= ') ||
                         TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS'));
    EXECUTE IMMEDIATE 'BEGIN N_KFF_' || v_Data.item_name ||
                      '_PKG.Init_Upld(); END;';
    dbms_output.put_line(RPAD('== Initial Upload == ', 20, '= ') ||
                         TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS'));
    dbms_output.put_line('  - Cache Table Count : ' || v_Item || ' (AFTER)');
    dbms_output.put_line(RPAD('===== ', 60, '= ') ||
                         TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS'));
  end loop;
end;

And here is a sample of the output generated on our system;


===== = = = = = = = = = = = = = = = = = = = = = = = = = = = 16-DEC-2011 15:46:31
Processing ACCALIAS
  - Package name      : N_KFF_ACCALIAS_PKG
  - Data Table name   : N_KFF_ACCALIAS
  - Cache Table Count : 1 (BEFORE)
== Initial Upload ==16-DEC-2011 15:46:31
Truncating Table N_KFF_GL_Acct
Initial Upload Started ...
Populating Table N_KFF_AccAlias with data
Initial Upload insert complete ...
Initial Upload completed ...
Time Taken for gather stats for data cache table in secs : 9
Initial Upload completed ...
 
== Initial Upload ==16-DEC-2011 15:46:31
  - Cache Table Count : 1 (AFTER)
===== = = = = = = = = = = = = = = = = = = = = = = = = = = = 16-DEC-2011 15:46:31
===== = = = = = = = = = = = = = = = = = = = = = = = = = = = 16-DEC-2011 15:46:31
Processing CTLG_GRP
  - Package name      : N_KFF_CTLG_GRP_PKG
  - Data Table name   : N_KFF_CTLG_GRP
  - Cache Table Count : 0 (BEFORE)
== Initial Upload ==16-DEC-2011 15:46:31
Truncating Table N_KFF_GL_Acct
Initial Upload Started ...
Populating Table N_KFF_Ctlg_Grp with data
Initial Upload insert complete ...
Initial Upload completed ...
Time Taken for gather stats for data cache table in secs : 23
Initial Upload completed ...
 
== Initial Upload ==16-DEC-2011 15:46:31
  - Cache Table Count : 0 (AFTER)
===== = = = = = = = = = = = = = = = = = = = = = = = = = = = 16-DEC-2011 15:46:31
===== = = = = = = = = = = = = = = = = = = = = = = = = = = = 16-DEC-2011 15:46:31
Processing GL_ACCT
  - Package name      : N_KFF_GL_ACCT_PKG
  - Data Table name   : N_KFF_GL_ACCT
  - Cache Table Count : 639234 (BEFORE)
== Initial Upload ==16-DEC-2011 15:46:31
Truncating Table N_KFF_GL_Acct
Initial Upload Started ...
Populating Table N_KFF_GL_Acct with data
Initial Upload insert complete ...
Initial Upload completed ...
Time Taken for gather stats for data cache table in secs : 655
Initial Upload completed ...
 
== Initial Upload ==16-DEC-2011 15:47:37
  - Cache Table Count : 639234 (AFTER)
===== = = = = = = = = = = = = = = = = = = = = = = = = = = = 16-DEC-2011 15:47:37
===== = = = = = = = = = = = = = = = = = = = = = = = = = = = 16-DEC-2011 15:47:37
Processing ITEM_LOC
  - Package name      : N_KFF_ITEM_LOC_PKG
  - Data Table name   : N_KFF_ITEM_LOC
  - Cache Table Count : 30845 (BEFORE)
== Initial Upload ==16-DEC-2011 15:47:37
Truncating Table N_KFF_GL_Acct
Initial Upload Started ...
Populating Table N_KFF_Item_Loc with data
Initial Upload insert complete ...
Initial Upload completed ...
Time Taken for gather stats for data cache table in secs : 37
Initial Upload completed ...
 
== Initial Upload ==16-DEC-2011 15:47:38
  - Cache Table Count : 30845 (AFTER)
===== = = = = = = = = = = = = = = = = = = = = = = = = = = = 16-DEC-2011 15:47:38
===== = = = = = = = = = = = = = = = = = = = = = = = = = = = 16-DEC-2011 15:47:38
Processing MTL_CAT
  - Package name      : N_KFF_MTL_CAT_PKG
  - Data Table name   : N_KFF_MTL_CAT
  - Cache Table Count : 1740 (BEFORE)
== Initial Upload ==16-DEC-2011 15:47:38
Truncating Table N_KFF_GL_Acct
Initial Upload Started ...
Populating Table N_KFF_Mtl_Cat with data
Initial Upload insert complete ...
Initial Upload completed ...
Time Taken for gather stats for data cache table in secs : 46
Initial Upload completed ...
 
== Initial Upload ==16-DEC-2011 15:47:40
  - Cache Table Count : 1740 (AFTER)
===== = = = = = = = = = = = = = = = = = = = = = = = = = = = 16-DEC-2011 15:47:40
===== = = = = = = = = = = = = = = = = = = = = = = = = = = = 16-DEC-2011 15:47:40
Processing SYS_ITEM
  - Package name      : N_KFF_SYS_ITEM_PKG
  - Data Table name   : N_KFF_SYS_ITEM
  - Cache Table Count : 690403 (BEFORE)
== Initial Upload ==16-DEC-2011 15:47:40
Truncating Table N_KFF_GL_Acct
Initial Upload Started ...
Populating Table N_KFF_Sys_Item with data
Initial Upload insert complete ...
Initial Upload completed ...
Time Taken for gather stats for data cache table in secs : 189
Initial Upload completed ...
 
== Initial Upload ==16-DEC-2011 15:48:35
  - Cache Table Count : 690403 (AFTER)
===== = = = = = = = = = = = = = = = = = = = = = = = = = = = 16-DEC-2011 15:48:35

Oracle PL/SQL: Dropping All Objects In An Oracle Schema

This blog post provides a simple piece of PL/SQL which will delete every object owned by the currently logged in user.

Here is the PL/SQL block;


declare
  v_ItemCount integer;
begin
  SELECT count(*)
    INTO v_ItemCount
    FROM ALL_OBJECTS AO
   WHERE AO.OWNER = USER
     AND AO.OBJECT_TYPE NOT IN ('INDEX')
     AND AO.OBJECT_NAME NOT LIKE 'BIN$%';
  while (v_ItemCount > 0) loop
    for v_Cmd in (SELECT 'drop ' || AO.OBJECT_TYPE || ' ' || AO.OBJECT_NAME ||
                         DECODE(AO.OBJECT_TYPE,
                                'TABLE',
                                ' CASCADE CONSTRAINTS',
                                '') as DROPCMD
                    FROM ALL_OBJECTS AO
                   WHERE AO.OWNER = USER
                     AND AO.OBJECT_TYPE NOT IN ('INDEX')
                     AND AO.OBJECT_NAME NOT LIKE 'BIN$%') loop
      begin
        execute immediate v_Cmd.dropcmd;
      exception
        when others then
          null; -- ignore errors
      end;
    end loop;
    SELECT count(*)
      INTO v_ItemCount
      FROM ALL_OBJECTS AO
     WHERE AO.OWNER = USER
       AND AO.OBJECT_TYPE NOT IN ('INDEX')
       AND AO.OBJECT_NAME NOT LIKE 'BIN$%';
  end loop;


  execute immediate 'purge recyclebin';
end;

The SQL was written against Oracle 10g (hence the "purge recyclebin" at the bottom and the exclusion of objects already in the recycle bin from the "drop" loop). You can monitor progress (and check to see if the code has stalled) by running the count SQL in another editor attached to the same schema.

Clearly a piece of SQL you should be very careful with!