Restricting database access per IP addresses
I think the only pure Oracle way to do this would be to run 2 separate listeners on different ports out of 2 different ORACLE_HOMEs, rather than just using the one.

A much more sane way of approaching this would be to use other security measures... Separate Usernames and passwords are quite common for this task - I guess you must be using LDAP or something that prevents this. Another option would be a login trigger on each database that checks the remote IP and disconnects the session if it's not come from the correct machine.

select SYS_CONTEXT('USERENV','IP_ADDRESS') from dual;


... will give you the remote IP address.

A trigger such as:

CREATE OR REPLACE TRIGGER DENY_LOGIN
AFTER LOGON ON DATABASE
DECLARE
  foo varchar2(128);
BEGIN

  IF ( sys_context('userenv','ip_address') &lt;&gt; '192.168.100.130' )
  THEN
    raise_application_error( -20001, 'Connection not authorised' );
  END IF;

EXCEPTION
WHEN OTHERS THEN
  RAISE;

END;


... will do the job for DB WORKSHOP.

(Untested, but should be OK)

Probably best to add a clause that allows DBAs to login from localhost too, or just exclude them completely :)