Hmmm...interesting....I learned something new today.
In the 9i world, the listener was not aware of whether the instance was in restricted mode. All users' connections were handed off to a server process, which then refused login if the instance was in restricted session and you didn't have restricted session privilege.
In 10g, the listener is "smarter". It's aware of whether the instance is in restricted session. If it is, it immediately rejects the connection attempt, even for privileged users, since it's not aware of which user is connecting.
This means that connections via a network connection will all be rejected, i.e., noone can login remotely if the instance is in restricted session. You must
be logged in to the database server, and then must initiate a non-networked connection, i.e., "/ as sysdba" or "username/password" without a connect string specified.
It's documented here:http://download.oracle.com/docs/cd/B19306_...t.htm#sthref543
Also, there's a workaround, add "(UR=A)" to the CONNECT_DATA portion of your connect string definition. This is documented in MetaLink Doc ID 444120.1.
Cool, I learned something new!
Thanks for pointing that out, Johnny!