Oracle Blog

Saturday, September 5, 2015

How to move AQ Tables

12:08 PM Posted by Yunus SIMSEK No comments
If you need to move Advance Queue tables between table-spaces then you need to follow below 5 steps. 

1- Revoked Enqueu/Deqeueu Privelege from Queue Table. 
2- Stop Queue Table. 
3- Move Queue Table 
4- Grant Enqueue/Dequeu privelege to Queue Table. 
5- Start Queue.


connect / as sysdba 
EXECUTE DBMS_AQADM.REVOKE_QUEUE_PRIVILEGE( privilege => 'ENQUEUE', queue_name => 'APPLSYS.WF_DEFERRED', grantee => 'APPS'); 
EXECUTE DBMS_AQADM.REVOKE_QUEUE_PRIVILEGE( privilege => 'DEQUEUE', queue_name => 'APPLSYS.WF_DEFERRED', grantee => 'APPS'); 

connect applsys/appstra 
set serveroutput on; 
exec dbms_aqadm.stop_queue('APPLSYS.WF_DEFERRED'); 

connect aqmove/aqmove 
set serveroutput on; 
EXEC MOVE_QT_PKG.MOVE_QUEUE_TABLE('APPLSYS','WF_DEFERRED','OLD_TABLESPACE','NEW_TABLESPACE'); 

connect / as sysdba 
EXECUTE DBMS_AQADM.GRANT_QUEUE_PRIVILEGE( privilege => 'ENQUEUE', queue_name => 'APPLSYS.WF_DEFERRED', grantee => 'APPS'); 
EXECUTE DBMS_AQADM.GRANT_QUEUE_PRIVILEGE( privilege => 'DEQUEUE', queue_name => 'APPLSYS.WF_DEFERRED', grantee => 'APPS') 
connect ***/***
exec dbms_aqadm.start_queue('WF_CONTROL'); 

SQL> set linesize 220 
SQL> select a.owner, a.queue_table, b.tablespace_name from dba_queue_tables a, dba_segments b where a.queue_table = b.segment_name and a.owner = b.owner and b.tablespace_name='APPS_TS_QUEUES' order by tablespace_name; 

OWNER QUEUE_TABLE TABLESPACE_NAME 
------------------------------ ------------------------------ ------------------------------ 
APPLSYS WF_CONTROL NEW_TABLESPACE
APPLSYS WF_DEFERRED APPS_TS_QUEUES 
Using above steps will move your Advance Queue tables to different tablespace without an issue. I had an issue and spent weeks to find to solution, solution was simple, stop queue and revoke privilege then move to different tablespace.

if you need to know how to install DBMS_REDEFINITION please follow below instruction. 
Create the aqmove user detailed below or grant the relevant privileges to an existing user that you want to be able to perform the move 
--- Download these 2 packets from MOS(1410195.1) and place to your home directory ----
@move_qt_pkgs 
@move_qt_pkgb.plb

drop user aqmove cascade;
create user aqmove identified by aqmove; 
alter user aqmove default tablespace system;
--Privileges required to use DBMS_REDEFINITION in conjunction with the move package.
grant connect, resource to aqmove; 
grant execute on sys.DBMS_LOB to aqmove;
grant unlimited tablespace to aqmove; 
grant create any table to aqmove; 
grant alter any table to aqmove; 
grant drop any table to aqmove; 
grant lock any table to aqmove; 
grant select any table to aqmove; 
grant create any index to aqmove; 
grant alter any index to aqmove; 
grant create any trigger to aqmove;
grant execute_catalog_role to aqmove; 
grant select_catalog_role to aqmove; 
grant execute on dbms_redefinition to aqmove; 
grant alter session to aqmove;
connect aqmove/aqmove 

--- Execute these packages from your home directory- 

@move_qt_pkgs 
@move_qt_pkgb.plb
Reference :How to perform an Online Move of Advanced Queening Tables using DBMS_REDEFINITION (Doc ID 1410195.1)

0 comments:

Post a Comment