In the last couple of weeks we are facing lot of issues in our development and testing environments for connections to Oracle geodatabase through ArcCatalog. We have different databases for Asset and Basemap. There is not a huge amount of data in the asset database, still it takes too much time to connect to it, like maybe 2 minutes or so. It connects through SQLPlus in 1 or 2 seconds.
While connecting to Basemap database, it causes ArcCatalog to hang and go into a not responding state. This was not the case couple of months ago when the databases and schemas were newly created and data was loaded. At worst we were able to connect in 10 seconds through ArcCatalog.
Since then there have been many changes related to data model in the geodatabase and also many times the database was refreshed with new set of data.
Any suggestions will help. We're using ArcGIS Desktop 10.4.1 on Windows 2012 VM servers. We have 12 GB RAM and quad core processor.
Another thing which I want to highlight is that we're using versioning for spatial data editing through Workflow Manager. In many cases, during the development, the versions which were created haven't been deleted. Will deleting the unnecessary versions help?
One more thing is that we have installed the full Oracle Client as the DBMS client to allow ArcCatalog to connect to Oracle geodatabase. Is the full version required or is Instant Client sufficient?
Yes, deleting unnecessary versions, Reconciling \Posting the rest to the Default version and then Performing a Compress may help.
check the below links as well:
FAQ: How can ArcSDE performance be improved?
How To: Diagnose ArcSDE connection and performance issues using SDEINTERCEPT
Also, Instant Client is sufficient to make a connection to an Oracle database from ArcCatalog and work on it. However, deciding upon which one to use (Full Client or Instant Client) is upto you.
I don't use Oracle, but just in case the upgrade to Desktop 10.4.1 was recent, make sure the Oracle database is compatible. I think for MSSQL anyway the minimum requirements changed (I have not updated to 10.4.1 yet)
Oracle database requirements for ArcGIS 10.4.x—Help | ArcGIS for Desktop