GGSCI (server01 as gguser@ebsprod) 2> dblogin userid gguser, password AACAAAAAAAAAAAGAKBGHFAIBJJMFRJXD , encryptkey default

2023-04-09 15:05:43  WARNING OGG-25108  Failed to set the Oracle session tag: ORA-04060: insufficient privileges to execute DBMS_STREAMS.SET_TAG
ORA-06512: at "SYS.DBMS_STREAMS", line 16
ORA-06512: at line 1.
Successfully logged into database.

授予用户dbms_streams_adm权限：
SQL> grant execute on dbms_streams_adm to gguser;

Grant succeeded.

GGSCI (server01 as gguser@ebsprod) 3> dblogin userid gguser, password AACAAAAAAAAAAAGAKBGHFAIBJJMFRJXD , encryptkey default
Successfully logged into database.

SYMPTOMS:

After An Upgrade To 11.2.0.1, DBMS_STREAMS.SET_TAGReturns Errors

ORA-4060 "insufficient privileges"
ORA-6512 at "SYS.DBMS_STREAMS", line 16

For example DBMS_STREAMS.SET_TAG executed via a trigger returns errors:

ERROR at line 1:
ORA-04060: insufficient privileges to execute DBMS_STREAMS.SET_TAG
ORA-06512: at "SYS.DBMS_STREAMS", line 16
ORA-06512: at "SMS_QA_MBLOX.APP_PHONE_REGISTRY_TRIGGER1", line 2
ORA-04088: error during execution of trigger 'SMS_QA_MBLOX.APP_PHONE_REGISTRY_TRIGGER1'
CHANGES
The database was upgraded from Oracle10g to Oracle11g.

CAUSE:

DBMS_STREAMS.SET_TAG is obsolete.

As per Oracle Streams Concepts and Administration 11g, DBMS_STREAMS_ADM.SET_TAG should be used, and execute privileges should be granted for the user invoking this API.

SOLUTION:

Instead of DBMS_STREAMS, use DBMS_STREAMS_ADM.

Example:

SQL> connect / as sysdba

Connected.

SQL> grant execute on dbms_streams_adm to scott;

Grant succeeded.

SQL> connect scott
Enter password:

Connected.

SQL> exec dbms_streams_adm.set_tag ('FF');

PL/SQL procedure successfully completed.