Babu, Closing the cursor doesn't close the connection AFAIK.
You can can use dbms_session.close_database_link or ALTER SESSION CLOSE DATABASE LINK <name> to do that. Jared On Friday 21 March 2003 04:28, Babu Nagarajan wrote: > Hemant > > I faced the same problem when setting up a centralized monitoring utility > (very much similar to what you are trying to accomplish here, I think) > > For the Ora-2020 - either increase the distributed_transactions parameter > or use dbms_sql instead of execute immediate where you can explicitly close > the connection by dbms_sql.close(cursor) > > Babu > ----- Original Message ----- > To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> > Sent: Friday, March 21, 2003 4:49 AM > > > Thanks Tim ! > > That works ! > > > > now I just have to resolve the "ORA-02020: too many database links in > > use" > > error ! > > > Thanks again. > > Hemant > > > > --- Tim Onions <[EMAIL PROTECTED]> wrote: > > > Hemant > > > > > > In case nobody has yet replied I believe your error is that you > > > cannot use a > > > bind variable in an execute immediate for the db link name as you > > > are trying > > > to do with this statement: > > > > > > exec_string := 'select sum(bytes)/1048576 from > > > dba_data_files@:b1'; > > > > > > Changing it to this might work (I've not checked it myself so no > > > guarantees) > > > > > > exec_string := 'select sum(bytes)/1048576 from > > > dba_data_files@'||remote_db; > > > execute immediate exec_string into db_size; > > > > > > T¬ > > > > > > > > > -----Original Message----- > > > From: Hemant K Chitale [mailto:[EMAIL PROTECTED] > > > Sent: 21 March 2003 01:34 > > > To: Multiple recipients of list ORACLE-L > > > Subject: Passing DB-Link name in a Loop > > > > > > > > > > > > Guys, > > > help me here. > > > This SQL [below] returns the error : > > > connecting to AM3C01 > > > declare > > > * > > > ERROR at line 1: > > > ORA-01729: database link name expected > > > ORA-06512: at line 16 > > > > > > > > > [AM3C01] is the first db_link fetched. > > > > > > tti 'Database Sizes (excluding TEMPFILEs) ' center > > > > > > spool DB_Sizes > > > > > > set serveroutput on size 50000; > > > > > > declare > > > cursor c1 is select db_link from user_db_links; > > > > > > remote_DB varchar2(128); > > > db_size number; > > > exec_string varchar2(255); > > > > > > begin > > > open c1; > > > loop > > > fetch c1 into remote_DB; > > > exit when c1%NOTFOUND; > > > dbms_output.put_line('connecting to '||remote_DB); > > > -- select sum(bytes)/1048576 into db_size from > > > [EMAIL PROTECTED]; > > > exec_string := 'select sum(bytes)/1048576 from > > > dba_data_files@:b1'; > > > execute immediate exec_string into db_size using remote_DB; > > > dbms_output.put_line('DB : '||remote_DB||': '||db_size); > > > end loop; > > > close c1; > > > commit; > > > end; > > > / > > > > > > spool off > > > > > > Hemant K Chitale > > > http://hkchital.tripod.com > > > -- > > > Please see the official ORACLE-L FAQ: http://www.orafaq.net > > > -- > > > Author: Hemant K Chitale > > > INET: [EMAIL PROTECTED] > > > > > > Fat City Network Services -- 858-538-5051 http://www.fatcity.com > > > San Diego, California -- Mailing list and web hosting > > > services > > > --------------------------------------------------------------------- > > > To REMOVE yourself from this mailing list, send an E-Mail message > > > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > > > the message BODY, include a line containing: UNSUB ORACLE-L > > > (or the name of mailing list you want to be removed from). You may > > > also send the HELP command for other information (like > > > subscribing). > > > > Hemant K Chitale > > http://hkchital.tripod.com > > -- > > Please see the official ORACLE-L FAQ: http://www.orafaq.net > > -- > > Author: Hemant K Chitale > > INET: [EMAIL PROTECTED] > > > > Fat City Network Services -- 858-538-5051 http://www.fatcity.com > > San Diego, California -- Mailing list and web hosting services > > --------------------------------------------------------------------- > > To REMOVE yourself from this mailing list, send an E-Mail message > > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > > the message BODY, include a line containing: UNSUB ORACLE-L > > (or the name of mailing list you want to be removed from). You may > > also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jared Still INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
