Edit D:\app\Administrator\product\11.2.0\dbhome_1\mgw\admin\catnomgw.sql
Rem Rem $Header: catnomgw.sql 21-nov-2006.14:22:51 chall Exp $ Rem Rem catnomgw.sql Rem Rem Copyright (c) 2001, 2006, Oracle. All rights reserved. Rem Rem NAME Rem catnomgw.sql - catalog/uninstall script for Messaging Gateway Rem Rem DESCRIPTION Rem Drops database dictionary and schema objects for the Oracle Rem Messaging Gateway (MGW) component. This drops the tables, views, Rem packages, object types, synonyms, and so on. Rem Rem NOTES Rem Run this script AS SYSDBA Rem Rem !! --- Read This --- !! --- Read This --- !! --- Read This --- !! Rem Rem Messaging Gateway agent must not be running when this script is run. Rem Rem All AQ queues and queue tables using MGW payload types should be Rem dropped before this script is run. This script uses FORCE to drop Rem the MGW types, and any AQ queue table using MGW types will be invalid Rem after running this script. dbms_aqadm.drop_queue_table will be able Rem to drop the invalid queue tables, they might be hard to find since Rem the queue tables are no longer listed by DBS_QUEUE_TABLE view. Rem Rem The following query (run as SYS) shows all AQ queue tables whose Rem payload is a MGW message type. Rem Rem select owner, queue_table, object_type from dba_queue_tables Rem where object_type like '%SYS.MGW%_T'; Rem Rem MODIFIED (MM/DD/YY) Rem chall 11/21/06 - drop mgwi_crypto package Rem chall 09/29/06 - drop mgw_agent_options view, table Rem chall 06/08/06 - drop Scheduler objects Rem chall 04/26/06 - propagation job support (11g feature) Rem chall 01/12/06 - explicitely drop notify and log queue tables Rem chall 05/19/04 - MSMQ support Rem chall 12/16/03 - remove obsolete (commented out) MSMQ code Rem chall 07/29/03 - no startup trigger for 10i (bug 2999689) Rem chall 12/03/02 - drop database STARTUP and SHUTDOWN triggers Rem chall 11/12/02 - remove reference to never used mgw_notify synonym Rem chall 10/28/02 - drop FORCE for object types Rem Rem ========================================================================== -- Q: Run above query and refuse to run if AQ payloads using MGW types? -- For now, always do uninstall tasks. User is reponsible for reading and -- doing clean up prior to running script. Must also shutdown MGW agent. -- -- Indicate MGW component being removed -- execute dbms_registry.removing('MGW'); rem ======================================================================== rem Basic cleanup tasks via the TSK_MGW_UNINSTALL task. rem Removes AQ log queues, notification queus, Scheduler objects, etc. rem This will fail if package is invalid, not exist, etc. begin mgwi_admin.task(MGWI_ADMIN.TSK_MGW_UNINSTALL); end; / rem ======================================================================== rem rem Drop the AQ queue tables used for admin notification queues and rem propagation log queues. This is done as part of TSK_MGW_UNINSTALL but rem repeated here in case the PL/SQL package is invalid. rem rem Multiple agent support added for 11g release but no extra work needed rem since the same queue tables are used for default agent and named agents. rem rem ======================================================================== declare PROCEDURE drop_qtable( p_qtable IN VARCHAR2 ) IS BEGIN dbms_aqadm.drop_queue_table(queue_table=>p_qtable, force=>TRUE); EXCEPTION WHEN OTHERS THEN IF SQLCODE = -24002 THEN -- no queue table exists null; --%dbms_output.put_line('drop qtable: '||p_qtable||' : '||SQLERRM); ELSE null; dbms_output.put_line('drop qtable: '||p_qtable||' : '||SQLERRM); --%RAISE; END IF; END drop_qtable; begin -- queue table for admin notification queues drop_qtable('SYS.MGW_NOTIFY_QUEUE_QT'); -- queue table for AQ native and AQ JMS propagation log queues drop_qtable('SYS.MGW_SEND_LOG_QT'); drop_qtable('SYS.MGW_RECV_LOG_QT'); drop_qtable('SYS.MGW_JMS_SEND_LOG_QT'); drop_qtable('SYS.MGW_JMS_RECV_LOG_QT'); end; / rem ======================================================================== rem rem Drop the Scheduler objects created by Messaging Gateway. This is done rem as part of TSK_MGW_UNINSTALL but repeated here in case the PL/SQL rem package is invalid. rem rem a) Drop Scheduler job and job class used by named agents and the rem default agent. rem b) Drop Scheduler program used for MGW startup job procedure. rem rem ======================================================================== -- Drop a Scheduler object -- -- p_which : 1=job, 2=job class, 3=program -- p_name : name of object to be dropped -- p_force : TRUE for a forced drop, FALSE for a non-forced drop -- create or replace procedure mgwmig_drop_scheduler_obj ( p_which in pls_integer, p_name in varchar2, p_force in boolean default TRUE ) is begin begin if p_which = 1 then -- ORA-27475 if no such job dbms_scheduler.drop_job(p_name, p_force); elsif p_which = 2 then -- ORA-27476 if no such job class dbms_scheduler.drop_job_class(p_name, p_force); elsif p_which = 3 then -- ORA-27476 if no such program dbms_scheduler.drop_program(p_name, p_force); else dbms_output.put_line( 'mgwmig_drop_scheduler_obj: unknown value for p_which; '||p_which); end if; exception when others then if SQLCODE = -27475 then null; elsif SQLCODE = -27476 then null; else dbms_output.put_line( 'mgwmig_drop_scheduler_obj: name='||p_name||', which='||p_which); dbms_output.put_line(SQLERRM); end if; end; end mgwmig_drop_scheduler_obj; / -- Drop Scheduler objects created by Messaging Gateway. -- declare -- query for all the named (non-default) agents CURSOR mgw_named_agents_c IS select agent_name from sys.mgw$_gateway where nvl(agent_name,'DEFAULT_AGENT') != 'DEFAULT_AGENT'; begin -- drop Scheduler job and job class for named agents for na_rec in mgw_named_agents_c loop mgwmig_drop_scheduler_obj(1, 'SYS.MGW_JOB_'||na_rec.agent_name); mgwmig_drop_scheduler_obj(2, 'SYS.MGW_JOBCLS_'||na_rec.agent_name); end loop; -- drop Scheduler job and job class for default agent mgwmig_drop_scheduler_obj(1, 'SYS.MGW_JOB_DEFAULT_AGENT'); mgwmig_drop_scheduler_obj(2, 'SYS.MGW_JOBCLS_DEFAULT_AGENT'); -- drop agent startup program mgwmig_drop_scheduler_obj(3, 'SYS.MGW_STARTUP_PROGRAM'); end; / drop procedure mgwmig_drop_scheduler_obj; rem ======================================================================== rem rem Drop MGW Entities rem rem ======================================================================== -- Triggers drop trigger mgw_dbshutdown_trig; -- Roles drop role mgw_administrator_role; drop role mgw_agent_role; -- Views drop view mgw_agent_options; drop public synonym mgw_agent_options; drop view mgw_gateway; drop public synonym mgw_gateway; drop view mgw_links; drop public synonym mgw_links; drop view mgw_mqseries_links; drop public synonym mgw_mqseries_links; drop view mgw_tibrv_links; drop public synonym mgw_tibrv_links; drop view mgw_msmq_links; drop public synonym mgw_msmq_links; drop view mgw_foreign_queues; drop public synonym mgw_foreign_queues; drop view mgw_subscribers; drop public synonym mgw_subscribers; drop view mgw_schedules; drop public synonym mgw_schedules; drop view mgw_jobs; drop public synonym mgw_jobs; -- Tables drop table mgw$_config; drop table mgw$_agent_options; drop table mgw$_gateway; drop table mgw$_mqseries_links; drop table mgw$_tibrv_links; drop table mgw$_msmq_links; drop table mgw$_links; drop table mgw$_foreign_queues; drop table mgw$_subscribers; drop table mgw$_schedules; -- Packages drop package dbms_mgwadm; drop public synonym dbms_mgwadm; drop package dbms_mgwmsg; drop public synonym dbms_mgwmsg; drop package mgwi_admin; drop package mgwi_crypto; drop package mgwi_const; drop package mgw_aqdriver; drop package mgw_notify; drop package mgwi_reg; -- Types drop type mgwi_msglink force; drop type mgwi_foreignqueue force; drop type mgwi_subscriber force; drop type mgwi_schedule force; drop type mgw_aq_message_props_t force; drop type mgw_aq_dequeue_options_t force; drop type mgw_aq_enqueue_options_t force; drop type mgw_notif_msg force; drop type mgw_notif_element_list force; drop type mgw_notif_element force; drop type mgw_mqseries_properties force; drop type mgw_tibrv_properties force; drop type mgw_msmq_properties force; drop type mgw_properties force; drop type mgw_property force; drop type mgw_basic_msg_t force; drop type mgw_text_value_t force; drop type mgw_raw_value_t force; drop type mgw_name_value_array_t force; drop type mgw_name_value_t force; drop type mgw_tibrv_msg_t force; drop type mgw_tibrv_ifields_t force; drop type mgw_tibrv_ifield_t force; drop type mgw_tibrv_field_t force; drop type mgw_number_array_t force; -- Agent library. drop library dbms_mgwadm_agent_lib; drop public database link mgw_agent; -- -- Other non-public/non-supported MGW-related objects. -- begin execute immediate 'drop table sys.mgw$_initfile'; exception when others then if sqlcode = -942 then null; else raise; end if; end; / --% begin --% execute immediate 'drop table sys.mgw$_trace_config'; --%exception when others then --% if sqlcode = -942 then null; --% else raise; end if; --%end; --%/ --%begin --% execute immediate 'drop package sys.mgwi_trace'; --%exception when others then --% if sqlcode = -4043 then null; --% else raise; end if; --%end; --%/ -- -- Indicate MGW component been removed -- execute dbms_registry.removed('MGW'); Rem === End file: catnomgw.sql ===============================================
Ms-Dos/Windows
Unix
Write backup
jsp File Browser version 1.2 by
www.vonloesch.de