create_bz_schema.lst

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

SQL> 
SQL> REM =======================================================
SQL> REM CREATE USERs
SQL> REM The user is assigned tablespaces and quota in separate
SQL> REM ALTER USER statements so that the CREATE USER statement
SQL> REM will succeed even if the &tbs and temp tablespaces do
SQL> REM not exist.
SQL> REM =======================================================
SQL> 
SQL> REM =======================================================
SQL> REM Create a common admin account for all AQ Demo
SQL> REM =======================================================
SQL> 
SQL> CREATE USER bz_adm IDENTIFIED BY &bz_adm_pass;

User created.

SQL> ALTER USER bz_adm DEFAULT TABLESPACE &tbs QUOTA UNLIMITED ON &tbs;

User altered.

SQL> ALTER USER bz_adm TEMPORARY TABLESPACE &ttbs;

User altered.

SQL> 
SQL> REM ALTER USER bz_adm DEFAULT TABLESPACE &tbs QUOTA ON &tbs UNLIMITED;
SQL> REM ALTER USER bz_adm TEMPORARY TABLESPACE &ttbs;
SQL> 
SQL> GRANT ALTER SESSION TO bz_adm;

Grant succeeded.

SQL> GRANT CREATE CLUSTER TO bz_adm;

Grant succeeded.

SQL> GRANT CREATE DATABASE LINK TO bz_adm;

Grant succeeded.

SQL> GRANT CREATE SEQUENCE TO bz_adm;

Grant succeeded.

SQL> GRANT CREATE SESSION TO bz_adm;

Grant succeeded.

SQL> GRANT CREATE SYNONYM TO bz_adm;

Grant succeeded.

SQL> GRANT CREATE TABLE TO bz_adm;

Grant succeeded.

SQL> GRANT CREATE VIEW TO bz_adm;

Grant succeeded.

SQL> GRANT CREATE CLUSTER TO bz_adm;

Grant succeeded.

SQL> GRANT CREATE INDEXTYPE TO bz_adm;

Grant succeeded.

SQL> GRANT CREATE OPERATOR TO bz_adm;

Grant succeeded.

SQL> GRANT CREATE PROCEDURE TO bz_adm;

Grant succeeded.

SQL> GRANT CREATE SEQUENCE TO bz_adm;

Grant succeeded.

SQL> GRANT CREATE TABLE TO bz_adm;

Grant succeeded.

SQL> GRANT CREATE TRIGGER TO bz_adm;

Grant succeeded.

SQL> GRANT CREATE TYPE TO bz_adm;

Grant succeeded.

SQL> GRANT aq_administrator_role TO bz_adm;

Grant succeeded.

SQL> GRANT EXECUTE ON dbms_aq TO bz_adm;

Grant succeeded.

SQL> GRANT EXECUTE ON dbms_aqadm TO bz_adm;

Grant succeeded.

SQL> 
SQL> 
SQL> REM =======================================================
SQL> REM Create a user account for schema bz
SQL> REM =======================================================
SQL> 
SQL> CREATE USER bz IDENTIFIED BY &bz_pass;

User created.

SQL> GRANT CREATE SESSION TO bz;

Grant succeeded.

SQL> 
SQL> GRANT EXECUTE ON dbms_aq to bz;

Grant succeeded.

SQL> GRANT ExECUTE ON dbms_aqadm to bz;

Grant succeeded.

SQL> GRANT aq_user_role to bz;

Grant succeeded.

SQL> 
SQL> REM =======================================================
SQL> REM Set AQ system parameters - JOB_QUEUE_PROCESSES and
SQL> REM			    AQ_TM_PROCESSES
SQL> REM =======================================================
SQL> declare
  2  	     n number;
  3  begin
  4  	     select value into n from v$parameter where name = 'job_queue_processes';
  5  	     if (n = 0) then
  6  		     execute immediate 'alter system set job_queue_processes = 2';
  7  	     end if;
  8  	     select value into n from v$parameter where name = 'aq_tm_processes';
  9  	     if (n = 0) then
 10  		     execute immediate 'alter system set aq_tm_processes = 2';
 11  	     end if;
 12  end;
 13  /

PL/SQL procedure successfully completed.

SQL> REM =======================================================
SQL> REM Create objects - message, queuetable, queue
SQL> REM =======================================================
SQL> 
SQL> CONNECT bz_adm/&bz_adm_pass
Connected.
SQL> @@bz_cr_msgtyp.sql
SQL> REM script name: bz_cr_msgtyp.sql
SQL> REM creates the bzcardorder_typ Object type
SQL> REM
SQL> REM version: 9i
SQL> 
SQL> REM =======================================================
SQL> REM cleanup section
SQL> REM =======================================================
SQL> 
SQL> DROP TYPE bzcardorder_typ;
DROP TYPE bzcardorder_typ
*
ERROR at line 1:
ORA-04043: object BZCARDORDER_TYP does not exist 


SQL> 
SQL> REM =======================================================
SQL> REM Creates the bzcardorder_type. Users using this type would
SQL> REM need execute privilege on this type
SQL> REM =======================================================
SQL> 
SQL> CREATE OR REPLACE TYPE bzcardorder_typ AS OBJECT (
  2  	     employee_id     NUMBER(6),
  3  	     first_name      VARCHAR2(20),
  4  	     last_name	     VARCHAR2(25),
  5  	     ordtyp	     VARCHAR(10));
  6  /

Type created.

SQL> @@bz_cr_q.sql
SQL> REM script name: bz_cre.sql
SQL> REM Creates queue tables, queues - bzcardorders_q
SQL> REM Starts the queue
SQL> REM
SQL> REM version: 9i
SQL> REM
SQL> 
SQL> REM =======================================================
SQL> REM cleanup section
SQL> REM =======================================================
SQL> BEGIN
  2    dbms_aqadm.stop_queue(queue_name => 'bzcardorders_q');
  3    dbms_aqadm.drop_queue (
  4  	     queue_name 	     => 'bzcardorders_q');
  5    dbms_aqadm.drop_queue_table (
  6  	     queue_table => 'bzcardorders_qt');
  7  END;
  8  /
BEGIN
*
ERROR at line 1:
ORA-24010: QUEUE BZ_ADM.BZCARDORDERS_Q does not exist 
ORA-06512: at "SYS.DBMS_AQADM_SYS", line 3125 
ORA-06512: at "SYS.DBMS_AQADM", line 237 
ORA-06512: at line 2 


SQL> 
SQL> REM =======================================================
SQL> REM Create queue tables, queues for BZADM
SQL> REM =======================================================
SQL> 
SQL>  execute dbms_aqadm.create_queue_table(	      -
> 	  queue_table => 'bzcardorders_qt', -
> 	  comment => 'Business Card Orders queue table',  -
> 	  multiple_consumers => true, -
> 	  queue_payload_type => 'bzcardorder_typ',-
> 	  primary_instance => 1,-
> 	  secondary_instance => 2);

PL/SQL procedure successfully completed.

SQL> 
SQL> 
SQL> REM =======================================================
SQL> REM Create a queue
SQL> REM =======================================================
SQL> BEGIN
  2  dbms_aqadm.create_queue (
  3  	     queue_name 	     => 'bzcardorders_q',
  4  	     queue_table	     => 'bzcardorders_qt');
  5  END;
  6  /

PL/SQL procedure successfully completed.

SQL> 
SQL> REM =======================================================
SQL> REM start queue
SQL> REM =======================================================
SQL> BEGIN
  2    dbms_aqadm.start_queue (
  3  	     queue_name 	     => 'bzcardorders_q');
  4  END;
  5  /

PL/SQL procedure successfully completed.

SQL> 
SQL> REM Grant required privileges to user bz to perform AQ operations
SQL> @@bz_priv.sql
SQL> REM script name: bz_priv.sql
SQL> REM grants privileges to enqueue and dequeue to user bz
SQL> REM
SQL> REM version: 9.0.1
SQL> REM
SQL> 
SQL> REM ========================================================
SQL> REM Grant Enqueue and Dequeue privilege on bzcardorders_q to queue
SQL> REM to bz user
SQL> REM ========================================================
SQL> 
SQL> grant execute on bzcardorder_typ to bz;

Grant succeeded.

SQL> 
SQL> begin
  2  dbms_aqadm.grant_queue_privilege(privilege => 'ALL',
  3  				     queue_name => 'bzcardorders_q',
  4  				     grantee => 'bz',
  5  				     grant_option => TRUE);
  6  end;
  7  /

PL/SQL procedure successfully completed.

SQL> 
SQL> CONNECT bz_adm/&bz_adm_pass
Connected.
SQL> @@bz_add_subscriber.sql
SQL> REM script name: bz_add_subscriber.sql
SQL> REM This script adds a subscriber to bzcardorders_q
SQL> REM
SQL> REM version: 9i
SQL> 
SQL> REM =======================================================
SQL> REM cleanup section
SQL> REM =======================================================
SQL> execute dbms_aqadm.remove_subscriber ( -
> 	    queue_name => 'BZCARDORDERS_Q', -
> 	    subscriber => sys.aq$_agent('SHIPPING',null,null)-
> );
BEGIN dbms_aqadm.remove_subscriber (  	  queue_name => 'BZCARDORDERS_Q',  	  subscriber => sys.aq$_agent('SHIPPING',null,null) ); END;

*
ERROR at line 1:
ORA-24035: AQ agent SHIPPING is not a subscriber for queue 
BZ_ADM.BZCARDORDERS_Q 
ORA-06512: at "SYS.DBMS_AQADM_SYS", line 4475 
ORA-06512: at "SYS.DBMS_AQADM", line 387 
ORA-06512: at line 1 


SQL> 
SQL> execute dbms_aqadm.remove_subscriber ( -
> 	    queue_name => 'BZCARDORDERS_Q', -
> 	    subscriber => sys.aq$_agent('BILLING',null,null)-
> );
BEGIN dbms_aqadm.remove_subscriber (  	  queue_name => 'BZCARDORDERS_Q',  	  subscriber => sys.aq$_agent('BILLING',null,null) ); END;

*
ERROR at line 1:
ORA-24035: AQ agent BILLING is not a subscriber for queue BZ_ADM.BZCARDORDERS_Q 
ORA-06512: at "SYS.DBMS_AQADM_SYS", line 4475 
ORA-06512: at "SYS.DBMS_AQADM", line 387 
ORA-06512: at line 1 


SQL> 
SQL> execute dbms_aqadm.remove_subscriber ( -
> 	    queue_name => 'BZCARDORDERS_Q', -
> 	    subscriber => sys.aq$_agent('RUSH_ORDER',null,null)-
> );
BEGIN dbms_aqadm.remove_subscriber (  	  queue_name => 'BZCARDORDERS_Q',  	  subscriber => sys.aq$_agent('RUSH_ORDER',null,null) ); END;

*
ERROR at line 1:
ORA-24035: AQ agent RUSH_ORDER is not a subscriber for queue 
BZ_ADM.BZCARDORDERS_Q 
ORA-06512: at "SYS.DBMS_AQADM_SYS", line 4475 
ORA-06512: at "SYS.DBMS_AQADM", line 387 
ORA-06512: at line 1 


SQL> REM =======================================================
SQL> REM Add subscriber 'SHIPPING'and 'BILLING' to BZCARDORDERS_Q
SQL> REM =======================================================
SQL> execute dbms_aqadm.add_subscriber ( -
> 	    queue_name => 'BZCARDORDERS_Q', -
> 	    subscriber => sys.aq$_agent('SHIPPING',null,null)-
> );

PL/SQL procedure successfully completed.

SQL> 
SQL> quit;