Bug: Unable to connect to an ArcSDE for Oracle instance after creating multiple geodatabase instances
When creating a connection to an ArcSDE for Oracle database with greater than 100 multiple geodatabase instances present in the ArcSDE instance, there is potential that the SQL statement executed by ArcSDE to filter tables and layers registered in other ArcSDE instances can exceed the allowable SQL character limit for an individual statement.
When connecting to an ArcSDE instance for Oracle, ArcSDE must filter any tables or layers that the user has SELECT privileges for that are not registered in the instance in which the user is connecting. The filter is simply an additional 'where' clause for every multiple geodatabase present in the Oracle instance.
For example, if the user 'tim' has a layer named 'parcels' registered in the master instance and has granted SELECT privileges to the 'PUBLIC' role, and the user 'chris' is connecting to a geodatabase instance in the 'project' schema, the tim.parcels layer must be filtered from the list of registered tables that the user 'chris' can see when connecting to the 'project' instance. The tim.parcels layer is only accessible to users that connect to the master ArcSDE instance.
This issue has been addressed in the ArcSDE 9.2 Service Pack 5 release.