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----- Sent: 21 March 2003 01:34 To: Multiple recipients of list ORACLE-L 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tim Onions 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).