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 [EMAIL PROTECTED];
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 <[EMAIL PROTECTED]> 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:[EMAIL PROTECTED]
> Sent: 21 March 2003 09:48
> To: Tim Onions; [EMAIL PROTECTED]
> 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 <[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
>
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).
