{"id":127,"date":"2009-04-04T18:12:13","date_gmt":"2009-04-04T12:42:13","guid":{"rendered":"http:\/\/www.oratraining.com\/blog\/?p=127"},"modified":"2009-04-04T18:12:13","modified_gmt":"2009-04-04T12:42:13","slug":"cmclean-sql-script-to-cleanup-concurrent-manager-tables","status":"publish","type":"post","link":"https:\/\/www.oratraining.com\/blog\/2009\/04\/cmclean-sql-script-to-cleanup-concurrent-manager-tables\/","title":{"rendered":"cmclean.sql script to cleanup concurrent manager tables"},"content":{"rendered":"

Click here to download\u00a0cmclean.sql<\/a><\/h4>\n

REM
\nREM FILENAME
\nREM\u00a0\u00a0 cmclean.sql
\nREM DESCRIPTION
\nREM\u00a0\u00a0 Clean out the concurrent manager tables
\nREM NOTES
\nREM\u00a0\u00a0 Usage: sqlplus <apps_user\/apps_passwd> @cmclean
\nREM
\nREM
\nREM\u00a0\u00a0 $Id: cmclean.sql,v 1.4 2001\/04\/07 15:55:07 pferguso Exp $
\nREM
\nREM
\nREM +==============================+
\nset verify off;
\nset head off;
\nset timing off
\nset pagesize 1000
\ncolumn manager format a20 heading ‘Manager short name’
\ncolumn pid heading ‘Process id’
\ncolumn pscode format a12 heading ‘Status code’
\ncolumn ccode format a12 heading ‘Control code’
\ncolumn request heading ‘Request ID’
\ncolumn pcode format a6 heading ‘Phase’
\ncolumn scode format a6 heading ‘Status’
\nWHENEVER SQLERROR EXIT ROLLBACK;
\nDOCUMENT
\nWARNING : Do not run this script without explicit instructions
\nfrom Oracle Support
\n*** Make sure that the managers are shut down\u00a0\u00a0\u00a0\u00a0 ***
\n*** before running this script\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ***
\n*** If the concurrent managers are NOT shut down, ***
\n*** exit this script now !!\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ***
\n#
\naccept answer prompt ‘If you wish to continue type the word ”dual”: ‘
\nset feed off
\nselect null from &answer;
\nset feed on
\nREM\u00a0\u00a0\u00a0\u00a0 Update process status codes to TERMINATED
\nprompt
\nprompt\u00a0 ————————————————————————
\nprompt\u00a0 — Updating invalid process status codes in FND_CONCURRENT_PROCESSES
\nset feedback off
\nset head on
\nbreak on manager
\nSELECT\u00a0 concurrent_queue_name manager,
\nconcurrent_process_id pid,
\nprocess_status_code pscode
\nFROM\u00a0\u00a0\u00a0 fnd_concurrent_queues fcq, fnd_concurrent_processes fcp
\nWHERE\u00a0\u00a0 process_status_code not in (‘K’, ‘S’)
\nAND\u00a0\u00a0\u00a0\u00a0 fcq.concurrent_queue_id = fcp.concurrent_queue_id
\nAND\u00a0\u00a0\u00a0\u00a0 fcq.application_id = fcp.queue_application_id;
\nset head off
\nset feedback on
\nUPDATE\u00a0 fnd_concurrent_processes
\nSET\u00a0\u00a0\u00a0\u00a0 process_status_code = ‘K’
\nWHERE\u00a0\u00a0 process_status_code not in (‘K’, ‘S’);
\nREM\u00a0\u00a0\u00a0\u00a0 Set all managers to 0 processes
\nprompt
\nprompt\u00a0 ————————————————————————
\nprompt\u00a0 — Updating running processes in FND_CONCURRENT_QUEUES
\nprompt\u00a0 — Setting running_processes = 0 and max_processes = 0 for all managers
\nUPDATE\u00a0 fnd_concurrent_queues
\nSET\u00a0\u00a0\u00a0\u00a0 running_processes = 0, max_processes = 0;
\nREM\u00a0\u00a0\u00a0\u00a0 Reset control codes
\nprompt
\nprompt\u00a0 ————————————————————————
\nprompt\u00a0 — Updating invalid control_codes in FND_CONCURRENT_QUEUES
\nset feedback off
\nset head on
\nSELECT\u00a0 concurrent_queue_name manager,
\ncontrol_code ccode
\nFROM\u00a0\u00a0\u00a0 fnd_concurrent_queues
\nWHERE\u00a0\u00a0 control_code not in (‘E’, ‘R’, ‘X’)
\nAND\u00a0\u00a0\u00a0\u00a0 control_code IS NOT NULL;
\nset feedback on
\nset head off
\nUPDATE\u00a0 fnd_concurrent_queues
\nSET\u00a0\u00a0\u00a0\u00a0 control_code = NULL
\nWHERE\u00a0\u00a0 control_code not in (‘E’, ‘R’, ‘X’)
\nAND\u00a0\u00a0\u00a0\u00a0 control_code IS NOT NULL;
\nREM\u00a0\u00a0\u00a0\u00a0 Also null out target_node for all managers
\nUPDATE\u00a0 fnd_concurrent_queues
\nSET\u00a0\u00a0\u00a0\u00a0 target_node = null;
\nREM\u00a0\u00a0\u00a0\u00a0 Set all ‘Terminating’ requests to Completed\/Error
\nREM\u00a0\u00a0\u00a0\u00a0 Also set Running requests to completed, since the managers are down
\nprompt
\nprompt\u00a0 ————————————————————————
\nprompt\u00a0 — Updating any Running or Terminating requests to Completed\/Error
\nset feedback off
\nset head on
\nSELECT\u00a0 request_id request,
\nphase_code pcode,
\nstatus_code scode
\nFROM\u00a0\u00a0\u00a0 fnd_concurrent_requests
\nWHERE\u00a0\u00a0 status_code = ‘T’ OR phase_code = ‘R’
\nORDER BY request_id;
\nset feedback on
\nset head off
\nUPDATE\u00a0 fnd_concurrent_requests
\nSET\u00a0\u00a0\u00a0\u00a0 phase_code = ‘C’, status_code = ‘E’
\nWHERE\u00a0\u00a0 status_code =’T’ OR phase_code = ‘R’;
\nREM\u00a0\u00a0\u00a0\u00a0 Set all Runalone flags to ‘N’
\nREM\u00a0\u00a0\u00a0\u00a0 This has to be done differently for Release 10
\nprompt
\nprompt\u00a0 ————————————————————————
\nprompt\u00a0 — Updating any Runalone flags to ‘N’
\nprompt
\nset serveroutput on
\nset feedback off
\ndeclare
\nc\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 pls_integer := dbms_sql.open_cursor;
\nupd_rows\u00a0 pls_integer;
\nvers\u00a0\u00a0\u00a0\u00a0\u00a0 varchar2(50);
\ntbl\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 varchar2(50);
\ncol\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 varchar2(50);
\nstatement varchar2(255);
\nbegin
\nselect substr(release_name, 1, 2)
\ninto\u00a0\u00a0 vers
\nfrom fnd_product_groups;
\nif vers >= 11 then
\ntbl := ‘fnd_conflicts_domain’;
\ncol := ‘runalone_flag’;
\nelse
\ntbl := ‘fnd_concurrent_conflict_sets’;
\ncol := ‘run_alone_flag’;
\nend if;
\nstatement := ‘update ‘ || tbl || ‘ set ‘ || col || ‘=”N” where ‘ || col || ‘ = ”Y”’;
\ndbms_sql.parse(c, statement, dbms_sql.native);
\nupd_rows := dbms_sql.execute(c);
\ndbms_sql.close_cursor(c);
\ndbms_output.put_line(‘Updated ‘ || upd_rows || ‘ rows of ‘ || col || ‘ in ‘ || tbl || ‘ to ”N”’);
\nend;
\n\/<\/p>\n

prompt<\/p>\n

prompt\u00a0 ————————————————————————<\/p>\n

prompt\u00a0 Updates complete.
\nprompt\u00a0 Type commit now to commit these updates, or rollback to cancel.
\nprompt\u00a0 ————————————————————————<\/p>\n

prompt<\/p>\n

set feedback on<\/p>\n

REM\u00a0 <= Last REM statment —————————————————–<\/p>\n","protected":false},"excerpt":{"rendered":"

Click here to download\u00a0cmclean.sql REM REM FILENAME REM\u00a0\u00a0 cmclean.sql REM DESCRIPTION REM\u00a0\u00a0 Clean out the concurrent manager tables REM NOTES REM\u00a0\u00a0 Usage: sqlplus <apps_user\/apps_passwd> @cmclean REM REM REM\u00a0\u00a0 $Id: cmclean.sql,v 1.4 2001\/04\/07 15:55:07 pferguso Exp $ REM REM REM +==============================+ set verify off; set head off; set timing off set pagesize 1000 column manager format […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[29,4,5,6],"tags":[204,196,98,198],"class_list":["post-127","post","type-post","status-publish","format-standard","hentry","category-concurrent-manager","category-oracle","category-applications","category-oracle-applications-dba","tag-concurrent-manager","tag-oracle","tag-oracle-applications","tag-oracle-applications-dba"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/www.oratraining.com\/blog\/wp-json\/wp\/v2\/posts\/127","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.oratraining.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.oratraining.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.oratraining.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.oratraining.com\/blog\/wp-json\/wp\/v2\/comments?post=127"}],"version-history":[{"count":0,"href":"https:\/\/www.oratraining.com\/blog\/wp-json\/wp\/v2\/posts\/127\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.oratraining.com\/blog\/wp-json\/wp\/v2\/media?parent=127"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.oratraining.com\/blog\/wp-json\/wp\/v2\/categories?post=127"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.oratraining.com\/blog\/wp-json\/wp\/v2\/tags?post=127"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}