Forum Stats

  • 3,827,149 Users
  • 2,260,746 Discussions


Is it supported to recompile AQ$ JMS TYPES with NLS_LENGTH_SEMANTICS=CHAR?

We are preparing for unicode migration. Out intention is to export/import whole database (20TB+) and use OGG to minimize downtime.

We have troubles with SYS.AQ$_JMS_MESSAGE and similiar types. Application is using JMS AQ and we need to replicate AQ as well (it is possible from OGG 12.2 onwards by procedural replication). Unfortunately we hit some messages which cannot be replicated, as they are close to VARCAHR2 4000 byte limit on the source database. SYS.AQ$_JMS_MESSAGE stores data with length < 4000 in VARCHAR2. However it does not fit in VARCHAR2 on target database and logical replication fails.

Moreover application also uses SYS.AQ$_JMS_MESSAGE as UDT of one or two application tables.

When we finish unicode migration, we won't be bothered by this issue anymore, as SYS.AQ$_JMS_MESSAGE will store new data with length over 4000 bytes in CLOB naturally.

We could recompile JMS AQ$ types on target database just after creation (startup upgrade, ALTER TYPE SYS.AQ$_JMS_MESSAGE NLS_LENGTH_SEMANTICS=CHAR; and utlrp, restart to normal mode. I just wonder if it is supported (and I would bet it is not). I have not found any mentioning in documentation.

I will also raise a SR, but it will take time to explain there and also the answer won't come very fast.