A solution for Oracle account locking when opening an MXD file in ArcMap 10.2.2 was shared with me by a member of the ESRI staff, Dennis Jarrard, I've re-posted the solution on his behalf.
Screenshot of Oracle error message as seen in ArcMap.
When you add a layer from an SDE into the map, the layer object in the map actually keeps track of the connection information. It doesn’t reference the connection file, it just embeds the connection information right into the layer. This is why you can update the connection file in ArcCatalog and the layers are still broken in the maps. Armed with that understanding, you have several options to update the connection information for each layer in the map.
Unfortunately, there is no way to prevent an MXD from attempting to access data through a broken data source upon opening it (which locks the account). However, there are several options to fixing it, one of which is rebuilding the MXD, another option is scripting a Python solution to update a large number of MXDs.
The first option is to use the Set Data Sources utility in ArcCatalog.
- In ArcCatalog, create a new database connection file with the correct credentials. Even if you’ve updated the original, use a new connection file for best results.
- In ArcCatalog, right-click on the broken MXD > Set Data Sources. A utility should now popup. This utility allows you to map new data sources, and exports the changes to a new MXD as opposed to changing the source MXD.
3. Go ahead and determine an Output File location at the top.
4. At this point, I’m assuming you’re only using one unique SDE workspace per Map Document (which is probably not true). If you’re not, you’ll need to slightly modify this next part so that you only replace the respective layers associated with each broken database instead of modifying all of the layer references. Click “Select All” on the right. All layers will be highlighted. Now click “Replace All…”
5. From here, you’ll see a reference to the original connection file in the “Find what” parameter. In the second parameter (“Replace with”), reference the new connection file you created and click “Replace All”
6. Now click “OK”.
7. You should now have a new MXD. Try opening it and verify that the references have been fixed.
The previous workflow is great for individual circumstances, and if you don’t have a large collection of MXDs to update. If you do have a larger number of MXDs, and/or you’d like to automate this process for the future, you can leverage the arcpy.mapping libraries through a python script (be sure to look at the “SDE Connection properties are changed” bit). I’ve seen customers build scripts that are automatically executed to fix all MXDs immediately after the password is changed for more seamless operations. This might be something to consider.