The compare/repair operation works fine sometimes, but intermittently fails with the error:
StmtExecute failed with ORA-01031: insufficient privileges
This issue occurs when below privileges are missing at the database level to <SharePlex> user
GRANT SELECT ON SYS.ENC$ TO SPLEX;
GRANT SELECT ON SYS.OBJ$ TO SPLEX;
GRANT SELECT ON SYS.TAB$ TO SPLEX;
GRANT SELECT ON SYS.USER$ TO SPLEX;
In an RAC Exadata environment, privileges granted on one node may not consistently reflect across other nodes due to privilege inconsistency.
As a result, the compare operation fails when internal SQL statements execute from nodes where the privileges are not active.
You can verify this using the following SQL:
SQL> SELECT o.obj#, t.property, t.flags, o.flags FROM sys.obj$ o, sys.user$ u, sys.tab$ t WHERE u.user# = o.owner# AND o.obj# = t.obj# AND o.name='<table_name>' AND u.name ='<user_name>';
Please run the following GRANT statements on each node of the RAC setup to ensure consistency:
GRANT SELECT ON SYS.ENC$ TO SPLEX;
GRANT SELECT ON SYS.OBJ$ TO SPLEX;
GRANT SELECT ON SYS.TAB$ TO SPLEX;
GRANT SELECT ON SYS.USER$ TO SPLEX;
