I need to bring some data (blobs) from another database. I created global links to enable bidirectional communication. The links work. When I try to bring some data from tables that have BLOB columns I get intermittently the ORA-12516:
TNS:listener could not find available handler with matching protocol stack
Here's what I run to bring BLOBs to my schema:
CREATE TABLE TEMP_MY_TABLE AS
SELECT * FROM SOME_REMOTE_TABLE_WITH_BLOB_COLUMN@dblink_source;
Oracle 11 r2. Blobs have different sizes.
Number of PROCESSES is set to 1000 and only half is utilized during the run. Sessions = 1528.
Both source and target are identical in hardware and software including patches.
The links work fine for hundreds of other queries. I checked and they are resolved properly.
Best Answer
Most probably you are reaching the maximum connections allowed. Make sure that old sessions are being closed properly and try increasing the processes and sessions parameters.