转：如何迁移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