0

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.

  • Add the definition of the dblink. If it uses a TNS alias, post what that TNS alias is resolved to. Add the output of lsnrctl services from the source system. Add the output of select * from gv$resource_limit where resource_name in ('sessions', 'processes'); from the source database. Blob, dblink are most likely irrelevant here, sounds just like a typical connecion issue with inadequate free sessions/processes. – Balazs Papp Jun 12 at 19:52
1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.