Run Oracle select query as a cron job ? Oracle - force commit distributed transactions-CodePudding

I would like to setup a cron job to execute the result of my select query output,

schedule this "force commit" every day before my export job... without running it manually.

Precisely the below SQL Query & its outcome to be automated ..

1)
select 'commit force '''||local_tran_id||''';' from DBA_2PC_PENDING where state='prepared';                    

'COMMITFORCE'''||LOCAL_TRAN_ID||''';'
--------------------------------------
commit force '5.20.61510';
commit force '15.4.1124117';


SQL> commit force '5.20.61510';
commit force '15.4.1124117';
Commit complete.

CodePudding user response：

Just make a simple procedure with some dynamic plsql:

CREATE OR REPLACE PROCEDURE commit_prepared
AS
BEGIN
  FOR local_transaction IN (SELECT local_tran_id AS trans_id
                              FROM DBA_2PC_PENDING 
                             WHERE state = 'prepared')
  LOOP
    EXECUTE IMMEDIATE 'COMMIT FORCE '||local_transaction.trans_id;
  END LOOP;
END;
/

CodePudding user response：

Can i append something like below to my existing crontab based on your inputs @Gnqz, as i need this to run first before the actual job, is the code correct ?

function pending_dist_trans {
echo  "commit all pending distributed transaction before export" >> $SHLOG
${ORACLE_HOME}/bin/sqlplus -S /nolog   << EOF >> $SHLOG
conn /as sysdba
Set serveroutput on
BEGIN
  FOR local_transaction IN (SELECT local_tran_id AS trans_id
                              FROM DBA_2PC_PENDING 
                             WHERE state = 'prepared')LOOP
BEGIN
    EXECUTE IMMEDIATE 'COMMIT FORCE '||local_transaction.trans_id;
  END LOOP;
END;
/
exit;
EOF
}