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).
