PLSQL: How to restore the stats on a table

Starting with Oracle 10g, if you need to modify/restore the statistics of a table to an older date statistics, because of a change in the execution plan or to check the performance, you could use dbms_stats package to restore the stats. You could also do an export and import, but for that, you should have exported the stats before the gather stats is run.

From Oracle 10g, when statistics are gathered for a table, the old statistics are retained for a pre-defined time, and you can restore the old ones.

Inorder to check for how long the stats are retained, the below queries would help.

The below query will give the number of days the stats are retained.

SELECT DBMS_STATS.get_stats_history_retention
FROM   dual;

The below query will return the date statistics have been purged. Only the stats from the dates after this date is available.

SELECT DBMS_STATS.get_stats_history_availability
FROM   dual;

The below query will give the statistics history for a given table.

SELECT table_name
      ,stats_update_time
FROM   dba_tab_stats_history
WHERE  table_name = 'XXAJ_TEST';
 

You can use  DBMS_STATS.RESTORE_TABLE_STATS ('<schema>','<table_name>',date) to restore the table stats.

BEGIN
 
  DBMS_STATS.restore_table_stats(ownname         => 'XXAJ'
                                ,tabname         => 'XXAJ_TEST'
                                ,as_of_timestamp => '18-JUL-19 12.01.20.766591 PM +08:00');
 
END;

Ref: How to Restore Oracle Optimizer Statistics (Doc ID 452011.1)




Feel free to point out if anything is wrong/missing in this post.

Oracle Apps: Script to unapply Credit memo

This is some sample code which we have used in Release 12.1.3 instance to unapply Credit Memo  in Bulk.

This code expects you already have the Customer Account Number, Credit Memo and Debit Memo/Invoice details. You just need to replace the SELECT from DUAL section in the cursor and replace with your values.

DECLARE
   CURSOR c_get_trx IS 
   SELECT t.account_number          account_number,
          TO_NUMBER(t.amt)          amount,
          t.field5                  cm_trx_number,
          t.cm_date                 cm_trx_date,
          psc.customer_trx_id       cm_trx_id,
          psc.payment_schedule_id   cm_ps_id,
          psc.amount_due_remaining  cm_amt_remaining,
          psc.status                cm_status, 
          adj.customer_trx_id       inv_trx_id,
          adj.trx_rec_number        inv_trx_number
   FROM   (SELECT '2412315'                             "ACCOUNT_NUMBER"
                   ,'100.50'                             "AMT"
                   ,'95000'                              "DM_NUMBER"
                   ,TO_DATE('01-Jan-2018','DD-Mon-YYYY') "DM_DATE"
                   ,'130000'                             "CM_NUMBER"
                   ,TO_DATE('05-Jan-2018','DD-Mon-YYYY') "CM_DATE"
            FROM   dual) t ,
          hz_cust_accounts     ca,
         ar_payment_schedules psc,
         ar_app_adj_v         adj
   WHERE ca.account_number  = t.account_number
   AND   psc.customer_id    = ca.cust_account_id
   AND   psc.class          = 'CM'
   AND   psc.trx_number     = t.field5
   AND   TRUNC(psc.trx_date)= t.cm_date
   AND   adj.payment_schedule_id = psc.payment_schedule_id
   AND   psc.status = 'CL';
 
  v_return_status           VARCHAR2(1);
  p_count                   NUMBER;
  v_msg_count               NUMBER;
  v_msg_data                VARCHAR2(2000);
 
  v_cm_unapp_rec            AR_CM_API_PUB.CM_UNAPP_REC_TYPE;
  v_cm_id                   NUMBER(15) DEFAULT 1605;
  v_inv_id                  NUMBER(15) DEFAULT 1604;
  v_gl_date                 DATE       DEFAULT SYSDATE;
 
  v_context                 VARCHAR2(2);
 
BEGIN
   -- Apps Initialize
   MO_GLOBAL.SET_POLICY_CONTEXT('S', 0);
   fnd_global.apps_initialize (1000,50000,222);
 
   FOR r_rec IN c_get_trx LOOP
      DBMS_OUTPUT.put_line('Processing account number ' || r_rec.account_number || ': Trying to un apply credit memo number: ' || r_rec.cm_trx_number ||' from debit memo number: ' || r_rec.dm_trx_number);
  
      BEGIN
 
     v_cm_unapp_rec.cm_customer_trx_id       := r_rec.cm_trx_id;
         v_cm_unapp_rec.inv_customer_trx_id  := r_rec.inv_trx_id;
         v_cm_unapp_rec.reversal_gl_date     := TRUNC(SYSDATE);
 
        /* Invoking credit memo unapplication api */
        ar_cm_api_pub.unapply_on_account(
                  p_api_version                => 1.0
                 ,p_init_msg_list              => FND_API.G_FALSE
                 ,p_commit                     => FND_API.G_FALSE
                 ,p_cm_unapp_rec               => v_cm_unapp_rec
                 ,x_return_status              => v_return_status
                 ,x_msg_count                  => v_msg_count
                 ,x_msg_data                   => v_msg_data);

        DBMS_OUTPUT.put_line('Message count:' || v_msg_count);
 
        IF v_msg_count = 1 THEN
          DBMS_OUTPUT.put_line('Message Data: '||v_msg_data);
        ELSIF v_msg_count > 1 THEN
          LOOP
            p_count := p_count+1;
            v_msg_data := FND_MSG_PUB.Get(FND_MSG_PUB.G_NEXT,FND_API.G_FALSE);
            IF v_msg_data IS NULL THEN
              EXIT;
            END IF;
            DBMS_OUTPUT.put_line('Message Data : ' || p_count ||' ---'||v_msg_data);
          END LOOP;
        END IF; 
 
      EXCEPTION
         WHEN OTHERS THEN
            DBMS_OUTPUT.put_line('*   ERROR: Unable to apply credit memo ' || r_rec.cm_trx_number ||': ' || app_exception.get_type||':'||app_exception.get_code||':'||app_exception.get_text);
      END;
   END LOOP;
   COMMIT;
END;
 

Feel free to point out if anything is wrong/missing in this post.