rem 
rem $Header: catdefer.sql 08-nov-96.09:40:50 ato Exp $ 
rem 
Rem  Copyright (c) 1992, 1996, 1997 by Oracle Corporation 
Rem    NAME
Rem      catdefer.sql - catalog of deferred rpc queues
Rem    DESCRIPTION
Rem      catalog of deferred rpc queues
Rem      This file contains sql which creates the base tables
Rem      used to store deferred remote procedure calls for used in
Rem      transaction replication.
Rem      Tables:
Rem         defTran
Rem         defTranDest
Rem         defError
Rem         defCallDest
Rem         defDefaultDest
Rem         defCall
Rem         defSchedule
Rem    RETURNS
Rem 
Rem    NOTES
Rem      Tables created in this file are owned by user system (not) sys
Rem      views are owned by sys.
Rem      The defcall view is implemented by the prvtdfri.plb script.
Rem      The defcalldest view is implemented by the catrepc.sql script.
Rem      The deftrandest view is reimplemented by the catrepc.sql script.
Rem      If the repcat tables are installed,
Rem      the catrepc.sql script should always be run after this script is run.
Rem 
Rem      Tables are created in catdefrt.sql.  All other objects created here
Rem 
Rem    MODIFIED   (MM/DD/YY)
Rem     jingliu    07/29/97 -  change deflob.enq_tid to deferred_tran_id
Rem     jstamos    04/04/97 -  tighter AQ integration
Rem     liwong     03/07/97 -  merge 433785 manually
Rem     liwong     02/10/97 -  Comment out defcalldest, 
Rem                         -  modify deftrandest, add queue_batch to deftran
Rem     liwong     01/15/97 -  Modified delete statement for expact$ and added
Rem                         -  def$_aqcall and def$_aqerror
Rem     jstamos    01/03/97 -  add drop user cascade support
Rem     jstamos    12/23/96 -  comment on nclob_col
Rem     jstamos    11/21/96 -  nchar support
Rem     ato        11/08/96 -  remove catqueue.sql
Rem     sjain      11/06/96 -  Change defcall and deftran for backwards compata
Rem     sjain      11/05/96 -  Fix type in defcall
Rem     mluong     10/28/96 -  remove dup calls to build AQ package
Rem     sjain      10/17/96 -  AQ Conversion
Rem     sjain      10/15/96 -  aq conversion
Rem     sjain      10/14/96 -  Aq conversion
Rem     sjain      10/01/96 -  AQ conversion
Rem     sjain      09/04/96 -  AQ cont.
Rem     sjain      07/25/96 -  continue with the aq conversion
Rem     sjain      07/22/96 -  Convert to AQ
Rem     jstamos    06/12/96 -  LOB support for deferred RPCs
Rem     ldoo       05/09/96 -  New security model
Rem     mmonajje   05/21/96 -  Replace interval col name with interval#
Rem     ixhu       04/11/96 -  AQ support: add obj_type in expact$
Rem     asurpur    04/08/96 -  Dictionary Protection Implementation
Rem     jstamos    08/17/95 -  code review changes
Rem     jstamos    08/16/95 -  add comments to views
Rem     hasun      01/23/95 -  Modify views for Rep3 - Object Groups
Rem     dsdaniel   01/25/95 -  merge changes from branch 1.5.720.4
Rem     dsdaniel   01/23/95 -  merge changes from branch 1.1.710.11
Rem     dsdaniel   01/05/95 -  need extra at sign
Rem     dsdaniel   12/23/94 -  merge changes from branch 1.5.720.1-3
Rem     dsdaniel   12/21/94 -  merge changes from branch 1.1.710.8-10
Rem     dsdaniel   12/08/94 -  revise defcalldest, deftrandest views
Rem     dsdaniel   11/22/94 -  split out table creations
Rem     dsdaniel   11/18/94 -  deftran-ectomy, deftrandest-ectomy
Rem     dsdaniel   11/17/94 -  merge changes from branch 1.1.710.7
Rem     dsdaniel   11/09/94 -  defcalldest, deftrandest changes
Rem     dsdaniel   08/04/94 -  make it a cluster (again)
Rem     dsdaniel   08/04/94 -  create a version without the cluster
Rem     dsdaniel   08/03/94 -  eliminate ON DELETE CASCADE *again
Rem     dsdaniel   08/02/94 -  make it a cluster
Rem     dsdaniel   07/28/94 -  restore ON DELETE CASCADE
Rem     dsdaniel   07/27/94 -  eliminate ON DELETE CASCADE
Rem     dsdaniel   07/19/94 -  export support changes
Rem     rjenkins   03/22/94 -  merge changes from branch 1.1.710.4
Rem     rjenkins   01/19/94 -  merge changes from branch 1.1.710.3
Rem     dsdaniel   01/18/94 -  merge changes from branch 1.1.710.2
Rem     rjenkins   01/17/94 -  changing jq to job
Rem     rjenkins   12/17/93 -  creating job queue
Rem     dsdaniel   10/31/93 -  merge changes from branch 1.1.710.1
Rem     dsdaniel   10/28/93 -  deferred rpc dblink security
Rem                         -  also removed table drops, since shouldnt
Rem                         -  loose data on upgrade
Rem     dsdaniel   10/26/93 -  merge changes from branch 1.1.400.1
Rem     dsdaniel   10/10/93 -  Creation from dbmsdefr
rem create base tables

-- Sys is granted privileges through roles, which don't apply to
-- packages owned by sys.  Explicitly grant permissions.
grant select any table to sys with admin option
/
grant insert any table to sys
/
grant update any table to sys
/
grant delete any table to sys
/

rem drop existing synonyms from sys -system

DROP SYNONYM def$_tran
/
DROP SYNONYM def$_call
/
DROP SYNONYM def$_calldest
/
DROP SYNONYM def$_error
/
DROP SYNONYM def$_defaultdest
/
DROP SYNONYM def$_schedule
/

--
--
@@catdefrt  
--
--
-- Create a synonym for the new deferred queue table.
-- Note columns in def$_aqcall are different from the old def$_call
CREATE SYNONYM def$_aqcall FOR system.def$_aqcall
/
 
CREATE SYNONYM def$_calldest FOR system.def$_calldest
/
CREATE SYNONYM def$_schedule FOR system.def$_schedule
/
CREATE SYNONYM def$_error FOR system.def$_error
/

CREATE OR REPLACE VIEW defschedule  AS
  SELECT s.dblink, s.job, j.interval# interval, next_date, j.last_date, s.disabled,
         s.last_txn_count, s.last_error_number, s.last_error_message
    FROM system.def$_destination s, sys.job$ j where s.job = j.job(+)
/
grant select on defschedule to select_catalog_role
/

comment on table DEFSCHEDULE is
'Information about propagation to different destinations'
/
comment on column DEFSCHEDULE.DBLINK is
'Destination'
/
comment on column DEFSCHEDULE.JOB is
'Number of job that pushes queue'
/
comment on column DEFSCHEDULE.INTERVAL is
'Function used to calculate the next time to push the queue to destination'
/
comment on column DEFSCHEDULE.NEXT_DATE is
'Next date that job is scheduled to be executed'
/
comment on column DEFSCHEDULE.LAST_DATE is
'Last time queue was (attempted to be) pushed to destination'
/
comment on column DEFSCHEDULE.DISABLED is
'Is propagation to destination disabled'
/
comment on column DEFSCHEDULE.LAST_TXN_COUNT is
'Number of transactions pushed during last attempt'
/
comment on column DEFSCHEDULE.LAST_ERROR_NUMBER is
'Oracle error number from last push'
/
comment on column DEFSCHEDULE.LAST_ERROR_MESSAGE is
'Error message from last push'
/

DROP PUBLIC SYNONYM defschedule
/
CREATE PUBLIC SYNONYM defschedule FOR defschedule
/

CREATE OR REPLACE VIEW deferror AS SELECT
   e.enq_tid deferred_tran_id,
   e.origin_tran_db,
   e.origin_enq_tid origin_tran_id,
   e.step_no callno,
   e.destination, 
   e.enq_time start_time, e.error_number, e.error_msg, u.name receiver 
    FROM system.def$_error e, sys.user$ u
    WHERE e.receiver = u.user# (+)
/
grant select on deferror to select_catalog_role
/

comment on table DEFERROR is
'Information about all deferred transactions that caused an error'
/
comment on column DEFERROR.DEFERRED_TRAN_ID is
'The ID of the transaction that created the error'
/
comment on column DEFERROR.ORIGIN_TRAN_DB is
'The database originating the deferred transaction'
/
comment on column DEFERROR.ORIGIN_TRAN_ID is
'The original ID of the transaction'
/
comment on column DEFERROR.CALLNO is
'Unique ID of call that caused an error'
/
comment on column DEFERROR.DESTINATION is
'Database link used to address destination'
/
comment on column DEFERROR.START_TIME is
'Time original transaction enqueued'
/
comment on column DEFERROR.ERROR_NUMBER is
'Oracle error number'
/
comment on column DEFERROR.ERROR_MSG is
'Error message text'
/
comment on column DEFERROR.RECEIVER is
'The original receiver of the deferred transaction'
/

DROP PUBLIC SYNONYM deferror
/
CREATE PUBLIC SYNONYM deferror for deferror
/

CREATE OR REPLACE VIEW deferrcount AS
  SELECT count(1) errcount, destination 
    FROM deferror GROUP BY destination
/
grant select on deferrcount to select_catalog_role
/

comment on table DEFERRCOUNT is
'Summary information about deferred transactions that caused an error'
/
comment on column DEFERRCOUNT.ERRCOUNT is
'Number of existing transactions that caused an error for given destination'
/
comment on column DEFERRCOUNT.DESTINATION is
'Database link used to address destination'
/

DROP PUBLIC SYNONYM deferrcount
/
CREATE PUBLIC SYNONYM deferrcount for deferrcount
/
GRANT SELECT ON deferrcount TO PUBLIC
/

CREATE OR REPLACE VIEW deftran AS SELECT 
  enq_tid deferred_tran_id, 
  cscn delivery_order, 
  decode(recipient_key, 0, 'D', 'R') destination_list,
  enq_time start_time 
  FROM system.def$_aqcall t 
  WHERE cscn is NOT NULL 
UNION ALL 
SELECT enq_tid deferred_tran_id, 
  cscn delivery_order, 
  'D' destination_list, 
  enq_time start_time 
  FROM system.def$_aqerror t 
  WHERE cscn is NOT NULL 
/
grant select on deftran to select_catalog_role
/

comment on table DEFTRAN is
'Information about all deferred transactions'
/
comment on column DEFTRAN.DEFERRED_TRAN_ID is
'The transaction that enqueued the calls'
/
comment on column DEFTRAN.DELIVERY_ORDER is
'Total ordering on transactions'
/
comment on column DEFTRAN.DESTINATION_LIST is
'Determine destinations from deftrandest (D) or repcat (R)'
/
comment on column DEFTRAN.START_TIME is
'Time original transaction enqueued'
/

DROP PUBLIC SYNONYM deftran
/
CREATE PUBLIC SYNONYM deftran FOR deftran
/

--- just select from def$_calldest (D-type txn). We need repcat$_repprop
--- to determine destination for R-type txn. We can't remove this one
--- because dbms_snapshot needs it
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
/

--  Create table of default nodes for replication targets
--  this table is managed by calls in dbms_defer_sys

CREATE SYNONYM def$_defaultdest FOR system.def$_defaultdest
/
CREATE OR REPLACE VIEW defdefaultdest AS
  SELECT * from system.def$_defaultdest
/
grant select on defdefaultdest to select_catalog_role
/

comment on table DEFDEFAULTDEST is
'Default destinations for deferred remote procedure calls'
/
comment on column DEFDEFAULTDEST.DBLINK is
'Default destination'
/

DROP PUBLIC SYNONYM defdefaultdest
/
CREATE PUBLIC SYNONYM defdefaultdest for defdefaultdest
/

DROP SYNONYM def$_lob
/
CREATE SYNONYM def$_lob FOR system.def$_lob
/

CREATE OR REPLACE VIEW DefLOB 
  (id, deferred_tran_id, blob_col, clob_col, nclob_col)
  AS SELECT
     d.id,
     d.enq_tid,
     d.blob_col,
     d.clob_col,
     d.nclob_col
  FROM sys.def$_lob d
/

grant select on deflob to select_catalog_role
/

comment on table DEFLOB is
'Storage for LOB parameters to deferred RPCs'
/
comment on column DEFLOB.ID is
'Identifier of LOB parameter'
/
comment on column DEFLOB.DEFERRED_TRAN_ID is
'Transaction identifier for deferred RPC with this LOB parameter'
/
comment on column DEFLOB.BLOB_COL is
'Binary LOB parameter'
/
comment on column DEFLOB.CLOB_COL is
'Character LOB parameter'
/
comment on column DEFLOB.NCLOB_COL is
'National Character LOB parameter'
/

DROP PUBLIC SYNONYM DefLOB
/
CREATE PUBLIC SYNONYM DefLOB for DefLOB
/

CREATE OR REPLACE VIEW defpropagator
  (username, userid, status, created)
  AS SELECT
       p.username,
       p.userid,
       DECODE(u.name, NULL, 'INVALID', 'VALID'),
       p.created
     FROM system.def$_propagator p, sys.user$ u
     WHERE p.userid = u.user# (+)
/
grant select on defpropagator to select_catalog_role
/

comment on table DEFPROPAGATOR is
'Information about the propagator for all deferred remote procedure calls'
/
comment on column DEFPROPAGATOR.USERNAME is
'Username of the propagator'
/
comment on column DEFPROPAGATOR.USERID is
'User ID of the propagator'
/
comment on column DEFPROPAGATOR.STATUS is
'Status of the propagator'
/
comment on column DEFPROPAGATOR.CREATED is
'Time when the propagator is registered'
/

DROP PUBLIC SYNONYM defpropagator
/
CREATE PUBLIC SYNONYM defpropagator FOR defpropagator
/

REM Set up export actions for deferred rpc tables.
rem delete existing export data

DELETE FROM expact$ WHERE name like 'DEF$_%' 
  AND func_package = 'DBMS_DEFER_IMPORT_INTERNAL'
/

insert into expact$ (owner, name, func_schema, func_package, func_proc, code,
obj_type)
values('SYSTEM','DEF$_AQERROR','SYS','DBMS_DEFER_IMPORT_INTERNAL',
        'QUEUE_EXPORT_CHECK',1,2)
/                                       
insert into expact$ (owner, name, func_schema, func_package, func_proc, code,
obj_type)
values('SYSTEM','DEF$_AQCALL','SYS','DBMS_DEFER_IMPORT_INTERNAL',
        'QUEUE_EXPORT_CHECK',1,2)
/
insert into expact$ (owner, name, func_schema, func_package, func_proc, code,
obj_type)
values('SYSTEM','DEF$_CALLDEST','SYS','DBMS_DEFER_IMPORT_INTERNAL',
        'QUEUE_EXPORT_CHECK',1,2)
/
insert into expact$ (owner, name, func_schema, func_package, func_proc, code,
obj_type)
values('SYSTEM','DEF$_ERROR','SYS','DBMS_DEFER_IMPORT_INTERNAL',
        'QUEUE_EXPORT_CHECK',1,2)
/
insert into expact$ (owner, name, func_schema, func_package, func_proc, code,
obj_type)
values('SYSTEM','DEF$_DEFAULTDEST','SYS','DBMS_DEFER_IMPORT_INTERNAL',
        'QUEUE_EXPORT_CHECK',1,2)
/
insert into expact$ (owner, name, func_schema, func_package, func_proc, code,
obj_type)
values('SYSTEM','DEF$_DESTINATION','SYS','DBMS_DEFER_IMPORT_INTERNAL',
        'QUEUE_EXPORT_CHECK',1,2)
/
COMMIT
/

DELETE FROM sys.duc$
  WHERE owner = 'SYS' AND pack = 'DBMS_DEFER_IMPORT_INTERNAL'
    AND proc = 'DROP_PROPAGATOR_CASCADE' AND operation# = 1
/
INSERT INTO sys.duc$ (owner, pack, proc, operation#, seq, com)
  VALUES('SYS', 'DBMS_DEFER_IMPORT_INTERNAL', 'DROP_PROPAGATOR_CASCADE', 1, 1,
         'Remove propagator if necessary')
/
COMMIT
/
