Oracle 11g: Configuring Stream

problem encountered during stream configuration:
1.
Clean up the queues on both instances and re-run the scripts.

ERROR at line 1:
ORA-39001: invalid argument value
ORA-06512: at line 140

Is most likely due to the fact that the file streams_setup_2013_5_17_16_8_6_697.dmp already exists in the OS directory.


2.

Applies to:

Enterprise Manager for Oracle Database - Version: 11.2.0.3 and later   [Release: 11.2 and later ]
Information in this document applies to any platform.

Symptoms

Enterprise Manager does not build correct script to configure streams in 11.2.0.3.0 and fails with ORA-44001

for eg-

SQL> BEGIN
dbms_streams_adm.set_up_queue(
queue_table => '"streamadmin"."QUEUE_TAB"',
storage_clause => NULL,
queue_name => '"streamadmin"."QUEUE_T"',
queue_user => '"STREAMADMIN"');
END;
/ 2 3 4 5 6 7 8
BEGIN
*
ERROR at line 1:
ORA-44001: invalid schema
ORA-06512: at "SYS.DBMS_STREAMS_ADM", line 743
ORA-06512: at line 2

Cause

Enterprise Manager does not build correct script to configure streams and it internally takes stream admin user name in small letters instead of CAPS.

Solution

- Correct the script to use the stream admin user name in CAPS in single quotes.

BEGIN
DBMS_STREAMS_ADM.SET_UP_QUEUE(
queue_name => 'STREAMS_QUEUE',
queue_table =>'STRMADMIN.STREAMS_QUEUE_TABLE',
queue_user => 'STRMADMIN');
END;
/