Rem
Rem $Header: catqueue.sql 13-aug-99.19:56:51 kmeiyyap Exp $
Rem $Header: catqueue.sql 13-aug-99.19:56:51 kmeiyyap Exp $
Rem
Rem catqueue.sql
Rem
Rem  Copyright (c) Oracle Corporation 1996, 1997, 1998, 1999. All Rights Reserved.
Rem
Rem    NAME
Rem      catqueue.sql - <one-line expansion of the name>
Rem
Rem    DESCRIPTION
Rem      This file contains the queue dictionary information which is created
Rem      in the SYSTEM schema.  There are three main tables:
Rem             aq$_queue_tables     - stores information about all the queue
Rem                                    tables
Rem             aq$_queues           - stores information about all the queues
Rem             aq$_schedules        - stores information about all the 
Rem                                    schedules
Rem      In the SYS schema
Rem             aq$_queue_statistics   - stores inforamtion about statistics
Rem                                      this inforamtion is only for OPS
Rem		aq$_message_types      - Type Identifiers (TOIDs) of queues
Rem				         that are targets of propagation.
Rem		aq$_propagation_status - Status of propagation to a destination
Rem					 from a given source queue
Rem		aq$_pending_messages   - Messages that have been sent in the
Rem				       - latest unit of work.
Rem 	 Sequence in the SYS schema
Rem		aq$_propagation_sequence - sequence number generator to stamp
Rem					   each transaction of propagator
Rem
Rem	 The Standard AQ types are created to be used in the enqueue call
Rem	 as well as the admin subscribe and unsubscribe calls. The types are:
Rem		aq$_agent - Uniquely identifies a producer or consumer of 
Rem			    a message. These can be users,queues, programs etc.
Rem			    Currently we only support agents that are queues or
Rem			    programs. This type is used in 
Rem			    dbms_aqadm.subscribe and dbms_aqadm.unsubscribe.
Rem		aq$_dequeue_history - History of dequeuers for the particular 
Rem				      message.
Rem		aq$_history - A varying array of aq$_dequeue_history
Rem
Rem	 In the SYS schema there is also a new queue table aq$_prop_table
Rem      for use internally in propagation scheduling.
Rem
Rem    NOTES
Rem      Must be run when connected to SYS or INTERNAL.
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    kmeiyyap    08/13/99 - bug 954417 - add sequence for trans. grouping tid
Rem    bnainani    07/30/99 - Bug 915265 - change file names to 8 chars
Rem    bnainani    07/29/99 - grant execute on aq packages to execute_catalog_r
Rem    bnainani    07/01/99 - grant execute on dbms_aqjms to aquser/aqadmin
Rem    bnainani    06/03/99 - add prvtaqjms.plb
Rem    nbhatt      03/12/99 - hide total consumers from v$aq
Rem    nbhatt      03/15/99 - bug:810412 move affinity table before aq_schedules
Rem    kmeiyyap    01/07/99 - make catqueue.sql sqlplus compatible
Rem    ato         12/12/98 - add dbms_aqadm to imp_full_database
Rem    ryaseen     11/20/98 - splitting dbms_aqadm
Rem    ato         11/02/98 - filter duplication types in queue_tables views  
Rem    mkamath     10/22/98 - Adding ENQUEUE/DEQUEUE ANY privilege to SYS
Rem    ato         10/21/98 - set order in exppkgact$
Rem    ato         10/12/98 - exp/imp procedural objects support               
Rem    ato         09/30/98 - add prvtaqin.sql                                 
Rem    ato         09/15/98 - create all_queue_tables view                   
Rem    mkamath     08/31/98 - making type sys.aq$_notify_msg public
Rem    schandra    08/06/98 - move prvtaqdi ahead of other packages
Rem    ncramesh    08/07/98 - change fro sqlplus
Rem    schandra    07/31/98 - defer delete from pending_messages
Rem    ato         07/16/98 - remove events 10931
Rem    ato         07/13/98 - bug672378-use intcol# in queue table views
Rem    schandra    06/25/98 - add spare fields in propagation tables
Rem    ato         06/25/98 - grant options for execute rights                 
Rem    nbhatt      06/18/98 -  add NONPERSISTENT queue to ALL_QUEUES
Rem    ryaseen     06/29/98 - update queue table flags comment
Rem    schandra    06/05/98 - queue table upgrade/downgrade
Rem    ato         06/04/98 - grant execute rights to roles  
Rem    mkamath     06/02/98 - Adding session serial# to schedules table     
Rem    ato         06/01/98 - grant select on propagation_status to aq_administ
Rem    mkamath     06/03/98 - add comment related to SCHEDULES table
Rem    ato         05/16/98 - remove catq8003.sql
Rem    ato         05/15/98 - grant select on v_$aq and gv_$aq to aq_admin_role
Rem    ato         05/14/98 - add ALL_QUEUES and QUEUE_PRIVILEGES views
Rem    ato         05/12/98 - grant all system types to PUBLIC
Rem    ato         05/08/98 - grant dba view to AQ_ADMINISTRATOR_ROLE
Rem    ato         05/08/98 - move role creations in catqueue.sql
Rem    ato         05/05/98 - reverse the index key order in aq$_queues_check
Rem    mkamath     05/31/98 - modifying user_queue_schedules
Rem    mkamath     05/28/98 - Changing view user_queue_schedules
Rem    schandra    05/05/98 - add COMPATIBLE column to DBA_QUEUE_TABLES and USE
Rem    mkamath     05/12/98 - Adding fields to the schedules table
Rem    arsaxena    04/30/98 - chnage views to decode non_persistent queue type
Rem    mkamath     04/23/98 - Moving notify_msg definition ahead of package loa
Rem    mkamath     04/26/98 - Enhancing propagation notify queue payload
Rem    ryaseen     04/13/98 - load package dbms_prvtaqis
Rem    mkamath     04/16/98 - Adding USER_QUEUE_SCHEDULES view
Rem    ato         04/22/98 - add prvtaqdi.plb (Admin Internal)
Rem    ato         04/09/98 - add AQ admin library
Rem    schandra    03/25/98 - create recovery tables for propagation
Rem    ryaseen     04/10/98 - Move subscriber, rule tables to user schema
Rem    ryaseen     04/07/98 - Add sys.aq$_rules
Rem    nireland    03/17/98 - Add synonym for DBA_QUEUES. #605559
Rem    ryaseen     02/20/98 - Add sys.aq$_queue_subscribers
Rem    schandra    01/27/98 - add comment for new queue tables
Rem    nbhatt      03/17/98 - add new table for queue affinities
Rem    nbhatt      02/23/98 - add dictionary table for instance affinity 
Rem    mkamath     02/28/98 - changing aq$_schedules adding aq$_prop_table
Rem    ato         11/13/97 - patch bug fix 572136
Rem    ato         11/13/97 - remove double quote
Rem    arsaxena    10/16/97 - increase number of recipients to 1024
Rem    nbhatt      09/15/97 - add gv$aq and v$aq views
Rem    arsaxena    09/12/97 - address 90->1024
Rem    nbhatt      08/14/97 - remove owner_inst and incarn_num
Rem    nbhatt      08/12/97 - add incarn_num to aq$_queues
Rem    nbhatt      08/06/97 - add column for statistics in system.aq_queues
Rem    schandra    08/15/97 - 524213: sqlplus is sensitive to blank lines
Rem    arsaxena    07/22/97 - add scheduling table/view for propagation
Rem    schandra    05/13/97 - address - 30->90
Rem    ato         05/07/97 - bug#489741:don't replace aq$ types
Rem    ato         05/05/97 - remove internal package
Rem    schandra    05/01/97 - Bug# 486530: fix retention time handling
Rem    schandra    04/30/97 - add types for displaying dequeue history
Rem    nbhatt      04/24/97 - add loading of catq8003.sql
Rem    schandra    04/24/97 - add field to denote how app was specified in dequ
Rem    nbhatt      04/23/97 - chnage EXCPT to EXCEPTION_QUEUE
Rem    nbhatt      04/22/97 - destination->address in AQ agent
Rem    nbhatt      04/21/97 - compile the dbms_aqin package
Rem    nbhatt      04/21/97 - change views
Rem    esoyleme    04/22/97 - bit for iot imp/exp in queue_tables
Rem    schandra    04/12/97 - create AQ types with fixed OIDs
Rem    jbellemo    04/12/97 - add with grant option
Rem    ato         03/29/97 - raw payload support
Rem    schandra    03/25/97 - support grouping by transaction
Rem    schandra    03/18/97 - increase size of varray to 100000
Rem    schandra    03/11/97 - string for queue & appname in history
Rem    schandra    02/18/97 - create AQ types
Rem    nbhatt      03/31/97 - update RETENTION field
Rem    ato         03/18/97 - fix view bug
Rem    asurpur     01/03/97 - Grant DBA views to SELECT_CATALOG_ROLE
Rem    ato         11/08/96 - ADT => object_type
Rem    ato         11/06/96 - insert aq$ tables into noexp$
Rem    ato         11/06/96 - do not drop aq$ tables
Rem    ato         10/22/96 - modify event level of 10931
Rem    ato         10/25/96 - fix view bug
Rem    ato         10/22/96 - change name of midseq to chainseq
Rem    ato         10/21/96 - add fields to DBA_QUEUES
Rem    ato         10/15/96 - fix user views
Rem    pshah       10/15/96 - Changing the load pathname of the dbms_aq 
Rem                           package specification and body
Rem    ato         10/09/96 - load AQ admin package
Rem    pshah       10/08/96 - Incorporating changes due to AQ packages 
Rem                           becoming non-fixed
Rem    pshah       09/27/96 - Remove AQLIB creation
Rem    pshah       09/25/96 - Creating AQLIB
Rem    pshah       09/25/96 - Creating a public synonym for the x$dbmsaq 
Rem                           package
Rem    pshah       09/23/96 - Modifying queue dictionary column names
Rem    pshah       08/26/96 - Adding the 'ret_time' column to system.aq$_queues
Rem    ato         08/26/96 - fix view user_queue_tables
Rem    ato         08/22/96 - remove toid from aq$_queues
Rem    ato         08/15/96 - modify for typed queues
Rem    ato         08/07/96 - change queue_type usage
Rem    pshah       08/02/96 - Removing data_obj_no from queue table dictionary
Rem    pshah       08/01/96 - Adding data_obj_no column to the queue tables dic
Rem    ato         07/31/96 - ALL_QUEUES -> DBA_QUEUES
Rem    ato         07/29/96 - queue_tid -> qtoid
Rem    ato         07/28/96 - fixed typos
Rem    pshah       07/23/96 - q_userdata_type => q_udata_type
Rem    ato         07/22/96 - Fixed-type vs variant-type queue
Rem    pshah       06/06/96 - Adding a column to queues data dictionary
Rem    pshah       05/30/96 - Queue Data Dictionary
Rem    pshah       05/30/96 - Created
Rem

-- Granting priveleges to SYS
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
/
grant select any sequence to sys
/
grant execute any type to sys
/
-- Create AQ types 

-- NOTE NOTE NOTE NOTE NOTE NOTE NOTE NOTE NOTE NOTE NOTE NOTE NOTE NOTE NOTE 
-- These types are used in oltp/qs/kwqi*.[ch]. If the type is changed make
-- sure the references to the type in the code is also changed.
-- The internal structure that represents aq$_agent is kwqia defined in kwqi.h
-- Similarly kwqidh represents the aq$_dequeue_history type.
-- NOTE NOTE NOTE NOTE NOTE NOTE NOTE NOTE NOTE NOTE NOTE NOTE NOTE NOTE NOTE 

CREATE TYPE sys.aq$_agent 
TIMESTAMP '1997-04-12:12:59:00' OID '00000000000000000000000000020000'
AS OBJECT
( name		varchar2(30), -- M_IDEN, name of a message producer or consumer
  address	varchar2(1024),           -- address where message must be sent
  protocol	number) 	       -- protocol for communication, must be 0
/

CREATE TYPE sys.aq$_dequeue_history 
TIMESTAMP '1997-04-12:12:59:00' OID '00000000000000000000000000020001'
AS OBJECT
( consumer		varchar2(30),			 -- identifies dequeuer
  transaction_id	varchar2(22),	  -- M_LTID, transaction id of dequeuer
  deq_time		date,				     -- time of dequeue
  deq_user		number,		-- user id of client performing dequeue
  remote_apps		varchar2(4000),        -- string repn. of remote agents
  agent_naming		number,		   -- how was the message sent to agent
  propagated_msgid	raw(16)) 		  -- message id in remote queue
/

-- TODO. Ideally the collection types below should be unbounded. We can use 
-- nested tables for this purpose but nested tables are stored out-of-line and 
-- may hence cause a performance hit. If the limit for the collections below
-- is an issue and nested tables is not an acceptable solution we may have
-- to use a LONG datatype for storing this information and parse it ourselves.
-- It is possible, however, that unbounded varrays may be supported in 8.1

CREATE TYPE sys.aq$_subscribers 
TIMESTAMP '1997-04-12:12:59:00' OID '00000000000000000000000000020002'
AS VARRAY(1024) OF sys.aq$_agent 
/

CREATE TYPE sys.aq$_recipients 
TIMESTAMP '1997-04-12:12:59:00' OID '00000000000000000000000000020003'
AS VARRAY(1024) OF sys.aq$_agent 
/
       
CREATE TYPE sys.aq$_history 
TIMESTAMP '1997-04-12:12:59:00' OID '00000000000000000000000000020004'
AS VARRAY(1024) OF sys.aq$_dequeue_history 
/

CREATE TYPE sys.aq$_dequeue_history_t 
TIMESTAMP '1997-04-12:12:59:00' OID '00000000000000000000000000020005'
AS TABLE of sys.aq$_dequeue_history 
/

-- create type for propagation notify queue
CREATE TYPE sys.aq$_notify_msg AS OBJECT (
	opcode INTEGER, qid RAW(16), dest VARCHAR2(128))
/

-- TODO: For some reason, must grant execute on these types to system as well
grant execute on sys.aq$_agent to PUBLIC
/
grant execute on sys.aq$_dequeue_history to PUBLIC
/
grant execute on sys.aq$_subscribers to PUBLIC
/
grant execute on sys.aq$_recipients to PUBLIC
/
grant execute on sys.aq$_history to PUBLIC
/
grant execute on sys.aq$_notify_msg to PUBLIC
/

-- Create the table with information about all the queue tables in the system
CREATE TABLE system.aq$_queue_tables(
	schema	        VARCHAR2(30)	 -- 	name of the schema the queue 
			NOT NULL,	 --	table belongs to
	name		VARCHAR2(30)	 -- 	name of the queue table
			NOT NULL,
        udata_type      NUMBER           --     userdata type: 1 for OBJECT
                        NOT NULL,        --                    2 for VARIANT
	objno		NUMBER NOT NULL, --	object number
        flags           NUMBER NOT NULL, --     queue table properties
					 --	1 for multiple dequeues
					 --	2 for transactional grouping
                                         --     4 for table exported but
                                         --       dequeue iot not patched.
					 --	8 for 8.1 multi-consumer qtable
					 --    16 for table exported but
					 --	  history iot not patched.
					 --    32 for table exported but
					 --	  timemanager iot not imported
					 --    64 for table exported but
					 --	  subscribers not imported
					 --   128 for upgrade in progress
					 --   256 for downgrade in progress
	sort_cols	NUMBER NOT NULL, --	sort order for dequeue
					 --	1 sort by priority
					 --	2 sort by enq_time
					 --	3 sort by priority, enq_time
					 --	4 sort by enq_time, prioirty
	table_comment	VARCHAR2(2000),	 --	user comment
CONSTRAINT aq$_queue_tables_primary PRIMARY KEY (objno))
/

-- Contains information on all the queues in the system
CREATE TABLE system.aq$_queues( 
        oid             RAW(16),                -- queue identifier which is
                                                -- globally used to identify
                                                -- a queue
	eventid	 	NUMBER NOT NULL,	-- queue id used as event id
	name		VARCHAR2(30) NOT NULL,	-- name of a queue
	table_objno	NUMBER NOT NULL,	-- object no. of the queue 
                                                -- table
	usage   	NUMBER NOT NULL,   	-- usage of the queue:
                                                -- 0 for normal queue
		 				-- 1 for exception queue
	enable_flag	NUMBER NOT NULL,        -- queue enabled?
                                        	-- 0x01 enqueue enabled
						-- 0x02 dequeue enabled
	max_retries	NUMBER,			-- maximum number of retries
	retry_delay	NUMBER,			-- delay before retrying (secs)
        properties      NUMBER,                 -- various properties of
                                                -- queues
                                                -- 0x0001 tracking on
        ret_time        NUMBER,                 -- retention time (in seconds)
	queue_comment	VARCHAR2(2000),		-- user comment
	subscribers	sys.aq$_subscribers,	-- varray of aq$_agents
CONSTRAINT aq$_queues_check UNIQUE(name, table_objno),
CONSTRAINT aq$_queues_primary PRIMARY KEY (oid))
/

-- contains information about queue table affinities (for OPS)
--
CREATE TABLE sys.aq$_queue_table_affinities(
       table_objno        NUMBER   NOT NULL,  -- table object number 
       primary_instance   NUMBER   NOT NULL,  --  primary owner instance-id
       secondary_instance NUMBER   NOT NULL,  --  secondary owner instance-id
       owner_instance     NUMBER   NOT NULL,  --  current owner instance-id
CONSTRAINT aq$_qtable_affinities_pk PRIMARY KEY (table_objno))
/

-- NOTE NOTE NOTE NOTE NOTE NOTE NOTE NOTE NOTE NOTE NOTE NOTE NOTE NOTE NOTE 
-- Please do not add tables needed by the time manager after aq$_schedules.
-- Reason: Time manager checks for the existence of aq$_schedules before 
-- starting.
-- NOTE NOTE NOTE NOTE NOTE NOTE NOTE NOTE NOTE NOTE NOTE NOTE NOTE NOTE NOTE 
 
-- Contains information on all the schedules in the system
CREATE TABLE system.aq$_schedules( 
        oid             RAW(16),                -- queue identifier which is
                                                -- globally used to identify
                                                -- a queue
        destination     VARCHAR2(128) NOT NULL, -- dblink, for now 
        start_time      DATE,                   -- seconds since midnight 
                                                -- to start propagating
        duration        VARCHAR2(8),            -- propagation window in
                                                -- seconds
        next_time       VARCHAR2(128),          -- function to compute next
                                                -- window wrt to end of 
                                                -- current window
        latency         VARCHAR2(8),            -- maximum delay in seconds
                                                -- to propagate a message
                                                -- after it is enqueued
        last_time       DATE,                   -- last time that messages were 
                                                -- propagated successfully 
        jobno           NUMBER,                 -- job number in the job queue
                                                -- that will propagate the 
                                                -- messages
CONSTRAINT aq$_schedules_check UNIQUE(jobno),
CONSTRAINT aq$_schedules_primary PRIMARY KEY (oid, destination))
/

-- To avoid import export problems, from 8.1 onwares the schedules information
-- is stored in the sys schema 
CREATE TABLE sys.aq$_schedules( 
        oid             RAW(16),                -- queue identifier which is
                                                -- globally used to identify
                                                -- a queue
	destination	VARCHAR2(128) NOT NULL,	-- dblink, for now 
	start_time   	DATE,			-- seconds since midnight 
                                                -- to start propagating
	duration	VARCHAR2(8),	        -- propagation window in  
  						-- seconds
	next_time	VARCHAR2(200),        	-- function to compute next
  						-- window wrt to end of 
                                                -- current window
	latency	  	VARCHAR2(8),		-- maximum delay in seconds
						-- to propagate a message
						-- after it is enqueued
	last_run	DATE,			-- last time that messages 
						-- were propagated 
						-- successfully 
	jobno		NUMBER,			-- job number in the job queue
						-- that will propagate the 
	failures	NUMBER,			-- number of times execution 
						-- failed
	disabled	VARCHAR2(1),		-- N if enabled, Y if disabled
						-- and schedule will not be
						-- executed
	error_time	DATE,			-- date and time of last 
						-- unsuccessful execution
	last_error_msg	VARCHAR2(4000),		-- error message text
	cur_start_time	DATE,			-- date and time the current 
						-- execution window started
	next_run        DATE,                   -- date when job is scheduled
						-- to run next
	process_name	VARCHAR2(8),		-- process name (instane# || 
						-- snp#) of the process 
						-- executing this step
	sid		NUMBER,			-- session ID of the job 
						-- executing this schedule
	serial		NUMBER,			-- session serial# of the job 
						-- executing this schedule
	total_time	NUMBER,			-- total time (seconds) spent
						-- by the system in executing
						-- this schedule
	total_msgs	NUMBER,			-- total number of messages
						-- transferred in this 
						-- schedule
	total_bytes	NUMBER,			-- total number of bytes in 
						-- all messages transferred
						-- in this schedule
	total_windows	NUMBER,			-- total number of windows
						-- for this schedule
        win_msgs        NUMBER,                 -- number of messages thus 
						-- far in this window
        win_bytes       NUMBER,                 -- number of bytes thus 
						-- far in this window
	max_num_per_win	NUMBER,			-- max. number of messages
						-- propagated in a window
	max_size	NUMBER,			-- max. size of a propagated
						-- message (bytes) in this 
						-- schedule
        instance        NUMBER,                 -- instance-affinity of queue
	spare1		NUMBER,       		-- possible protocol - unused
	spare2		VARCHAR2(1024),         -- possible address - unused
CONSTRAINT aq$_schedules_primary PRIMARY KEY (oid, destination))
/

-- NOTE NOTE NOTE NOTE NOTE NOTE NOTE NOTE NOTE NOTE NOTE NOTE NOTE NOTE NOTE 
-- Please do not add tables needed by the time manager after aq$_schedules.
-- Reason: Time manager checks for the existence of aq$_schedules before 
-- starting.
-- NOTE NOTE NOTE NOTE NOTE NOTE NOTE NOTE NOTE NOTE NOTE NOTE NOTE NOTE NOTE 
 
-- Contains information on all the message types for propagation

CREATE TABLE sys.aq$_message_types(
        queue_oid       RAW(16),                -- object id of the source 
						-- queue
        schema_name     VARCHAR(30),            -- schema name at destination
        queue_name      VARCHAR(30),            -- queue name at destination
        destination     VARCHAR2(128), 		-- dblink, for now
        toid            RAW(16),                -- msg toid at destination 
        version 	NUMBER,
        verified        VARCHAR(1),             -- 'T' if types are equal
                                                -- 'F' otherwise 
CONSTRAINT aq$_msgtypes_primary PRIMARY KEY (queue_oid, schema_name, 
                                             queue_name, destination))
/

-- aq$_propagation_status contains status information about propagation.
-- Sender:
--	queue_id	- Event id of source queue
--	destination 	- dblink address that is target of propagation
--	sequence	- Sequence # of messages that have been sent
--	status		- one of 1 (sent) or 3 (prepared)
-- Receiver:
--	queue_id	- Event id of source queue
--	destination	- dblink address that is source of propagation
--	sequence	- Sequence # of messages that have been received
--	status		- 2 (received)
-- Unused fields:
--	txnid		- Transaction id used by sender for a batch
--	destqueue_id	- Target queue's identifier
--	flags		- type of entry in this table

CREATE TABLE sys.aq$_propagation_status(
	queue_id	NUMBER,		   	    -- Event id of source queue
	destination	VARCHAR2(128),			      -- dblink address
	sequence	NUMBER,	  -- sequence # of messages that have been sent
	status		NUMBER,		      		      -- one of kwqppst
	txnid		VARCHAR2(22),				      -- unused
	destqueue_id	NUMBER,					      -- unused
	flags		NUMBER,					      -- unused
CONSTRAINT aq$_propagation_status_primary PRIMARY KEY (queue_id, destination))
/

-- aq$_pending_messages contains message ids and rowids of messages that
-- have been prepared to be sent. This table is only used in the sender side.
-- Enrties are inserted into this table either when a) sender goes to the
-- prepared state or b) sender needs to flush entries to the table when
-- the transaction size is larger than the stream size. A message in 
-- aq$_pending_messages may be propagated to several queues in the remote
-- database (i.e there could be duplicate msgids for the same sequence #). 
-- The copy column in aq$_pending_messages uniquely identifies which remote 
-- queue generated the pmsgid message id on enqueue. If there are n copies for
-- msgid m, the ith copy's pmsgid column is the propagated msgid for the ith 
-- unique address that received this message at the specified destination and 
-- had a type match with the local queue. The copy columns are 1-based. Note
-- that propagator may not send messages even if the type matches if the
-- expiration time has passed. This is OK because we only require that the
-- copy column be in the same order as message sends to a destination. If
-- expiration causes propagation not to send a message to one queue it will
-- not send the message to all subsequent queues.

-- Entries are inserted into sys.aq$_pending_messages during propagation before
-- sending a commit command to the remote database. However, entries are
-- deleted from this table in a deferred fashion (currently it is done
-- as part of updating the schedule) to reduce amount of work to be done
-- during every propagation batch. We must be careful to delete only those
-- entries that are no longer required. Entries are required only when
-- the status column for the corresponding sequence# in aq$_propagation_status
-- is in the prepared state (note that this covers the case where message ids
-- are flushed to disk when propagating a large transaction group). To
-- avoid a join in the purge of the table below we delete all entries whose 
-- sequence# is < minimum of all sequence#s in aq$_propagation_status.

CREATE TABLE sys.aq$_pending_messages(
	sequence	NUMBER,    -- sequence #of messages that have been sent
	msgid		RAW(16),         -- message id sent with above sequence
	copy		NUMBER, 			-- copy# of above msgid
	pmsgid		RAW(16),	   	       -- propagated message id
	txnid		VARCHAR2(22),				      -- unused
	flags		NUMBER)					      -- unused
/

-- Note that the index on sequence in the above table is removed as of 8.1.4
-- since efficient access to this table for a given sequence# will
-- be required only during propagation recovery

Rem CREATE INDEX sys.aq$_pending_messages_i 
Rem 		 ON sys.aq$_pending_messages(sequence)
Rem /

-- sequence for generating montonically increasing values for each batch of
-- messages. Note that we do not use the ORDER clause because we assume that
-- the propagation scheduler will schedule propagation for a given source queue
-- from the same instance.
CREATE SEQUENCE sys.aq$_propagation_sequence START WITH 1
/

-- contains information about owner instances and their
-- incarnation number (for statistics in OPS) 

CREATE TABLE sys.aq$_queue_statistics(
        eventid        NUMBER,                -- the eventid of the queue
                                              -- unique in a database
        owner_inst     NUMBER,                -- instance number of the owner
                                              -- instance
        incarn_num     NUMBER,                -- incarnation number of the 
                                              -- instance
CONSTRAINT aq$_queue_statitics_pk PRIMARY KEY (eventid))
/

-- NOTE NOTE NOTE NOTE NOTE NOTE NOTE NOTE NOTE NOTE NOTE NOTE NOTE NOTE NOTE 
-- Please do not add tables needed by the time manager after aq$_schedules.
-- Reason: Time manager checks for the existence of aq$_schedules before 
-- starting.
-- NOTE NOTE NOTE NOTE NOTE NOTE NOTE NOTE NOTE NOTE NOTE NOTE NOTE NOTE NOTE 
 
-- Add aq$ tables to noexp$
delete from noexp$ where name like 'AQ$_%'
/
insert into noexp$ (owner, name, obj_type) 
values('SYSTEM', 'AQ$_QUEUE_TABLES', 2)
/
insert into noexp$ (owner, name, obj_type) 
values('SYSTEM', 'AQ$_QUEUES', 2)
/
insert into noexp$ (owner, name, obj_type)
values('SYSTEM', 'AQ$_SCHEDULES', 2)
/
commit
/

-- Create entries to provide EXP/IMP procedural object support for AQ
delete from sys.exppkgact$ 
where package = 'DBMS_AQ_EXP_QUEUE_TABLES'
or package = 'DBMS_AQ_EXP_INDEX_TABLES'
or package = 'DBMS_AQ_EXP_TIMEMGR_TABLES'
or package = 'DBMS_AQ_EXP_HISTORY_TABLES'
or package = 'DBMS_AQ_EXP_SUBSCRIBER_TABLES'
/
delete from sys.exppkgobj$ where package = 'DBMS_AQ_EXP_QUEUES'
/
insert into sys.exppkgact$(package, schema, class, level#)
values('DBMS_AQ_EXP_QUEUE_TABLES', 'SYS', 3, 1000)
/
insert into sys.exppkgact$(package, schema, class, level#)
values('DBMS_AQ_EXP_INDEX_TABLES', 'SYS', 3, 2000)
/
insert into sys.exppkgact$(package, schema, class, level#)
values('DBMS_AQ_EXP_TIMEMGR_TABLES', 'SYS', 3, 2000)
/
insert into sys.exppkgact$(package, schema, class, level#)
values('DBMS_AQ_EXP_HISTORY_TABLES', 'SYS', 3, 2000)
/
insert into sys.exppkgact$(package, schema, class, level#)
values('DBMS_AQ_EXP_SUBSCRIBER_TABLES', 'SYS', 3, 2000)
/
insert into sys.exppkgobj$(package, schema, class, type#, prepost, level#) 
values('DBMS_AQ_EXP_QUEUES', 'SYS', 3, 24, 1, 1000)
/
commit
/

-- Create the view DBA_QUEUE_TABLES
create or replace view DBA_QUEUE_TABLES
as 
select t.schema OWNER, t.name QUEUE_TABLE, 
     decode(t.udata_type, 1 , 'OBJECT', 2, 'VARIANT', 3, 'RAW') TYPE,
     u.name || '.' || o.name OBJECT_TYPE,
     decode(t.sort_cols, 0, 'NONE', 1, 'PRIORITY', 2, 'ENQUEUE_TIME',
			       3, 'PRIORITY, ENQUEUE_TIME', 
			       7, 'ENQUEUE_TIME, PRIORITY') SORT_ORDER,
     decode(bitand(t.flags, 1), 1, 'MULTIPLE', 0, 'SINGLE') RECIPIENTS,
     decode(bitand(t.flags, 2), 2, 'TRANSACTIONAL', 0, 'NONE')MESSAGE_GROUPING,
     decode(bitand(t.flags, 8), 8, '8.1.3', 0, '8.0.3')COMPATIBLE,
     aft.primary_instance PRIMARY_INSTANCE,
     aft.secondary_instance SECONDARY_INSTANCE,
     aft.owner_instance OWNER_INSTANCE,
     substr(t.table_comment, 1, 50) USER_COMMENT
from system.aq$_queue_tables t, sys.col$ c, sys.coltype$ ct, sys.obj$ o,
sys.user$ u, sys.aq$_queue_table_affinities aft
where c.intcol# = ct.intcol# 
and c.obj# = ct.obj# 
and c.name = 'USER_DATA' 
and t.objno = c.obj# 
and o.oid$ = ct.toid
and o.type# = 13
and o.owner# = u.user#
and t.objno = aft.table_objno
union
select t.schema OWNER, t.name QUEUE_TABLE, 
     decode(t.udata_type, 1 , 'OBJECT', 2, 'VARIANT', 3, 'RAW') TYPE, 
     null OBJECT_TYPE,
     decode(t.sort_cols, 0, 'NONE', 1, 'PRIORITY', 2, 'ENQUEUE_TIME',
			       3, 'PRIORITY, ENQUEUE_TIME', 
			       7, 'ENQUEUE_TIME, PRIORITY') SORT_ORDER,
     decode(bitand(t.flags, 1), 1, 'MULTIPLE', 0, 'SINGLE') RECIPIENTS,
     decode(bitand(t.flags, 2), 2, 'TRANSACTIONAL', 0, 'NONE')MESSAGE_GROUPING,
     decode(bitand(t.flags, 8), 8, '8.1.3', 0, '8.0.3')COMPATIBLE,
     aft.primary_instance PRIMARY_INSTANCE,
     aft.secondary_instance SECONDARY_INSTANCE,
     aft.owner_instance OWNER_INSTANCE,
     substr(t.table_comment, 1, 50) USER_COMMENT
from system.aq$_queue_tables t, sys.aq$_queue_table_affinities aft
where (t.udata_type = 2
or t.udata_type = 3) 
and t.objno = aft.table_objno 
/
comment on table DBA_QUEUE_TABLES is
'All queue tables created in the database'
/
comment on column DBA_QUEUE_TABLES.OWNER is
'Owner of the queue table'
/
comment on column DBA_QUEUE_TABLES.QUEUE_TABLE is
'Name of the queue table'
/
comment on column DBA_QUEUE_TABLES.TYPE is
'Name of the payload type'
/
comment on column DBA_QUEUE_TABLES.OBJECT_TYPE is
'Name of the payload type for object type payload'
/
comment on column DBA_QUEUE_TABLES.SORT_ORDER is
'Sort order for the queue table'
/
comment on column DBA_QUEUE_TABLES.RECIPIENTS is
'Mulitple or single recipient queue'
/
comment on column DBA_QUEUE_TABLES.MESSAGE_GROUPING is
'Transaction grouping'
/
comment on column DBA_QUEUE_TABLES.COMPATIBLE is
'Compatibility version of the queue table'
/
comment on column DBA_QUEUE_TABLES.PRIMARY_INSTANCE is
'Instance assigned as the primary owner of the queue table'
/
comment on column DBA_QUEUE_TABLES.SECONDARY_INSTANCE is
'Instance assigned as the secondary owner of the queue table'
/
comment on column DBA_QUEUE_TABLES.OWNER_INSTANCE is
'Instance which owns the queue table currently'
/
comment on column DBA_QUEUE_TABLES.USER_COMMENT is 
'User specified comment'
/
drop public synonym DBA_QUEUE_TABLES
/
create public synonym DBA_QUEUE_TABLES for SYS.DBA_QUEUE_TABLES
/
grant select on DBA_QUEUE_TABLES to SELECT_CATALOG_ROLE
/

-- Create the view ALL_QUEUE_TABLES
create or replace view ALL_QUEUE_TABLES
as 
select t.schema OWNER, t.name QUEUE_TABLE, 
     decode(t.udata_type, 1 , 'OBJECT', 2, 'VARIANT', 3, 'RAW') TYPE,
     u.name || '.' || o.name OBJECT_TYPE,
     decode(t.sort_cols, 0, 'NONE', 1, 'PRIORITY', 2, 'ENQUEUE_TIME',
			       3, 'PRIORITY, ENQUEUE_TIME', 
			       7, 'ENQUEUE_TIME, PRIORITY') SORT_ORDER,
     decode(bitand(t.flags, 1), 1, 'MULTIPLE', 0, 'SINGLE') RECIPIENTS,
     decode(bitand(t.flags, 2), 2, 'TRANSACTIONAL', 0, 'NONE')MESSAGE_GROUPING,
     decode(bitand(t.flags, 8), 8, '8.1.3', 0, '8.0.3')COMPATIBLE,
     aft.primary_instance PRIMARY_INSTANCE,
     aft.secondary_instance SECONDARY_INSTANCE,
     aft.owner_instance OWNER_INSTANCE,
     substr(t.table_comment, 1, 50) USER_COMMENT
from system.aq$_queue_tables t, sys.col$ c, sys.coltype$ ct, sys.obj$ o,
sys.user$ u, sys.aq$_queue_table_affinities aft
where c.intcol# = ct.intcol# 
and c.obj# = ct.obj# 
and c.name = 'USER_DATA' 
and t.objno = c.obj#
and o.oid$ = ct.toid
and o.type# = 13
and o.owner# = u.user#
and t.objno = aft.table_objno
and t.objno in
(select q.table_objno
 from system.aq$_queues q, sys.obj$ ro
 where ro.obj# = q.eventid
 and (ro.owner# = userenv('SCHEMAID')
      or ro.obj# in
	   (select oa.obj#
	    from sys.objauth$ oa
	    where grantee# in (select kzsrorol from x$kzsro))
      or exists (select null from v$enabledprivs
	         where priv_number in (-218 /* MANAGE ANY QUEUE */,
			 	       -219 /* ENQUEUE ANY QUEUE */,
                                       -220 /* DEQUEUE ANY QUEUE */))
      or ro.obj# in
           (select q.eventid from system.aq$_queues q, 
	                          system.aq$_queue_tables t
              where q.table_objno = t.objno
              and bitand(t.flags, 8) = 0
              and exists (select null from sys.objauth$ oa, sys.obj$ o
                          where oa.obj# = o.obj#
                          and (o.name = 'DBMS_AQ' or o.name = 'DBMS_AQADM')
                          and o.type# = 9
                          and oa.grantee# = userenv('SCHEMAID')))
     )
)
union
select t.schema OWNER, t.name QUEUE_TABLE, 
     decode(t.udata_type, 1 , 'OBJECT', 2, 'VARIANT', 3, 'RAW') TYPE, 
     null OBJECT_TYPE,
     decode(t.sort_cols, 0, 'NONE', 1, 'PRIORITY', 2, 'ENQUEUE_TIME',
			       3, 'PRIORITY, ENQUEUE_TIME', 
			       7, 'ENQUEUE_TIME, PRIORITY') SORT_ORDER,
     decode(bitand(t.flags, 1), 1, 'MULTIPLE', 0, 'SINGLE') RECIPIENTS,
     decode(bitand(t.flags, 2), 2, 'TRANSACTIONAL', 0, 'NONE')MESSAGE_GROUPING,
     decode(bitand(t.flags, 8), 8, '8.1.3', 0, '8.0.3')COMPATIBLE,
     aft.primary_instance PRIMARY_INSTANCE,
     aft.secondary_instance SECONDARY_INSTANCE,
     aft.owner_instance OWNER_INSTANCE,
     substr(t.table_comment, 1, 50) USER_COMMENT
from system.aq$_queue_tables t, sys.aq$_queue_table_affinities aft
where (t.udata_type = 2
or t.udata_type = 3) 
and t.objno = aft.table_objno
and t.objno in
(select q.table_objno
 from system.aq$_queues q, sys.obj$ ro
 where ro.obj# = q.eventid
 and (ro.owner# = userenv('SCHEMAID')
      or ro.obj# in
	   (select oa.obj#
	    from sys.objauth$ oa
	    where grantee# in (select kzsrorol from x$kzsro))
      or exists (select null from v$enabledprivs
	         where priv_number in (-218 /* MANAGE ANY QUEUE */,
			 	       -219 /* ENQUEUE ANY QUEUE */,
                                       -220 /* DEQUEUE ANY QUEUE */))
      or ro.obj# in
           (select q.eventid from system.aq$_queues q, 
	                          system.aq$_queue_tables t
              where q.table_objno = t.objno
              and bitand(t.flags, 8) = 0
              and exists (select null from sys.objauth$ oa, sys.obj$ o
                          where oa.obj# = o.obj#
                          and (o.name = 'DBMS_AQ' or o.name = 'DBMS_AQADM')
                          and o.type# = 9
                          and oa.grantee# = userenv('SCHEMAID')))
     )
)
/
comment on table ALL_QUEUE_TABLES is
'All queue tables accessible to the user'
/
comment on column ALL_QUEUE_TABLES.OWNER is
'Owner of the queue table'
/
comment on column ALL_QUEUE_TABLES.QUEUE_TABLE is
'Name of the queue table'
/
comment on column ALL_QUEUE_TABLES.TYPE is
'Name of the payload type'
/
comment on column ALL_QUEUE_TABLES.OBJECT_TYPE is
'Name of the payload type for object type payload'
/
comment on column ALL_QUEUE_TABLES.SORT_ORDER is
'Sort order for the queue table'
/
comment on column ALL_QUEUE_TABLES.RECIPIENTS is
'Mulitple or single recipient queue'
/
comment on column ALL_QUEUE_TABLES.MESSAGE_GROUPING is
'Transaction grouping'
/
comment on column ALL_QUEUE_TABLES.COMPATIBLE is
'Compatibility version of the queue table'
/
comment on column ALL_QUEUE_TABLES.PRIMARY_INSTANCE is
'Instance assigned as the primary owner of the queue table'
/
comment on column ALL_QUEUE_TABLES.SECONDARY_INSTANCE is
'Instance assigned as the secondary owner of the queue table'
/
comment on column ALL_QUEUE_TABLES.OWNER_INSTANCE is
'Instance which owns the queue table currently'
/
comment on column ALL_QUEUE_TABLES.USER_COMMENT is 
'User specified comment'
/
drop public synonym ALL_QUEUE_TABLES
/
create public synonym ALL_QUEUE_TABLES for SYS.ALL_QUEUE_TABLES
/
grant select on ALL_QUEUE_TABLES to PUBLIC with grant option
/

-- Create the view USER_QUEUE_TABLES
create or replace view USER_QUEUE_TABLES
as 
select t.name QUEUE_TABLE, 
     decode(t.udata_type, 1 , 'OBJECT', 2, 'VARIANT', 3, 'RAW') TYPE,
     tc.name || '.' || o.name OBJECT_TYPE,
     decode(t.sort_cols, 0, 'NONE', 1, 'PRIORITY', 2, 'ENQUEUE_TIME',
			       3, 'PRIORITY, ENQUEUE_TIME', 
			       7, 'ENQUEUE_TIME, PRIORITY') SORT_ORDER,
     decode(bitand(t.flags, 1), 1, 'MULTIPLE', 0, 'SINGLE') RECIPIENTS,
     decode(bitand(t.flags, 2), 2, 'TRANSACTIONAL', 0, 'NONE')MESSAGE_GROUPING,
     decode(bitand(t.flags, 8), 8, '8.1.3', 0, '8.0.3')COMPATIBLE,
     aft.primary_instance PRIMARY_INSTANCE,
     aft.secondary_instance SECONDARY_INSTANCE,
     aft.owner_instance OWNER_INSTANCE,
     substr(t.table_comment, 1, 50) USER_COMMENT
from system.aq$_queue_tables t, sys.col$ c, sys.coltype$ ct, sys.obj$ o,
sys.user$ tc, sys.user$ qc, sys.aq$_queue_table_affinities aft
where c.intcol# = ct.intcol# 
and c.obj# = ct.obj# 
and c.name = 'USER_DATA' 
and t.objno = c.obj# 
and o.oid$ = ct.toid
and o.type# = 13
and o.owner# = tc.user#
and qc.user# = USERENV('SCHEMAID')
and qc.name = t.schema
and t.objno = aft.table_objno
union
select t.name QUEUE_TABLE, 
     decode(t.udata_type, 1 , 'OBJECT', 2, 'VARIANT', 3, 'RAW') TYPE, 
     null OBJECT_TYPE,
     decode(t.sort_cols, 0, 'NONE', 1, 'PRIORITY', 2, 'ENQUEUE_TIME',
			       3, 'PRIORITY, ENQUEUE_TIME', 
			       7, 'ENQUEUE_TIME, PRIORITY') SORT_ORDER,
     decode(bitand(t.flags, 1), 1, 'MULTIPLE', 0, 'SINGLE') RECIPIENTS,
     decode(bitand(t.flags, 2), 2, 'TRANSACTIONAL', 0, 'NONE')MESSAGE_GROUPING,
     decode(bitand(t.flags, 8), 8, '8.1.3', 0, '8.0.3')COMPATIBLE,
     aft.primary_instance PRIMARY_INSTANCE,
     aft.secondary_instance SECONDARY_INSTANCE,
     aft.owner_instance OWNER_INSTANCE,
     substr(t.table_comment, 1, 50) USER_COMMENT
from system.aq$_queue_tables t, sys.user$ qc,
     sys.aq$_queue_table_affinities aft 
where (t.udata_type = 2
or t.udata_type = 3)
and qc.user# = USERENV('SCHEMAID')
and qc.name  = t.schema
and t.objno = aft.table_objno
/
comment on table USER_QUEUE_TABLES is
'All queue tables created by the user'
/
comment on column USER_QUEUE_TABLES.QUEUE_TABLE is
'Name of the queue table'
/
comment on column USER_QUEUE_TABLES.TYPE is
'Name of the payload type'
/
comment on column USER_QUEUE_TABLES.OBJECT_TYPE is
'Name of the payload type for object type payload'
/
comment on column USER_QUEUE_TABLES.SORT_ORDER is
'Sort order for the queue table'
/
comment on column USER_QUEUE_TABLES.RECIPIENTS is
'Mulitple or single recipient queue'
/
comment on column USER_QUEUE_TABLES.MESSAGE_GROUPING is
'Transaction grouping'
/
comment on column USER_QUEUE_TABLES.COMPATIBLE is
'Compatibility version of the queue table'
/
comment on column USER_QUEUE_TABLES.PRIMARY_INSTANCE is
'Instance assigned as the primary owner of the queue table'
/
comment on column USER_QUEUE_TABLES.SECONDARY_INSTANCE is
'Instance assigned as the secondary owner of the queue table'
/
comment on column USER_QUEUE_TABLES.OWNER_INSTANCE is
'Instance which owns the queue table currently'
/
comment on column USER_QUEUE_TABLES.USER_COMMENT is 
'User specified comment'
/
-- Create a synonym for the USER_QUEUE_TABLES view
drop public synonym USER_QUEUE_TABLES
/
create public synonym USER_QUEUE_TABLES for SYS.USER_QUEUE_TABLES
/
grant select on USER_QUEUE_TABLES to PUBLIC with grant option
/

--
-- Create the view DBA_QUEUES 
--
create or replace view DBA_QUEUES
as
select u.name OWNER, q.name NAME, t.name QUEUE_TABLE, q.eventid QID,
       decode(q.usage, 1, 'EXCEPTION_QUEUE', 2, 'NON_PERSISTENT_QUEUE', 
              'NORMAL_QUEUE') QUEUE_TYPE,
       q.max_retries MAX_RETRIES, q.retry_delay RETRY_DELAY,
       decode(bitand(q.enable_flag, 1), 1 , '  YES  ', '  NO  ')ENQUEUE_ENABLED,
       decode(bitand(q.enable_flag, 2), 2 , '  YES  ', '  NO  ')DEQUEUE_ENABLED,
       decode(q.ret_time, -1, ' FOREVER', q.ret_time) RETENTION,
       substr(q.queue_comment, 1, 50) USER_COMMENT
from system.aq$_queues q, system.aq$_queue_tables t, sys.user$ u
where u.name  = t.schema
and   q.table_objno = t.objno
/
comment on table DBA_QUEUES is
'All database queues'
/
comment on column DBA_QUEUES.OWNER is
'Owner of the queue'
/
comment on column DBA_QUEUES.NAME is
'Name of the queue'
/
comment on column DBA_QUEUES.QUEUE_TABLE is
'Name of the table the queue data resides in'
/
comment on column DBA_QUEUES.QID is
'Object number of the queue'
/
comment on column DBA_QUEUES.QUEUE_TYPE is
'Type of the queue'
/
comment on column DBA_QUEUES.MAX_RETRIES is
'Maximum number of retries allowed when dequeuing from the queue'
/
comment on column DBA_QUEUES.RETRY_DELAY is
'Time interval between retries'
/
comment on column DBA_QUEUES.ENQUEUE_ENABLED is
'Queue is enabled for enqueue'
/
comment on column DBA_QUEUES.DEQUEUE_ENABLED is
'Queue is enabled for dequeue'
/
comment on column DBA_QUEUES.RETENTION is
'Time interval processed messages retained in the queue'
/
comment on column DBA_QUEUES.USER_COMMENT is 
'User specified comment'
/
drop public synonym DBA_QUEUES
/
create public synonym DBA_QUEUES for DBA_QUEUES
/
grant select on DBA_QUEUES to SELECT_CATALOG_ROLE
/

-- Create the view ALL_QUEUES 
-- This view displays all queues that the user has either ENQUEUE or
-- DEQUEUE privilege on.  If the user has any AQ system privileges,
-- like ENQUEUE_ANY, DEQUEUE_ANY or MANAGE_ANY, all the queues in
-- the system will be displayed by this view.
create or replace view ALL_QUEUES
as
select u.name OWNER, q.name NAME, t.name QUEUE_TABLE, q.eventid QID,
       decode(q.usage, 1, 'EXCEPTION_QUEUE', 2, 'NON_PERSISTENT_QUEUE', 
	      'NORMAL_QUEUE') QUEUE_TYPE,
       q.max_retries MAX_RETRIES, q.retry_delay RETRY_DELAY,
       decode(bitand(q.enable_flag, 1), 1 , '  YES  ', '  NO  ')ENQUEUE_ENABLED,
       decode(bitand(q.enable_flag, 2), 2 , '  YES  ', '  NO  ')DEQUEUE_ENABLED,
       decode(q.ret_time, -1, ' FOREVER', q.ret_time) RETENTION,
       substr(q.queue_comment, 1, 50) USER_COMMENT
from system.aq$_queues q, system.aq$_queue_tables t, sys.user$ u, sys.obj$ ro
where u.name  = t.schema
and   q.table_objno = t.objno
and   ro.owner# = u.user#
and   ro.obj# = q.eventid
and  (ro.owner# = userenv('SCHEMAID')
      or ro.obj# in
	   (select oa.obj#
	    from sys.objauth$ oa
	    where grantee# in (select kzsrorol from x$kzsro))
      or exists (select null from v$enabledprivs
	         where priv_number in (-218 /* MANAGE ANY QUEUE */,
			 	       -219 /* ENQUEUE ANY QUEUE */,
				       -220 /* DEQUEUE ANY QUEUE */))
      or ro.obj# in
           (select q.eventid from system.aq$_queues q, 
	                          system.aq$_queue_tables t
              where q.table_objno = t.objno
              and bitand(t.flags, 8) = 0
              and exists (select null from sys.objauth$ oa, sys.obj$ o
                          where oa.obj# = o.obj#
                          and (o.name = 'DBMS_AQ' or o.name = 'DBMS_AQADM')
                          and o.type# = 9
                          and oa.grantee# = userenv('SCHEMAID')))          
     )
/
comment on table ALL_QUEUES is
'All queues accessible to the user'
/
comment on column ALL_QUEUES.OWNER is
'Owner of the queue'
/
comment on column ALL_QUEUES.NAME is
'Name of the queue'
/
comment on column ALL_QUEUES.QUEUE_TABLE is
'Name of the table the queue data resides in'
/
comment on column ALL_QUEUES.QID is
'Object number of the queue'
/
comment on column ALL_QUEUES.QUEUE_TYPE is
'Type of the queue'
/
comment on column ALL_QUEUES.MAX_RETRIES is
'Maximum number of retries allowed when dequeuing from the queue'
/
comment on column ALL_QUEUES.RETRY_DELAY is
'Time interval between retries'
/
comment on column ALL_QUEUES.ENQUEUE_ENABLED is
'Queue is enabled for enqueue'
/
comment on column ALL_QUEUES.DEQUEUE_ENABLED is
'Queue is enabled for dequeue'
/
comment on column ALL_QUEUES.RETENTION is
'Time interval processed messages retained in the queue'
/
comment on column ALL_QUEUES.USER_COMMENT is 
'User specified comment'
/
drop public synonym ALL_QUEUES
/
create public synonym ALL_QUEUES for ALL_QUEUES
/
grant select on ALL_QUEUES to PUBLIC with grant option
/

--
-- Create the view USER_QUEUES
--
create or replace view USER_QUEUES
as
select q.name NAME, t.name QUEUE_TABLE, q.eventid QID,
       decode(q.usage, 1, 'EXCEPTION_QUEUE', 2, 'NON_PERSISTENT_QUEUE', 
              'NORMAL_QUEUE') QUEUE_TYPE,
       q.max_retries MAX_RETRIES, q.retry_delay RETRY_DELAY,
       decode(bitand(q.enable_flag, 1), 1 , '  YES  ', '  NO  ')ENQUEUE_ENABLED,
       decode(bitand(q.enable_flag, 2), 2 , '  YES  ', '  NO  ')DEQUEUE_ENABLED,
       decode(q.ret_time, -1, ' FOREVER', q.ret_time) RETENTION,
       substr(q.queue_comment, 1, 50) USER_COMMENT
from system.aq$_queues q, system.aq$_queue_tables t, sys.user$ u
where u.user# = USERENV('SCHEMAID')
and   u.name  = t.schema
and   q.table_objno = t.objno
/
comment on table USER_QUEUES is
'All queues owned by the user'
/
comment on column USER_QUEUES.NAME is
'Name of the queue'
/
comment on column USER_QUEUES.QUEUE_TABLE is
'Name of the table the queue data resides in'
/
comment on column USER_QUEUES.QID is
'Object number of the queue'
/
comment on column USER_QUEUES.QUEUE_TYPE is
'Type of the queue'
/
comment on column USER_QUEUES.MAX_RETRIES is
'Maximum number of retries allowed when dequeuing from the queue'
/
comment on column USER_QUEUES.RETRY_DELAY is
'Time interval between retries'
/
comment on column USER_QUEUES.ENQUEUE_ENABLED is
'Queue is enabled for enqueue'
/
comment on column USER_QUEUES.DEQUEUE_ENABLED is
'Queue is enabled for dequeue'
/
comment on column USER_QUEUES.RETENTION is
'Time interval processed messages retained in the queue'
/
comment on column USER_QUEUES.USER_COMMENT is 
'User specified comment'
/
drop public synonym USER_QUEUES
/
create public synonym USER_QUEUES for USER_QUEUES
/
grant select on USER_QUEUES to PUBLIC with grant option
/

-- Create the view DBA_QUEUE_SCHEDULES
-- This view provides all the details of all the propagation schedules
-- This includes scheduling parameters (start_time, duration, latency,
-- next_time, destination), qschema, qname, SNP process name and (session
-- ID, serial) if the schedule is in progress, statistics such as total and
-- averages of messages/bytes sent, message size, schedules status (Disabled/
-- enabled) and information about the last error (message, time) if one 
-- occured.

create or replace view DBA_QUEUE_SCHEDULES
as
select t.schema SCHEMA, q.name QNAME, 
       s.destination DESTINATION, s.start_time START_DATE,
       substr(to_char(s.start_time,'HH24:MI:SS'),1,8) START_TIME,
       to_number(s.duration) PROPAGATION_WINDOW,
       s.next_time NEXT_TIME, to_number(s.latency) LATENCY,
       s.disabled SCHEDULE_DISABLED, s.process_name PROCESS_NAME, 
       decode(s.sid, NULL, NULL, 
         concat(to_char(s.sid), concat(', ',to_char(s.serial)))) SESSION_ID,
       s.instance INSTANCE, s.last_run LAST_RUN_DATE, 
       substr(to_char(s.last_run,'HH24:MI:SS'),1,8) LAST_RUN_TIME,
       s.cur_start_time CURRENT_START_DATE, 
       substr(to_char(s.cur_start_time,'HH24:MI:SS'),1,8) CURRENT_START_TIME,
       s.next_run NEXT_RUN_DATE, 
       substr(to_char(s.next_run,'HH24:MI:SS'),1,8) NEXT_RUN_TIME,
       s.total_time TOTAL_TIME, s.total_msgs TOTAL_NUMBER, 
       s.total_bytes TOTAL_BYTES,
       s.max_num_per_win MAX_NUMBER, s.max_size MAX_BYTES,
       s.total_msgs/decode(s.total_windows, 0, 1, s.total_windows) AVG_NUMBER, 
       s.total_bytes/decode(s.total_msgs, 0, 1, s.total_msgs) AVG_SIZE, 
       s.total_time/decode(s.total_msgs, 0, 1, s.total_msgs) AVG_TIME,
       s.failures FAILURES, s.error_time LAST_ERROR_DATE,
       substr(to_char(s.error_time,'HH24:MI:SS'),1,8) LAST_ERROR_TIME,
       s.last_error_msg LAST_ERROR_MSG
from system.aq$_queues q, system.aq$_queue_tables t, 
     sys.aq$_schedules s
where s.oid  = q.oid
and   q.table_objno = t.objno
/
drop public synonym DBA_QUEUE_SCHEDULES
/
create public synonym DBA_QUEUE_SCHEDULES for DBA_QUEUE_SCHEDULES
/
grant select on DBA_QUEUE_SCHEDULES to SELECT_CATALOG_ROLE
/
 
-- Create the view USER_QUEUE_SCHEDULES
-- This view provides all the details of the propagation schedules whose
-- source queues reside in the user's schema.
-- This includes scheduling parameters (start_time, duration, latency,
-- next_time, destination), qschema, qname, SNP process name and (session
-- ID, serial) if the schedule is in progress, statistics such as total and
-- averages of messages/bytes sent, message size, schedules status (Disabled/
-- enabled) and information about the last error (message, time) if one 
-- occured.
 
create or replace view USER_QUEUE_SCHEDULES
as
select q.name QNAME, 
       s.destination DESTINATION, s.start_time START_DATE,
       substr(to_char(s.start_time,'HH24:MI:SS'),1,8) START_TIME,
       to_number(s.duration) PROPAGATION_WINDOW,
       s.next_time NEXT_TIME, to_number(s.latency) LATENCY,
       s.disabled SCHEDULE_DISABLED, s.process_name PROCESS_NAME, 
       decode(s.sid, NULL, NULL, 
         concat(to_char(s.sid), concat(', ',to_char(s.serial)))) SESSION_ID,
       s.instance INSTANCE, s.last_run LAST_RUN_DATE, 
       substr(to_char(s.last_run,'HH24:MI:SS'),1,8) LAST_RUN_TIME,
       s.cur_start_time CURRENT_START_DATE, 
       substr(to_char(s.cur_start_time,'HH24:MI:SS'),1,8) CURRENT_START_TIME,
       s.next_run NEXT_RUN_DATE, 
       substr(to_char(s.next_run,'HH24:MI:SS'),1,8) NEXT_RUN_TIME,
       s.total_time TOTAL_TIME, s.total_msgs TOTAL_NUMBER, 
       s.total_bytes TOTAL_BYTES,
       s.max_num_per_win MAX_NUMBER, s.max_size MAX_BYTES,
       s.total_msgs/decode(s.total_windows, 0, 1, s.total_windows) AVG_NUMBER, 
       s.total_bytes/decode(s.total_msgs, 0, 1, s.total_msgs) AVG_SIZE, 
       s.total_time/decode(s.total_msgs, 0, 1, s.total_msgs) AVG_TIME,
       s.failures FAILURES, s.error_time LAST_ERROR_DATE,
       substr(to_char(s.error_time,'HH24:MI:SS'),1,8) LAST_ERROR_TIME,
       s.last_error_msg LAST_ERROR_MSG
from system.aq$_queues q, system.aq$_queue_tables t, 
     sys.aq$_schedules s, sys.user$ u
where u.user# = USERENV('SCHEMAID')
and   u.name  = t.schema
and   s.oid  = q.oid
and   q.table_objno = t.objno
/
drop public synonym USER_QUEUE_SCHEDULES
/
create public synonym USER_QUEUE_SCHEDULES for USER_QUEUE_SCHEDULES
/
grant select on USER_QUEUE_SCHEDULES to PUBLIC with grant option
/

-- Create the view QUEUE_PRIVILEGES
-- This view displays all AQ object level privileges granted to or
-- granted by the user.

create or replace view QUEUE_PRIVILEGES
as 
select ue.name GRANTEE, u.name OWNER, o.name NAME, ur.name GRANTOR,
decode(sum(privilege#), 20, 1, 41, 1, 0) ENQUEUE_PRIVILEGE, 
decode(sum(oa.privilege#), 21, 1, 41, 1, 0) DEQUEUE_PRIVILEGE
from sys.objauth$ oa, sys.obj$ o, sys.user$ ue, sys.user$ ur, sys.user$ u
where oa.obj# = o.obj#
  and o.type# = 24
  and oa.grantor# = ur.user#
  and oa.grantee# = ue.user#
  and u.user# = o.owner#
  and (oa.grantor# = userenv('SCHEMAID') or
       oa.grantee# in (select kzsrorol from x$kzsro) or
       o.owner# = userenv('SCHEMAID'))
group by u.name, o.name, ur.name, ue.name
/
comment on table QUEUE_PRIVILEGES is
'Grants on queues for which the user is the grantor, grantee, owner, 
 or an enabled role or PUBLIC is the grantee'
/
comment on column QUEUE_PRIVILEGES.GRANTEE is
'Name of the user to whom access was granted'
/
comment on column QUEUE_PRIVILEGES.OWNER is
'Owner of the object'
/
comment on column QUEUE_PRIVILEGES.NAME is
'Name of the object'
/
comment on column QUEUE_PRIVILEGES.GRANTOR is
'Name of the user who performed the grant'
/
comment on column QUEUE_PRIVILEGES.ENQUEUE_PRIVILEGE is
'Permission to ENQUEUE to the queue'
/
comment on column QUEUE_PRIVILEGES.DEQUEUE_PRIVILEGE is
'Permission to DEQUEUE from the queue'
/
drop public synonym QUEUE_PRIVILEGES
/
create public synonym QUEUE_PRIVILEGES for QUEUE_PRIVILEGES
/
grant select on QUEUE_PRIVILEGES to PUBLIC with grant option
/
 
-- Create GV$AQ and V$AQ views for AQ statistics

create or replace view v_$aq as
  select QID, WAITING, READY, EXPIRED, TOTAL_WAIT,
         AVERAGE_WAIT from v$aq1
/ 
drop public synonym v$aq
/
create public synonym v$aq for v_$aq
/
grant select on v_$aq to select_catalog_role
/

create or replace view gv_$aq as
 select qid, max(WAITING) waiting, max(READY) ready, max(EXPIRED) expired,
   max(TOTAL_WAIT) total_wait,
   decode(max(TOTAL_CONSUMERS), 
              0, 0, max(TOTAL_WAIT)/max(TOTAL_CONSUMERS)) average_wait
 from   gv$aq1 group by qid
/
drop public synonym gv$aq
/
create public synonym gv$aq for gv_$aq
/
grant select on gv_$aq to select_catalog_role
/

-- Creating a sequence for generating message identifiers for messages
CREATE SEQUENCE sys.aq$_chainseq START WITH 1
/

-- Creating a sequence for  iot enqueue transaction id
CREATE SEQUENCE sys.aq$_iotenqtxid START WITH 1 CACHE 1000
/
 
-- Create the library where 3GL callouts will reside
CREATE OR REPLACE LIBRARY dbms_aq_lib trusted as static
/

-- Create the library where 3GL callouts will reside
CREATE OR REPLACE LIBRARY dbms_aqadm_lib trusted as static
/

-- Load AQ interface package specifications and bodies
-- Be carefull with the dependency when changing the order
-- the packages being loaded.
@@dbmsaq.plb
@@prvtaq.plb
@@dbmsaqad.sql
@@dbmsaqds.plb
@@dbmsaq8x.plb
@@prvtaqdi.plb
@@prvtaqxi.plb
@@prvtaqxe.plb
@@prvtaqip.plb
@@prvtaqis.plb
@@prvtaqim.plb
@@prvtaqad.plb
@@prvtaq8x.plb
@@prvtaqin.plb
@@prvtaqjm.plb
@@prvtaqmi.plb
@@prvtaqme.plb
@@prvtaqds.plb

--
-- Create and grant privileges to all the AQ system-defined roles
-- Notes:  The upgrade script should have revoked all privileges from
--         the role and have the privileges granted here.
--
-- Create the AQ administrator role
--
CREATE ROLE aq_administrator_role
/

BEGIN
dbms_aqadm.grant_system_privilege(privilege => 'MANAGE_ANY', grantee => 'AQ_ADMINISTRATOR_ROLE', admin_option => TRUE);
END;
/
BEGIN
dbms_aqadm.grant_system_privilege(privilege => 'ENQUEUE_ANY', grantee => 'AQ_ADMINISTRATOR_ROLE', admin_option => TRUE);
END;
/
BEGIN
dbms_aqadm.grant_system_privilege(privilege => 'DEQUEUE_ANY',grantee => 'AQ_ADMINISTRATOR_ROLE', admin_option => TRUE);
END;
/
GRANT SELECT ON DBA_QUEUE_TABLES TO aq_administrator_role
/
GRANT SELECT ON DBA_QUEUES TO aq_administrator_role
/
GRANT SELECT ON DBA_QUEUE_SCHEDULES TO aq_administrator_role
/
GRANT SELECT ON sys.v_$aq TO aq_administrator_role
/
GRANT SELECT ON sys.gv_$aq TO aq_administrator_role
/
GRANT SELECT ON sys.aq$_propagation_status TO aq_administrator_role
/
GRANT EXECUTE ON sys.dbms_aqadm TO aq_administrator_role
/
GRANT EXECUTE ON sys.dbms_aq TO aq_administrator_role
/
GRANT EXECUTE ON sys.dbms_aq_import_internal TO aq_administrator_role
/
GRANT EXECUTE ON sys.dbms_rule_eximp TO aq_administrator_role
/
GRANT EXECUTE ON sys.dbms_aqin TO aq_administrator_role
/
GRANT EXECUTE ON sys.dbms_aqjms TO aq_administrator_role
/

--
-- Create the AQ user role (obsoleted, kept here mainly for 8.0 compatibility)
--
CREATE ROLE aq_user_role
/
GRANT EXECUTE ON sys.dbms_aq TO aq_user_role
/
GRANT EXECUTE ON sys.dbms_aqin TO aq_user_role
/
GRANT EXECUTE ON sys.dbms_aqjms TO aq_user_role
/

--
--  Grant AQ_ADMINSTRATOR_ROLE to SYSTEM
--
GRANT aq_administrator_role TO system WITH ADMIN OPTION
/
GRANT EXECUTE ON sys.dbms_aqadm TO system WITH GRANT OPTION
/
GRANT EXECUTE ON sys.dbms_aq TO system WITH GRANT OPTION
/

--
-- Grant dbms_aq_import_internal
--  
GRANT EXECUTE ON sys.dbms_aq_import_internal TO SYSTEM WITH GRANT OPTION
/
GRANT EXECUTE ON sys.dbms_aq_import_internal TO imp_full_database
/
GRANT EXECUTE ON sys.dbms_aq_import_internal TO exp_full_database
/

--
-- Grant execute right to EXECUTE_CATALOG_ROLE
--
GRANT EXECUTE ON sys.dbms_aqadm TO execute_catalog_role
/
GRANT EXECUTE ON sys.dbms_aq_import_internal TO execute_catalog_role
/
GRANT EXECUTE ON sys.dbms_aq TO execute_catalog_role
/
GRANT EXECUTE ON sys.dbms_rule_eximp TO execute_catalog_role
/
GRANT EXECUTE ON sys.dbms_aqin TO execute_catalog_role
/
GRANT EXECUTE ON sys.dbms_aqjms TO execute_catalog_role
/

--
-- Grant 'MANAGE_ANY' to imp_full_database
-- Note: 'select any table' privilege is needed for full database export
--       'manage any queue' privilege is needed for full database import
--
GRANT EXECUTE ON sys.dbms_aqadm TO imp_full_database
/
BEGIN
dbms_aqadm.grant_system_privilege(privilege => 'MANAGE_ANY', grantee => 'IMP_FULL_DATABASE', admin_option => FALSE);
END;
/

-- Grant Enqueue, Dequeue and Manage ANY privilege to SYS
BEGIN
dbms_aqadm.grant_system_privilege(privilege => 'MANAGE_ANY', grantee => 'SYS', admin_option => TRUE);
END;
/
BEGIN
dbms_aqadm.grant_system_privilege(privilege => 'ENQUEUE_ANY', grantee => 'SYS', admin_option => TRUE);
END;
/
BEGIN
dbms_aqadm.grant_system_privilege(privilege => 'DEQUEUE_ANY',grantee => 'SYS', admin_option => TRUE);
END;
/
