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: Babu Nagarajan
  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).

Reply via email to