Unable to activate Oracle Database Partitioning - ORA-06650 and/or PLS-00905 error

Products

• DX Unified Infrastructure Management (Nimsoft / UIM)

Issue/Introduction

When attempting to use Database Partitioning, either during installation or by activating it in the data_engine GUI, an error may occur.

The error reported may be ORA-06650, and/or PLS-00905, and may be accompanied by an error similar to "object AUTO_10_238_4_69.SPN_DE_DATAMAINT is invalid."

Example message from data_engine.log or iaoutput.txt installation log:

OCIEnv: 0xfff100 OCIAuthInfo: 0x1021690 OCISvcCtx: 0x1020148

- Error: ExecuteSP [Admin] data_engine [Admin Classic] status: -1

????? OCI_ERROR - ORA-06550: line 1, column 7:

PLS-00905: object AUTO_10_238_4_69.SPN_DE_DATAMAINT is invalid

ORA-06550: line 1, column 7:

PL/SQL: Statement ignored

Environment

Release:
Component: UIMDEG 8.x or higher

Cause

• This error message indicates that one or more of the required database permissions has not been applied to the database user.
• The permissions have changed in UIM 8.0 compared to previous versions, so it's important to double-check and make sure all of the appropriate permissions have been correctly applied.
• Please refer to the UIM installation guide for details regarding the necessary permissions.

Resolution

Specifically, prior to installation, you will want to "grant execute on dbms_redefinition" to the user that is being used for the DB credentials.

 

 

To correct this situation after installation, use the following procedure to grant the appropriate permissions and then you will have to recompile the stored procedures.

 

Follow these steps:

1. Log in to the database server as SYSDBA and execute:
2. GRANT EXECUTE  on dbms_redefinition to <UIM USER>;
3. Use a tool such as Oracle SQL Developer to recompile the following stored procedures:

SPN_DE_DATAMAINT

SPN_DE_DATAMAINTDELETEOLDDATA

SPN_DE_UNPARTITIONTABLE

SPN_DE_PARTITIONTABLE

SPN_DE_UPDATEQOSDEFMETRIC

 

Additional Information

Oracle Grant Permimssions (broadcom.com)