RE: Passing DB-Link name in a Loop

I've fixed the open links issue as well. Thanks

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 dba_data_files_at_remote_DB;

    exec_string := 'select round(sum(bytes)/1048576,2) from dba_data_files@'||r emote_DB;
-- execute immediate exec_string into db_size using remote_DB;

    execute immediate exec_string into db_size ;     commit;
    exec_string := 'alter session close database link '||remote_DB;     execute immediate exec_string ;
    dbms_output.put_line(rpad(remote_DB,20)||': '||db_size);    end loop;
   close c1;
 commit;
end;
/

spool off
Hemant

• Tim Onions <tim.onions_at_speechmachines.com> wrote:

> Hemant
>
> There was a post not so long ago about how to close a link once
> you've used
> it, I suggest you dig that out and close the db link after you've
> done the
> select.
>
> T¬
>
> -----Original Message-----
> From: Hemant K Chitale [mailto:hkchital_at_singnet.com.sg]
> Sent: 21 March 2003 09:48
> To: Tim Onions; ORACLE-L_at_fatcity.com
> Subject: RE: Passing DB-Link name in a Loop
>
>
>
> 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 <tim.onions_at_speechmachines.com> 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:hkchital_at_singnet.com.sg]
> > 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
> > dba_data_files_at_remote_DB;
> > 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: hkchital_at_singnet.com.sg
> >
> > 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: ListGuru_at_fatcity.com (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
Received on Fri Mar 21 2003 - 04:08:05 CST