Oracle Streams Setup Error

Question

I am running Oracle 11g on a Linux box. I am trying to run the following command to set my streams user grants and other authorizations:

BEGIN
  DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE(
    grantee          => 'nwisxfer',    
    grant_privileges => false,
    file_name        => 'grant_strms_privs.sql',
    directory_name   => '/usr/oracle');
END;
  8  /
BEGIN
*
ERROR at line 1:
ORA-23481: unexpected name string "/usr/oracle"
ORA-06512: at "SYS.DBMS_STREAMS_AUTH", line 1621
ORA-06512: at line 2

A Google search suggested that I run these scripts:

/usr/oracle/app/product/11.2.0/dbhome_1/rdbms/admin/catrep.sql
/usr/oracle/app/product/11.2.0/dbhome_1/rdbms/admin/utlrpt.sql

This did not eliminate the error.

Any advice on how to proceed?

Answer 1

You need to create an Oracle DIRECTORY first.

CREATE DIRECTORY MYSTREAMSDIR as '/usr/oracle';

GRANT READ, WRITE ON DIRECTORY MYSTREAMSDIR TO YOURUSER;

BEGIN
  DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE(
    grantee          => 'nwisxfer',    
    grant_privileges => false,
    file_name        => 'grant_strms_privs.sql',
    directory_name   => 'MYSTREAMSDIR');
END;

The grant isn't needed if you create the directory logged in as the streams admin user (usually strmadmin).