Working Through ORA-03150 on Database Links
You see this error when the remote side of a database link simply vanishes mid-operation. The local instance had a session open against the remote database, and then the communication channel — the TCP socket between the two — got torn down unexpectedly. Oracle raises ORA-03150 to tell you the file descriptor representing that connection has hit EOF, which in practical terms means the other end stopped responding. The technical explanation is straightforward: a database link creates a dedicated session on the remote instance, and that session talks back through a network connection managed by Oracle Net Services. When something breaks that connection without a clean shutdown handshake, the local process reading from it gets ORA-03150. It is not a permissions issue. It is not a syntax problem in your query. The pipe is just empty now. I ran into this last year on a migration project where we had a database link running between a production Oracle 19c instance and a staging environment across a VPN tunnel. We were pushing a large materialized view refresh through that link, and every time the job ran past about forty minutes, it would fail with ORA-03150. The VPN concentrator had an idle timeout set at thirty-five minutes, so the tunnel would silently drop the connection while the query was still running. No error before it. Just dead air, then the exception.
How to Figure Out Which Side Caused It
The first thing to check is whether the remote database is still up. Connect directly with SQL*Plus using a straight TNS alias — skip the database link entirely and see if you can reach the remote side. If you can, the instance is fine and the problem lived in the specific session that the database link was using. If you cannot, the remote side went down or the network path between them is broken. Next thing I always look at is the alert log on both sides. On the local instance, grep for "ORA-03150" entries. On the remote instance, look for corresponding errors around the same timestamp — things like ORA-03114 (not connected), ORA-03135 (connection lost), or ORA-27140 (attachment failure). The timing of those messages tells you whether the local side dropped the connection or the remote side did. In my VPN case, there were no errors on the remote side at all. The remote instance never knew the session died. That pointed squarely at the network path, not either database.
Common Causes and What to Do About Them
Network timeout issues are the most frequent culprit, especially in environments with firewalls, load balancers, or VPNs sitting between the two databases. These middleboxes often have idle connection timeouts that are shorter than your database link usage patterns. The fix usually involves either tuning the TCP layer or changing how the database link behaves. On the local database, setting SQLNET.EXPIRE_TIME in sqlnet.ora forces periodic probes on established connections. A value of 10 minutes means the server sends a ten-byte packet every ten minutes to verify the connection is still alive. If a firewall or VPN drops idle connections, this keeps them from becoming stale in the middle of a long-running operation. This is configured at the OS level, so it requires a restart of the listener or the database to take effect depending on how your environment handles sqlnet.ora changes. Most of the time a listener reload picks it up, but I have seen cases where it did not. TNS parameter adjustments matter too. The KEEP ALIVE setting on the TNS entry for your database link can help, but it depends on the operating system supporting it. On Linux, TCP_KEEPIDLE, TCP_KEEPINTVL, and TCP_KEEPCNT kernel parameters interact with Oracle's own keepalive behavior, and mismatched values here produce exactly the kind of silent drops that lead to ORA-03150. I spent two days once tracking down a problem where the DBAs had set TCP_KEEPIDLE to 7200 seconds at the OS level while Oracle's default probe interval was sixty seconds. The kernel parameter was overriding Oracle's settings entirely, and long-running cross-database queries were getting dropped by the network stack before Oracle even tried to detect the problem.
Get the Full Details
Long-Running Queries and Chunking Strategy
If your database link is processing large datasets, breaking the work into smaller chunks is usually more reliable than trying to tune timeout values alone. A query that runs for five minutes through a database link is far less likely to hit a transient network glitch than one that runs for two hours. I typically restructure bulk operations to process somewhere between five thousand and fifty thousand rows per pass, depending on the row size and network conditions. The overhead of multiple round trips is real, but it is cheaper than debugging intermittent connection drops. Materialized view logs and incremental refresh options exist for this reason. When you can use REFRESH FAST WITH ROWID instead of a complete refresh, the amount of data flowing through the database link shrinks dramatically, and the session duration drops with it. This is not always available depending on your table structure and whether you have a materialized view log in place, but when it is an option it solves a lot of these problems without touching network configuration.
Remote Instance and Listener Health
Sometimes the remote instance is fine but the listener that accepted the connection is no longer handling traffic properly. Check the listener status on the remote side with lsnrctl status and look for any recent listener restarts. A listener restart does not kill existing connections, but it can cause issues with certain Oracle versions and patch levels where new connections after a restart behave differently than expected. I encountered this on a 12cR2 system where the listener was automated to restart during a maintenance window, and database links established before the restart started failing with ORA-03150 on subsequent operations even though the sessions were still technically active. Also verify that the remote instance has not hit any resource limits. If the remote PGA or UGA memory is exhausted, Oracle may abort the session in a way that produces EOF on the local side rather than a clean error. Check the remote instance's resource limits and memory utilization around the time of the failures.
What This Approach Cannot Fix
Database links are fundamentally a synchronous, session-based protocol. They do not handle network partitioning well, and they do not recover from mid-query failures gracefully. If the connection drops in the middle of a multi-table join that has already read and processed half the data, you lose all of it. There is no checkpoint mechanism within the database link protocol itself. This is a hard limitation of the technology, and it is why many organizations move away from database links for large data movement operations in favor of something like Data Pump, GoldenGate, or plain file-based ETL. Oracle's official recommendation for high-availability scenarios involving database links is to use Active Data Guard with queryable standby databases rather than relying on database links across unstable network paths. It is more infrastructure, but it eliminates the class of problems that ORA-03150 represents. For smaller environments where that is not practical, the chunking strategy and keepalive tuning described above will handle the majority of cases, but you should expect occasional failures on any connection that traverses an unmanaged network segment for more than a few minutes at a time.
