Q
Manage Learn to apply best practices and optimize your operations.

Recreating advanced queuing objects during database upgrade

Expert Brian Peasland explains how solving the ORA-28031 error will enable an Oracle user to recreate queue tables.

I am currently upgrading a 9.2.0.7 database to 10.2.0.1 and upon migration I see errors related to advanced queuing objects as seen:

ORA-28031: maximum of 148 enabled roles exceeded
ORA-06512: at "SYS.DBMS_RULEADM_INTERNAL", line 28
ORA-06512: at "SYS.DBMS_RULE_ADM", line 138
ORA-06512: at "SYS.DBMS_PRVTAQIS",line 2938
ORE-00904: "QT"."USER_PROP":invalid identifier

How can I recreate the queue tables on the 10.2.0.1 database?

First, you need to resolve the ORA-28031 error. Increase the MAX_ENABLED_ROLES initialization parameter and bounce the instance. After that, sign on as SYS and rerun the following in SQL*Plus:

@?/rdbms/admin/catalog.sql
@?/rdbms/admin/catproc.sql

Running those two scripts should recreate the AQ tables.

Dig Deeper on Oracle database administration

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close