You have global names set to TRUE which means the database link has to be named after the global name of the other database.
SQL> alter session close database link remotedb; -- remotedb --> a dblink name Session altered. So, you can do one of the following: o alter session|system set global_names=false o create the database link with the name ORB instead of RP o rename the global name of ORB to RP This is done at sign-on time using Oracle's dbms_session package. The set_context procedure can … o set open_links higher, allow for more concurrently open links o issue alter session close database link in the procedures that use them, after a commit, to totally close them. At sign-on, a database logon trigger executes, setting the application context for the user by calling dbms_session.set_context. [crayon-5ef2ff9f7b7ea432450518/] Here open_links is set to 4, […] Asaf Tal Post author January 23, 2014 at 4:53 pm. A ... 3 thoughts on “ ORA-02020 – Too many database links in use (also a workaround for ORA-28267 ) ” steve January 23, 2014 at 4:17 pm. Anyone on the network can use it. Amongst other things, the DBMS_SESSION package contains procedures to help manage sessions being used as part of a connection pool. If you access a database link in a session, then the link remains open until you close the session. ALTER SESSION CLOSE DATABASE LINK my_db_link; EXECUTE DBMS_SESSION.CLOSE_DATABASE_LINK(‘my_db_link‘); To successfully close database link: database link have to be opened first, or you will get ORA-02081: database link is not open Using Oracle dbms_session For the VPD to properly use the security policy to add the where clause to the end user's SQL, Oracle must know details about the authority of the user. The ALTER SYSTEM DISCONNECT SESSION syntax is an alternative method for killing Oracle sessions. The syntax to kill a session in oracle database is : ALTER SYSTEM KILL SESSION ‘SID,SERIAL#’ IMMEDIATE; EXAMPLE: First get the sid and serial# of the session; Here the session is executing the query SELECT * FROM DBACLASS; Use the below query to get the sid and serial# of this sql query. Public database link - all users in the database can use it. DBMS_SESSION : Managing Sessions From a Connection Pool in Oracle Databases. [crayon-5ef2ff9f7b7e0432055197/] CAUSE & SOLUTION: open_links parameter control, the number of database links each session can use without closing it.
Global database link - defined in an OID or Oracle Names Server. Oracle | Toad expert blog for developers, admins and data analysts. You still need to do commit or rollback before closing a db_link session to do that. Test Link: BEGIN ALTER SESSION CLOSE DATABASE LINK remove_db; SELECT table_name INTO i FROM [email protected]_db WHERE rownum = 1; EXCEPTION WHEN OTHERS THEN RAISE_APPLICATION_ERROR(-20999, 'No Connection'); END; Remote PL/SQL: SELECT over a db_link From Jonathan Lewis's FAQ: Why does it seem that a SELECT over a db_link requires a … Cause: a transaction is active or a cursor is open on the database link given in the alter session close database link link command. @steve, this … [crayon-5ef0f9b0104e3040652617/] Create a DBlink: CREATE DATABASE LINK remotedb CONNECT TO scott IDENTIFIED BY tiger USING 'tns_conn_str'; Drop a DBlink: DROP DATABASE LINK remotedb; Close Database Link . Create and drop . SQL> alter session close database link test; ERROR: ORA-02081: database link is not open 注意，在使用此方式的命令之前需要用commit命令首先提交，否则会出现ora-02080错误。 --eg:實現關閉dblink. execute immediate 'alter session close database link DB LINK NAME' However, it is not always feasible to change existing code. ALTER SESSION ADVISE