FAQ
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 -----
From: "Hemant K Chitale"
To: "Multiple recipients of list ORACLE-L"
Sent: Friday, March 21, 2003 4:49 AM
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 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;




-----Original Message-----
From: Hemant K Chitale
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
subscribing).


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

Search Discussions

Discussion Posts

Previous

Follow ups

Related Discussions

Discussion Navigation
viewthread | post
posts ‹ prev | 5 of 6 | next ›
Discussion Overview
grouporacle-l @
categoriesoracle
postedMar 21, '03 at 1:32a
activeMar 21, '03 at 3:51p
posts6
users4
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase