oracle 删除队列表,转：如何迁移oracle 队列表

如何迁移队列表

广东生产环境需要从 9i 迁移到 10g ,虽然数据量不大导出数据只有4G。

但是ORACLE BPEL工单处理系统应用到高级列队。迁移Advanced Queuing

需要注意以下内容：

Export Modes & Queue Tables

----------------------------

1. Full database mode: Queue tables, all related metadata tables, system-level

grants, and primary and secondary object grants are exported automatically.

2. User mode: Queue tables, all related metadata tables, and primary object grants

are exported automatically. Please note that a user-level export from one schema

to another using the FROMUSER TOUSER clause is not supported. However, you can

only do that if you are dropping and recreating the schema in the same database,

or if you are sure that the target database does not have queue table(s) with

the same name.

3. Table mode: If you need to export a queue table in table mode then you must

export all related metadata tables. This mode is not recommended.

Importing Queue Tables

-----------------------

Similar to exporting queues, importing queues entails importing the underlying

queue tables and related dictionary data. After the queue table data is imported, the

import utility executes the PL/SQL anonymous blocks in the dump file to write the

metadata to the data dictionary. In the case of queue tables that support mutiple

receipients, the above list of associated metadata tables will be imported as well.

Please note that You should not import queue data into a queue table that already

contains data. The IGNORE parameter of the import utility should always be set to

NO when importing queue tables, otherwise the rows will be loaded from the dump file

into the existing table while at the same time the existing queue table and queue

definition will be dropped and re-created. Hence, queue table and queue definitions

prior to the import will be lost, and duplicate rows will appear in the queue table.

迁移步骤具体如下：

1. 新建用户：

-- Create the user

create user bpel

identified by bpel

default tablespace TS_ORABPEL

temporary tablespace TEMP

profile DEFAULT;

-- Grant/Revoke role privileges

grant aq_administrator_role to bpel;

grant connect to bpel;

grant resource to bpel;

grant create view to bpel;

-- Grant/Revoke system privileges

grant unlimited tablespace to bpel;

GRANT connect, resource,AQ_ADMINISTRATOR_ROLE TO bpel ;

GRANT execute ON sys.dbms_aqadm TO bpel;

GRANT execute ON sys.dbms_aq TO bpel;

GRANT execute ON sys.dbms_aqin TO bpel;

GRANT execute ON sys.dbms_aqjms TO bpel;

2. 新建队列表：

BEGIN

--创建Request的Table

DBMS_AQADM.CREATE_QUEUE_TABLE(

Queue_table => 'QT_BIZPROC_REQUEST',

Queue_payload_type => 'SYS.AQ$_JMS_TEXT_MESSAGE',

sort_list => 'PRIORITY,ENQ_TIME',

multiple_consumers => false,

compatible => '8.1.5');

DBMS_AQADM.CREATE_QUEUE_TABLE(

Queue_table => 'QT_BIZPROC_REPLY',

Queue_payload_type => 'SYS.AQ$_JMS_TEXT_MESSAGE',

sort_list => 'PRIORITY,ENQ_TIME',

multiple_consumers => true,

compatible => '8.1.5');

--创建Request Queue

DBMS_AQADM.CREATE_QUEUE(

Queue_name => 'BIZPROC_REQUEST_QUEUE',

Queue_table => 'QT_BIZPROC_REQUEST');

DBMS_AQADM.CREATE_QUEUE(

Queue_name => 'BIZPROC_REPLY_TOPIC',

Queue_table => 'QT_BIZPROC_REPLY');

DBMS_AQADM.START_QUEUE(

queue_name => 'BIZPROC_REQUEST_QUEUE');

DBMS_AQADM.START_QUEUE(

queue_name => 'BIZPROC_REPLY_TOPIC');

END;

/

3. 导入数据

imp silence/passwd fromuser=bpel touser=bpel file=exp_bpel.dmp log=exp_bpep.log

4. 从新编译失效对象

cd $ORACLE_HOME/rdbms/admin/

sqlplus "/ as sysdba"

@utlrp.sql