rem 
rem $Header: catrepr.sql 26-feb-96.19:14:42 gpongrac Exp $ 
rem 
Rem  Copyright (c) 1995, 1996, 1997, 1998 by Oracle Corporation 
Rem    NAME
Rem      catrepr.sql - CATalog REPlication Remove
Rem    DESCRIPTION
Rem      Removes all replication tables, views, packages
Rem      synonyms, and sequences which were installed for
Rem      replication option from a database (check all files loaded in
Rem      catrep.sql to determine what should be dropped)
Rem
Rem    RETURNS
Rem 
Rem    NOTES
Rem      This script must be updated eveytime Replication is modified.
Rem      It does not include snapshot related stuff which is under 
Rem      distributed option.
Rem 
Rem      Must connect as INTERNAL or SYS to run this script.
Rem
Rem      When a new table is added to repcat, it must be dropped in this
Rem      script for un-installation. ANY ASSOCIATED OBJECTS (E.G., VIEWS,
Rem      SYNONYMS) MUST ALSO BE DROPPED.
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem     wesmith    11/11/98  - added package dbms_repcat_rgt_exp_site_inst     
Rem     celsbern   10/22/98  - adde dbms_repcat_instantiate                    
Rem     celsbern   09/28/98 -  added delete of new all_ and user_ views for tem
Rem     celsbern   09/18/98 -  added dbms_repcat_rgt_cust2
Rem     liwong     06/22/98  - drop dbms_repcat_internal2                      
Rem     celsbern   06/21/98  - removed repcat$_template_output table.          
Rem     celsbern   05/12/98 -  removed dbmsobjg and dbmsobjgwrapper from script
Rem     celsbern   05/11/98 -  updated for refresh group template tables/packag
Rem     liwong     04/24/98  - Fix after flavor support                        
Rem     liwong     04/17/97 -  Fix after AQ integration
Rem     liwong     10/15/96 -  Added SQLs to remove Rep3 (in V8) related
Rem                            tables, views, etc
Rem     sjain      10/10/95 -  Name changes of dba_repcat etc.
Rem     hasun      03/06/95 -  Creation
Rem

------------------------------------------------------------------------------
-- Note:
--     1. dbms_snapshot uses deftrandest which requires def$_destination
--        and def$_error. So, we should not drop these two
--        tables and related views
--     2. many def% views, synonyms and tables are present before loading
--        of catrep.sql. So, if we remove those views, synonyms and tables
--        during deinstalling of catrep.sql, and reloading catrep.sql 
--        is not sufficient to reinstall replication option. Hence,
--          a. the tables, views, synonyms created before loading catrep.sql
--             should not be dropped, e.g., def$_% tables, def% views and
--             synonyms
--          b. the views which were replaced after loading catrep.sql should
--             be restored, e.g., prvtdfrd.plb, deftrandest, defcalldest
------------------------------------------------------------------------------

---------------------------------
-- RepCat Packages
---------------------------------
DROP PACKAGE sys.dbms_repcat_output
/
DROP PACKAGE sys.dbms_repcat_rgt_utl
/ 
DROP PACKAGE sys.dbms_repcat_rgt_cust 
/ 
DROP PACKAGE sys.dbms_repcat_rgt_cust2 
/ 
DROP PACKAGE sys.dbms_repcat_rgt_chk
/ 
DROP PACKAGE sys.dbms_repcat_rgt_alt
/ 
DROP PACKAGE sys.dbms_repcat_rgt_exp
/ 
DROP PACKAGE sys.dbms_repcat_instantiate
/
DROP PACKAGE sys.dbms_repcat_rgt
/ 
DROP PACKAGE sys.dbms_offline_utl 
/ 
DROP PACKAGE sys.dbms_offline_internal
/ 
DROP PACKAGE sys.dbms_offline_og
/ 
DROP PACKAGE sys.dbms_rectifier_friends 
/ 
DROP PACKAGE sys.dbms_rectifier_diff
/ 
DROP PACKAGE sys.dbms_repcat_mig_internal 
/ 
DROP PACKAGE sys.dbms_repcat_mig
/ 
DROP PACKAGE sys.dbms_repcat_fla_mas
/ 
DROP PACKAGE sys.dbms_repcat_validate 
/ 
DROP PACKAGE sys.dbms_repcat_utl4 
/ 
DROP PACKAGE sys.dbms_repcat_utl3 
/ 
DROP PACKAGE sys.dbms_repcat_utl2 
/ 
DROP PACKAGE sys.dbms_repcat_rpc_utl
/ 
DROP PACKAGE sys.dbms_repcat_rpc
/ 
DROP PACKAGE sys.dbms_repcat_mas
/ 
DROP PACKAGE sys.dbms_repcat_conf 
/ 
DROP PACKAGE sys.dbms_repcat_auth 
/ 
DROP PACKAGE system.dbms_repcat_auth
/ 
DROP PACKAGE sys.dbms_internal_repcat
/ 
DROP PACKAGE sys.dbms_repcat
/ 
DROP PACKAGE sys.dbms_offline_snapshot
/ 
DROP PACKAGE sys.dbms_repcat_fla_utl
/ 
DROP PACKAGE sys.dbms_repcat_fla
/ 
DROP PACKAGE sys.dbms_repcat_admin
/ 
DROP PACKAGE sys.dbms_repcat_utl
/ 
DROP PACKAGE sys.dbms_repcat_untrusted
/ 
DROP PACKAGE sys.dbms_repcat_sna_utl
/ 
DROP PACKAGE sys.dbms_repcat_sna
/ 
DROP PACKAGE sys.dbms_repcat_decl 
/ 
DROP PACKAGE sys.dbms_defer_repcat
/ 
DROP PACKAGE sys.dbms_asyncrpc_push 
/ 
DROP PACKAGE sys.dbms_defer_enq_utl 
/ 
DROP PACKAGE sys.dbms_defer_query_utl 
/ 
DROP PACKAGE sys.dbms_maint_gen 
/ 
DROP PACKAGE sys.dbms_defergen_wrap 
/ 
DROP PACKAGE sys.dbms_defergen_internal 
/ 
DROP PACKAGE sys.dbms_defergen_priority 
/ 
DROP PACKAGE sys.dbms_defergen_resolution 
/ 
DROP PACKAGE sys.dbms_defergen_audit
/ 
DROP PACKAGE sys.dbms_defergen_lob
/ 
DROP PACKAGE sys.dbms_defergen_util 
/ 
DROP PACKAGE sys.dbms_defergen
/ 
DROP PACKAGE sys.dbms_reputil2
/ 
DROP PACKAGE sys.dbms_reputil 
/ 
DROP PACKAGE sys.dbms_repcat_internal 
/ 
DROP PACKAGE sys.dbms_repcat_internal_package 
/ 
DROP PACKAGE sys.dbms_repcat_cache
/ 
---------------------------------
--- RepCat Tables
---------------------------------
DROP TABLE system.repcat$_audit_column
/
DROP TABLE system.repcat$_audit_attribute
/
DROP TABLE system.repcat$_parameter_column
/
DROP TABLE system.repcat$_resol_stats_control
/
DROP TABLE system.repcat$_resolution_statistics
/
DROP TABLE system.repcat$_resolution
/
DROP TABLE system.repcat$_resolution_method
/
DROP TABLE system.repcat$_conflict
/
DROP TABLE system.repcat$_grouped_column
/
DROP TABLE system.repcat$_column_group
/
DROP TABLE system.repcat$_priority
/
DROP TABLE system.repcat$_priority_group
/
DROP TABLE system.repcat$_ddl
/
DROP TABLE system.repcat$_repcatlog
/
DROP TABLE system.repcat$_repprop
/
DROP TABLE system.repcat$_generated
/
DROP TABLE system.repcat$_object_parms
/
DROP TABLE system.repcat$_user_parm_values
/
DROP TABLE system.repcat$_template_parms
/
DROP TABLE system.repcat$_template_sites
/
DROP TABLE system.repcat$_template_objects
/
DROP TABLE system.repcat$_key_columns
/
DROP TABLE system.repcat$_repcolumn
/
DROP TABLE system.repcat$_repobject
/
DROP TABLE system.repcat$_repschema
/
DROP TABLE system.repcat$_snapgroup
/
DROP TABLE system.repcat$_runtime_parms
/
DROP TABLE system.repcat$_user_authorizations
/
DROP TABLE system.repcat$_refresh_templates
/
DROP TABLE system.repcat$_repgroup_privs
/
DROP TABLE system.repcat$_flavor_objects
/
DROP TABLE system.repcat$_flavors
/
DROP TABLE system.repcat$_repcat
/

------------------------------------
--- RepCat Views
------------------------------------
DROP VIEW repcat_repcat
/
DROP VIEW user_repcat
/
DROP VIEW all_repcat
/
DROP VIEW dba_repcat
/
DROP VIEW user_repgroup
/
DROP VIEW all_repgroup
/
DROP VIEW dba_repgroup
/
DROP VIEW repcat_repschema
/
DROP VIEW user_repschema
/
DROP VIEW all_repschema
/
DROP VIEW dba_repschema
/
DROP VIEW repcat_repobject
/
DROP VIEW user_repobject
/
DROP VIEW all_repobject
/
DROP VIEW dba_repobject
/
DROP VIEW repcat_generated
/
DROP VIEW user_repgenerated
/
DROP VIEW all_repgenerated
/
DROP VIEW dba_repgenerated
/
DROP VIEW repcat_repprop
/
DROP VIEW user_repprop
/
DROP VIEW all_repprop
/
DROP VIEW dba_repprop
/
DROP VIEW repcat_repcatlog
/
DROP VIEW user_repcatlog
/
DROP VIEW all_repcatlog
/
DROP VIEW dba_repcatlog
/
DROP VIEW user_repkey_columns
/
DROP VIEW all_repkey_columns
/
DROP VIEW dba_repkey_columns
/
DROP VIEW user_repddl
/
DROP VIEW all_repddl
/
DROP VIEW dba_repddl
/
DROP VIEW dba_reppriority
/
DROP VIEW all_reppriority
/
DROP VIEW user_reppriority
/
DROP VIEW dba_reppriority_group
/
DROP VIEW all_reppriority_group
/
DROP VIEW user_reppriority_group
/
DROP VIEW dba_repcolumn_group
/
DROP VIEW all_repcolumn_group
/
DROP VIEW user_repcolumn_group
/
DROP VIEW dba_repgrouped_column
/
DROP VIEW all_repgrouped_column
/
DROP VIEW user_repgrouped_column
/
DROP VIEW dba_repconflict
/
DROP VIEW all_repconflict
/
DROP VIEW user_repconflict
/
DROP VIEW dba_represolution_method
/
DROP VIEW all_represolution_method
/
DROP VIEW user_represolution_method
/
DROP VIEW dba_represolution
/
DROP VIEW all_represolution
/
DROP VIEW user_represolution
/
DROP VIEW dba_represolution_statistics
/
DROP VIEW all_represolution_statistics
/
DROP VIEW user_represolution_statistics
/
DROP VIEW dba_represol_stats_control
/
DROP VIEW all_represol_stats_control
/
DROP VIEW user_represol_stats_control
/
DROP VIEW dba_repparameter_column
/
DROP VIEW all_repparameter_column
/
DROP VIEW user_repparameter_column
/
DROP VIEW dba_repaudit_attribute
/
DROP VIEW all_repaudit_attribute
/
DROP VIEW user_repaudit_attribute
/
DROP VIEW dba_repaudit_column
/
DROP VIEW all_repaudit_column
/
DROP VIEW user_repaudit_column
/
DROP VIEW REPCAT$_CDEF
/
DROP VIEW DBA_REPFLAVORS
/
DROP VIEW ALL_REPFLAVORS
/
DROP VIEW USER_REPFLAVORS
/
DROP VIEW dba_repflavor_objects
/
DROP VIEW all_repflavor_objects
/
DROP VIEW "_ALL_REPFLAVOR_OBJECTS"
/
DROP VIEW user_repflavor_objects
/
DROP VIEW dba_repflavor_columns
/
DROP VIEW all_repflavor_columns
/
DROP VIEW user_repflavor_columns
/

--
-- additional RepCat views in Rep3
--
DROP VIEW all_repsites
/
DROP VIEW dba_repsites
/
DROP VIEW user_repsites
/
DROP VIEW user_repcolumn
/
DROP VIEW all_repcolumn
/
DROP VIEW dba_repcolumn
/
DROP VIEW user_repgenobjects
/
DROP VIEW all_repgenobjects
/
DROP VIEW dba_repgenobjects
/
DROP VIEW all_repgroup_privileges
/
DROP VIEW dba_repgroup_privileges
/
DROP VIEW user_repgroup_privileges
/
DROP VIEW all_repcat_refresh_templates
/  
DROP VIEW all_repcat_template_objects
/   
DROP VIEW all_repcat_template_parms
/     
DROP VIEW all_repcat_template_sites
/     
DROP VIEW all_repcat_user_authorizations
/
DROP VIEW all_repcat_user_parm_values
/   
DROP VIEW dba_repcat_refresh_templates
/  
DROP VIEW dba_repcat_template_objects
/   
DROP VIEW dba_repcat_template_parms
/     
DROP VIEW dba_repcat_template_sites
/     
DROP VIEW dba_repcat_user_authorizations
/
DROP VIEW dba_repcat_user_parm_values
/   
DROP VIEW user_repcat_refresh_templates
/ 
DROP VIEW user_repcat_template_objects
/  
DROP VIEW user_repcat_template_parms
/    
DROP VIEW user_repcat_template_sites
/    
DROP VIEW user_repcat_user_authorization
/
DROP VIEW user_repcat_user_parm_values
/  

---------------------------------
--- RepCat Synonyms
---------------------------------
DROP PUBLIC SYNONYM user_repcat
/
DROP PUBLIC SYNONYM all_repcat
/
DROP PUBLIC SYNONYM user_repgroup
/
DROP PUBLIC SYNONYM all_repgroup
/
DROP PUBLIC SYNONYM user_repschema
/
DROP PUBLIC SYNONYM all_repschema
/
DROP PUBLIC SYNONYM user_repobject
/
DROP PUBLIC SYNONYM all_repobject
/
DROP PUBLIC SYNONYM user_repprop
/
DROP PUBLIC SYNONYM all_repprop
/
DROP PUBLIC SYNONYM user_repkey_columns
/
DROP PUBLIC SYNONYM all_repkey_columns
/
DROP PUBLIC SYNONYM user_repgenerated
/
DROP PUBLIC SYNONYM all_repgenerated
/
DROP PUBLIC SYNONYM user_repcatlog
/
DROP PUBLIC SYNONYM all_repcatlog
/
DROP PUBLIC SYNONYM user_repddl
/
DROP PUBLIC SYNONYM all_repddl
/
DROP PUBLIC SYNONYM all_reppriority_group
/
DROP PUBLIC SYNONYM user_reppriority_group
/
DROP PUBLIC SYNONYM all_reppriority
/
DROP PUBLIC SYNONYM user_reppriority
/
DROP PUBLIC SYNONYM all_repcolumn_group
/
DROP PUBLIC SYNONYM user_repcolumn_group
/
DROP PUBLIC SYNONYM all_repgrouped_column
/
DROP PUBLIC SYNONYM user_repgrouped_column
/
DROP PUBLIC SYNONYM all_repconflict
/
DROP PUBLIC SYNONYM user_repconflict
/
DROP PUBLIC SYNONYM all_represolution_method
/
DROP PUBLIC SYNONYM user_represolution_method
/
DROP PUBLIC SYNONYM all_represolution
/
DROP PUBLIC SYNONYM user_represolution
/
DROP PUBLIC SYNONYM all_represolution_statistics
/
DROP PUBLIC SYNONYM user_represolution_statistics
/
DROP PUBLIC SYNONYM all_represol_stats_control
/
DROP PUBLIC SYNONYM user_represol_stats_control
/
DROP PUBLIC SYNONYM all_repparameter_column
/
DROP PUBLIC SYNONYM user_repparameter_column
/
DROP PUBLIC SYNONYM all_repaudit_attribute
/
DROP PUBLIC SYNONYM user_repaudit_attribute
/
DROP PUBLIC SYNONYM all_repaudit_column
/
DROP PUBLIC SYNONYM user_repaudit_column
/
DROP PUBLIC SYNONYM all_repcat_refresh_templates
/  
DROP PUBLIC SYNONYM all_repcat_template_objects
/   
DROP PUBLIC SYNONYM all_repcat_template_parms
/     
DROP PUBLIC SYNONYM all_repcat_template_sites
/     
DROP PUBLIC SYNONYM all_repcat_user_authorizations
/
DROP PUBLIC SYNONYM all_repcat_user_parm_values
/   
DROP PUBLIC SYNONYM user_repcat_refresh_templates
/ 
DROP PUBLIC SYNONYM user_repcat_template_objects
/  
DROP PUBLIC SYNONYM user_repcat_template_parms
/    
DROP PUBLIC SYNONYM user_repcat_template_sites
/    
DROP PUBLIC SYNONYM user_repcat_user_authorization
/
DROP PUBLIC SYNONYM user_repcat_user_parm_values
/  

--------
-- Additional synonyms in Rep3
--------
DROP PUBLIC SYNONYM user_repcolumn
/
DROP PUBLIC SYNONYM all_repcolumn
/
DROP PUBLIC SYNONYM dba_repcolumn
/
DROP PUBLIC SYNONYM user_repgenobjects
/
DROP PUBLIC SYNONYM all_repgenobjects
/
DROP PUBLIC SYNONYM dba_repgenobjects
/
DROP PUBLIC SYNONYM dbms_reputil
/
DROP PUBLIC SYNONYM dbms_reputil2
/
DROP PUBLIC SYNONYM dbms_offline_snapshot
/
DROP PUBLIC SYNONYM dbms_offline_og
/
DROP PUBLIC SYNONYM ALL_REPSITES
/
DROP PUBLIC SYNONYM dba_repaudit_attribute
/
DROP PUBLIC SYNONYM dba_repaudit_column
/
DROP PUBLIC SYNONYM dba_repcat
/
DROP PUBLIC SYNONYM dba_repcatlog
/
DROP PUBLIC SYNONYM dba_repcolumn_group
/
DROP PUBLIC SYNONYM dba_repconflict
/
DROP PUBLIC SYNONYM dba_repddl
/
DROP PUBLIC SYNONYM dba_repgenerated
/
DROP PUBLIC SYNONYM dba_repgroup
/
DROP PUBLIC SYNONYM dba_repgrouped_column
/
DROP PUBLIC SYNONYM dba_repkey_columns
/
DROP PUBLIC SYNONYM dba_repobject
/
DROP PUBLIC SYNONYM dba_repparameter_column
/
DROP PUBLIC SYNONYM dba_reppriority
/
DROP PUBLIC SYNONYM dba_reppriority_group
/
DROP PUBLIC SYNONYM dba_repprop
/
DROP PUBLIC SYNONYM dba_represolution
/
DROP PUBLIC SYNONYM dba_represolution_method
/
DROP PUBLIC SYNONYM dba_represolution_statistics
/
DROP PUBLIC SYNONYM dba_represol_stats_control
/
DROP PUBLIC SYNONYM dba_repschema
/
DROP PUBLIC SYNONYM dba_repsites
/
DROP PUBLIC SYNONYM dbms_defer
/
DROP PUBLIC SYNONYM dbms_defergen
/
DROP PUBLIC SYNONYM dbms_defer_query
/
DROP PUBLIC SYNONYM dbms_defer_sys
/
DROP PUBLIC SYNONYM dbms_repcat_admin
/
DROP PUBLIC SYNONYM dbms_repcat_auth
/
DROP PUBLIC SYNONYM dbms_repcat_decl
/
DROP PUBLIC SYNONYM dbms_repcat_validate
/
DROP PUBLIC SYNONYM user_repsites
/
DROP PUBLIC SYNONYM dbmsobjgwrapper
/
-- the following synonyms were created for snapshot only site
DROP PUBLIC SYNONYM dbms_repcat 
/
DROP PUBLIC SYNONYM dbms_rectifier_diff
/
DROP PUBLIC SYNONYM dbms_repcat_rgt
/
DROP PUBLIC SYNONYM dbms_repcat_mig
/
DROP PUBLIC SYNONYM dbms_repcat_instantiate
/

-- a few more public synonyms for 8.1

DROP PUBLIC SYNONYM dbms_repcat_internal
/
DROP PUBLIC SYNONYM dbms_repcat_internal_package
/                         
DROP PUBLIC SYNONYM dba_repcat_template_sites
/                            
DROP PUBLIC SYNONYM dba_repcat_user_parm_values
/                          
DROP PUBLIC SYNONYM dba_repcat_template_parms
/                            
DROP PUBLIC SYNONYM dba_repcat_user_authorizations
/                       
DROP PUBLIC SYNONYM dba_repcat_template_objects
/                          
DROP PUBLIC SYNONYM dba_repcat_refresh_templates
/

---------------------------------
--- RepCat Sequences
---------------------------------
DROP SEQUENCE system.repcat_log_sequence
/
DROP SEQUENCE generator$_s
/
DROP SEQUENCE system.repcat$_refresh_templates_s 
/
DROP SEQUENCE system.repcat$_repprop_key
/
DROP SEQUENCE system.repcat$_template_objects_s
/
DROP SEQUENCE system.repcat$_template_parms_s
/
DROP SEQUENCE system.repcat$_template_sites_s
/
DROP SEQUENCE system.repcat$_runtime_parms_s
/
DROP SEQUENCE system.repcat$_temp_output_s
/
DROP SEQUENCE system.repcat$_user_parm_values_s
/
DROP SEQUENCE system.repcat$_user_authorizations_s
/
DROP SEQUENCE system.repcat$_flavors_s
/

-------------------------------------
--- Drop procedures
-------------------------------------
DROP PROCEDURE system.ora$_sys_rep_auth
/

-------------------------------------
--- Drop library
-------------------------------------
DROP LIBRARY dbms_defer_enq_utl_lib             -- 3GL callout
/
-------------------------------------
--- Restore packages overwritten when replication
--- is loaded
-------------------------------------
@@dbmsdfrd.sql
@@prvtdfrd.plb

-------------------------------------
--- Restore views overwritten when replication
--- is loaded
-------------------------------------
create or replace view deftrandest as SELECT
C.enq_tid deferred_tran_id,
C.cscn delivery_order,
D.dblink
from system.def$_aqcall C, system.def$_destination D
where C.cscn IS NOT NULL
AND C.cscn >= D.last_delivered
AND
  (C.cscn > D.last_delivered
  OR
   (C.cscn = D.last_delivered AND (C.enq_tid > D.last_enq_tid)))
AND EXISTS (
 select /*+ index(def$_calldest_primary) */ NULL
 from system.def$_calldest CD
 where CD.enq_tid = C.enq_tid
   AND CD.dblink = D.dblink)
/
 
Rem The deftrandest view defined above is replaced in catrepc.
grant select on deftrandest to select_catalog_role
/
comment on table DEFTRANDEST is
'Information about destinations for deferred transactions'
/
comment on column DEFTRANDEST.DEFERRED_TRAN_ID is
'Transaction ID'
/
comment on column DEFTRANDEST.DELIVERY_ORDER is
'Total ordering of transactions: second element in the tuple'
/
comment on column DEFTRANDEST.DBLINK is
'The destination database'
/
DROP PUBLIC SYNONYM deftrandest
/
CREATE PUBLIC SYNONYM deftrandest FOR deftrandest
/

Rem dbms_snapshot depends on deftrandest
ALTER PACKAGE DBMS_SNAPSHOT COMPILE BODY
/

DROP LIBRARY dbms_repcat_internal_pkg_lib
/
