在升级完数据库之后不久就接到客户投诉，说是用户完成工作之后无法收到邮件，经过UNIXteam排除邮件服务器之后，问题被转移到DBAteam了，研究了半天终于解决了。
参考文章http://www.oracle-base.com/articles/11g/fine-grained-access-to-network-services-11gr1.php。
Oracle allows access to external network services using several PL/SQL APIs (UTL_TCP, UTL_SMTP, UTL_MAIL, UTL_HTTP and UTL_INADDR), all of which are implemented using the TCP protocol. In previous versions of the database, access to external services was effectively an on/off switch based on whether a user was granted execute permissions on a specific package or not. Oracle 11g introduces fine grained access to network services using access control lists (ACL) in the XML DB repository, allowing control over which users access which network resources, regardless of package grants.
我们的解决方案参考了前面的文章，具体步骤如下：

Check xdb status

set echo on;
connect / as sysdba
set pagesize 1000
col comp_name format a36
col version format a12
col status format a8
col owner format a12
col object_name format a35
col name format a25

-- Check status of XDB

select comp_name, version, status
from dba_registry
where comp_id = 'XDB';
(select * from v$option)
-- Check for invalid objects

select owner, object_name, object_type, status
from dba_objects
where status = 'INVALID'
and owner in ('SYS', 'XDB');

spool off;

step1 install xdb

Master Note for Oracle XML Database (XDB) Install / Deinstall (文档 ID 1292089.1)

select tablespace_name from dba_tablespaces;

SQL>

select tablespace_name,SEGMENT_SPACE_MANAGEMENT  from dba_tablespaces;

 

TABLESPACE_NAME                SEGMEN

------------------------------ ------

SYSTEM                         MANUAL

SYSAUX                         AUTO

UNDOTBS1                       MANUAL

TEMP                           MANUAL

USERS                          AUTO

MAXIMO                         AUTO

RBS                            AUTO

RBSBIG                         AUTO

TOOLS                          AUTO

@?/rdbms/admin/catqm.sql  andrew123  USERS  TEMP YES

@?/rdbms/admin/utlrp.sql

step2: install the dbms_network_acl_admin package

@ ?/rdbms/admin/catnacl.sql

@ ?/rdbms/admin/dbmsnacl.sql

@ ?/rdbms/admin/prvtnacl.plb

Step3 create acl

Log db as sysdba

BEGIN

  DBMS_NETWORK_ACL_ADMIN.create_acl (

    acl=> 'NOR754S_SENDMAIL.xml',

    description=> 'EMAIL_SEND',

    principal=> 'SYS',

    is_grant=> TRUE,

    privilege=> 'connect',

    start_date=> SYSTIMESTAMP,

    end_date=> NULL);

  COMMIT;

END;

/

Step4 :add other user to acl

Run the following SQL AS sysdba:

sqlplus  -s  / as sysdba < set termout off

set heading off
set pagesize 0
set verify off
set echo off

spool /home/ora11g/NOR754S.sql

select

 'BEGIN' ||chr(10)||

 ' DBMS_NETWORK_ACL_ADMIN.add_privilege ( '||chr(10)||

 '    acl=> '||'''NOR754S_SENDMAIL.xml'''||', '||chr(10)||

 '   principal=>'''||upper(username)||''','||chr(10)||

 '   is_grant=> TRUE, '||chr(10)||

 '  privilege=> '||'''connect'','||chr(10)||

 '  position=> NULL, '||chr(10)||

 '  start_date=> SYSTIMESTAMP,'||chr(10)||

 '   end_date=> NULL);'||chr(10)||

 '    END;'||chr(10)||

 '/'||chr(10)

from dba_users where account_status='OPEN'

and username 

not in('SYS');

spool off

!

---then run

sqlplus  /as sysdba

@/home/ora11g/NOR754S.sql

---to add other user

step5: assign act to network

Only one ACL can be assigned to a specific host and port-range combination. Assigning a new ACL to a specific host and port-range results in the deletion of the previous assignment. You must take care when making a new assignment that you are not opening ports that were closed by a previous ACL assignment, or you could be opening yourself to attack. When wildcard usage causes overlapping assignments, the most specific assignment will take precedence, so an ACL assigned to 192.168.2.3:80 takes precedence over once assigned to 192.168.2.* etc. Run the following sql:

BEGIN

  DBMS_NETWORK_ACL_ADMIN.assign_acl (

    acl=> 'NOR754S_SENDMAIL.xml',

    host=> '9.xxx.xxx.19', ----邮件服务器IP

    lower_port=>24433,   -----指定端口号

    upper_port=> NULL);

         COMMIT;

END;

/