Oracle create db link using a proxy schema

Question

So I want to create a database link in oracle, my username is jefferson and I want to connect trough opms so I was told to do this.

create  database link  tmpp connect to jefferson[opms] identified by nothing using $something ;

For some reason when I try to use [] syntax it just tells me indentified is missing. Why is this not working, I was told to do it this way but I can't find any help in the official documentation for [] usage or the correct syntax.

Answer 1

You can create a fixed-user database link like this, but you need to enclose the entire proxy user identifier in double-quotes; and because it's now a quoted identifier the case has to match the DBA_USERS username, which is uppercase by default:

create database link tmpp connect to "JEFFERSON[OPMS]" identified by nothing using ... ;

As noted in MOS document 1477939.1 you can't create a connected-user database link (which you aren't trying to do); and the 30-character limit on identifiers applies, so the total length of both usernames plus the square brackets has to be 30 characters or less (which is also fine in your example).

However, as discussed in this related question, this functionality is currently broken in 11.2.0.4 and above because of bug 19191702.