bz_main.sql

oracle9i+j2ee开发aq oracle9i+j2ee开发aq oracle9i+j2ee开发aq

REM script name: bz_main.sql
REM creates and populates the BZ schema
REM 
REM assumes that &tbs and &temp tablespace exists locally
REM 
REM It sets up system parameters - job_queue_processes and aq_tm_processes
REM used by AQ, if not already set.
REM create bz_adm and bz users. It grants aq_administrator role to bz_adm 
REM and aq_user tole bz.
REM It creates aq queues in the schema bz_adm
REM
REM 
REM version: 9.0.1
REM change:  initial version
REM
REM =======================================================
REM cleanup section
REM =======================================================

SET VERIFY OFF

define bz_pass      = &1
define bz_adm_pass  = &2
define tbs          = &3
define ttbs         = &4
define master_pass  = &5

DROP USER bz_adm CASCADE;
DROP USER bz CASCADE;

SPOOL create_bz_schema

REM =======================================================
REM CREATE USERs
REM The user is assigned tablespaces and quota in separate
REM ALTER USER statements so that the CREATE USER statement
REM will succeed even if the &tbs and temp tablespaces do
REM not exist.
REM =======================================================

REM =======================================================
REM Create a common admin account for all AQ Demo
REM =======================================================

CREATE USER bz_adm IDENTIFIED BY &bz_adm_pass;
ALTER USER bz_adm DEFAULT TABLESPACE &tbs QUOTA UNLIMITED ON &tbs;
ALTER USER bz_adm TEMPORARY TABLESPACE &ttbs;

REM ALTER USER bz_adm DEFAULT TABLESPACE &tbs QUOTA ON &tbs UNLIMITED;
REM ALTER USER bz_adm TEMPORARY TABLESPACE &ttbs;

GRANT ALTER SESSION TO bz_adm;
GRANT CREATE CLUSTER TO bz_adm;
GRANT CREATE DATABASE LINK TO bz_adm;
GRANT CREATE SEQUENCE TO bz_adm;
GRANT CREATE SESSION TO bz_adm;
GRANT CREATE SYNONYM TO bz_adm;
GRANT CREATE TABLE TO bz_adm;
GRANT CREATE VIEW TO bz_adm;
GRANT CREATE CLUSTER TO bz_adm;
GRANT CREATE INDEXTYPE TO bz_adm;
GRANT CREATE OPERATOR TO bz_adm;
GRANT CREATE PROCEDURE TO bz_adm;
GRANT CREATE SEQUENCE TO bz_adm;
GRANT CREATE TABLE TO bz_adm;
GRANT CREATE TRIGGER TO bz_adm;
GRANT CREATE TYPE TO bz_adm;
GRANT aq_administrator_role TO bz_adm;
GRANT EXECUTE ON dbms_aq TO bz_adm;
GRANT EXECUTE ON dbms_aqadm TO bz_adm;


REM =======================================================
REM Create a user account for schema bz
REM =======================================================

CREATE USER bz IDENTIFIED BY &bz_pass;
GRANT CREATE SESSION TO bz;

GRANT EXECUTE ON dbms_aq to bz;
GRANT ExECUTE ON dbms_aqadm to bz;
GRANT aq_user_role to bz;

REM =======================================================
REM Set AQ system parameters - JOB_QUEUE_PROCESSES and
REM                            AQ_TM_PROCESSES
REM =======================================================
declare
	n number;
begin
	select value into n from v$parameter where name = 'job_queue_processes';
	if (n = 0) then
		execute immediate 'alter system set job_queue_processes = 2';
	end if;
	select value into n from v$parameter where name = 'aq_tm_processes';
	if (n = 0) then
		execute immediate 'alter system set aq_tm_processes = 2';
	end if;
end;
/
REM =======================================================
REM Create objects - message, queuetable, queue
REM =======================================================

CONNECT bz_adm/&bz_adm_pass
@@bz_cr_msgtyp.sql
@@bz_cr_q.sql

REM Grant required privileges to user bz to perform AQ operations
@@bz_priv.sql 

CONNECT bz_adm/&bz_adm_pass
@@bz_add_subscriber.sql

spool off