How to perform an Online Move of Advanced Queueing Tables using DBMS_REDEFINITION
(Doc ID 1410195.1)
Last updated on MAY 02, 2023
Applies to:
Oracle Database Cloud Service - Version N/A and laterOracle Database - Enterprise Edition - Version 10.2.0.5 to 12.1.0.2 [Release 10.2 to 12.1]
Oracle Database Cloud Schema Service - Version N/A and later
Gen 1 Exadata Cloud at Customer (Oracle Exadata Database Cloud Machine) - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Information in this document applies to any platform.
Purpose
The purpose of this article is to discuss and provide a procedure which enables you to perform an online move of Advanced Queuing (AQ) tables using DBMS_REDEFINITION.
Scope
This article is intended for anyone who needs to move AQ objects from one tablespace to another or within the same tablespace. The procedure can be executed in 10.2 or later databases.
In 10.2 onwards DBMS_REDEFINITION can be used to move queue tables whilst they are being actively used which avoids any application downtime. The procedure utilises this functionality and overcomes the limitations of the move procedure supplied in <Note 304522.1> which is an offline procedure. It will retain any user created indexes created on the queue table, any privileges granted and any statistics created on the queue table objects.
In addition to the ability to use this procedure to move a queue table in 10.2 onwards you also have the ability to dynamically shrink queue tables to reduce the high watermark of these objects if they are located in ASSM tablespaces as per <Note 421474.1>.
Details
To view full details, sign in with your My Oracle Support account. |
|
Don't have a My Oracle Support account? Click to get started! |
In this Document
| Purpose |
| Scope |
| Details |
| References |