Skip to Main Content
  • Questions
  • insert into local table with select from multiple database links in a loop

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Oliver.

Asked: May 07, 2018 - 12:59 pm UTC

Last updated: May 24, 2018 - 2:23 am UTC

Version: 12.2

Viewed 1000+ times

You Asked

Hi Tom,
i would like to apply the Orignial SQL Statement from Oracle MOS DOC ID 1317265.1 and 1309070.1 for license and healthcheck for all of my database instances. My Goal is to create a centralized repository with informations of my databases. Unfortunately i get only error messages on the approach to create a Looping SQL Statement to run through the remote databases dynamically.

This is the first select i want to use:
select i.HOST_NAME,
       i.INSTANCE_NAME,
       d.NAME as database_name,
       d.OPEN_MODE,
       d.DATABASE_ROLE,
       d.CREATED,
       d.DBID,
       i.VERSION,
       v.BANNER
  from  V$INSTANCE i, V$DATABASE d, V$VERSION v
  where v.BANNER LIKE 'Oracle%' or v.BANNER like 'Personal Oracle%';

I have created a local table for Looping through my database instances:
create table ISG_ORA_DB
  (INSTANCE_NAME    VARCHAR2(16));
insert into ISG_ORA_DB values ('app3');
insert into ISG_ORA_DB values ('app4');
-- and so on...

The result the remote database instance should be inserted in a second local table like:
create table ISG_ORA_DB_ALLGEMEIN
  (CHECKED          date check (checked=trunc(checked,'dd')),
   HOST_NAME        VARCHAR2(64),
   INSTANCE_NAME    VARCHAR2(16),
   DATABASE_NAME    VARCHAR2(9),
   OPEN_MODE        VARCHAR2(20),
   DATABASE_ROLE    VARCHAR2(16),
   CREATED          DATE,
   DBID             NUMBER,
   VERSION          VARCHAR2(17),
   BANNER           VARCHAR2(80)
   );
alter table ISG_ORA_DB_ALLGEMEIN  add constraint ISG_ORA_DB_ALLGEMEIN_PK primary key(checked, instance_name);

The later script should collect information every day or week for each local and remote database instance.

I have created a database link for all database instances like:
CREATE DATABASE LINK MONITOR_APP3_LINK CONNECT TO ...


and Connor said...

I don't think you actually asked a question here, so I'll take a guess.

If you want to dynamically use a database link, you'll need dynamic SQL.

So you would have something like this:

declare
  l_sql_template := 
q'{  
insert into ISG_ORA_DB_ALLGEMEIN  
select i.HOST_NAME,
       i.INSTANCE_NAME,
       d.NAME as database_name,
       d.OPEN_MODE,
       d.DATABASE_ROLE,
       d.CREATED,
       d.DBID,
       i.VERSION,
       v.BANNER
  from  V$INSTANCE@link i, V$DATABASE@link d, V$VERSION@link v
  where v.BANNER LIKE 'Oracle%' or v.BANNER like 'Personal Oracle%'
}';
begin
  for i in ( select link_name from ISG_ORA_DB ) 
  loop
    execute immediate replace(l_sql_template, '@link', '@'||i.link_name);
  end loop;
end;
/


to cycle through each link.

Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Great

Oliver Kolm, May 11, 2018 - 12:19 pm UTC

Sorry for confusing. Thats exactly what i need. Thanks a lot. Oliver
Connor McDonald
May 12, 2018 - 1:28 am UTC

glad we could help

ORA-02020: too many database links in use

Oliver Kolm, May 23, 2018 - 11:44 am UTC

Hello Tom,

dynamic SQL generally works, but i get an error about too many database links.

-------------------------------------------------------------------------------
-- i have 27 instances
-------------------------------------------------------------------------------
SQL> select instance_name from ISG_ORA_DB;

INSTANCE_NAME
------------------------------------------------
ap12
ap14
ap22
...
tst1
tst3
tst6

27 rows selected.

-------------------------------------------------------------------------------
-- and i have 27 database links created
-------------------------------------------------------------------------------
SQL> select db_link from user_db_links;

DB_LINK
----------------------------------------------------------------------------------------------------
MONITOR_AP12_LINK.CAMPUS.GENDORF.NET
MONITOR_AP14_LINK.CAMPUS.GENDORF.NET
MONITOR_AP22_LINK.CAMPUS.GENDORF.NET
...
MONITOR_TST1_LINK.CAMPUS.GENDORF.NET
MONITOR_TST3_LINK.CAMPUS.GENDORF.NET
MONITOR_TST6_LINK.CAMPUS.GENDORF.NET

27 rows selected.

-------------------------------------------------------------------------------
-- my modified select i get an ora-error
-------------------------------------------------------------------------------
SQL> r
1 declare
2 l_sql_template varchar2(2000):=
3 q'{
4 insert into ISG_ORA_DB_ALLGEMEIN
5 select trunc(sysdate,'dd'),
6 i.INSTANCE_NAME,
7 i.HOST_NAME,
8 d.NAME as database_name,
9 d.OPEN_MODE,
10 d.DATABASE_ROLE,
11 d.CREATED,
12 d.DBID,
13 i.VERSION,
14 v.BANNER
15 from V$INSTANCE@link i, V$DATABASE@link d, V$VERSION@link v
16 where v.BANNER LIKE 'Oracle%' or v.BANNER like 'Personal Oracle%'
17 }';
18 begin
19 for i in ( select instance_name from ISG_ORA_DB )
20 loop
21 execute immediate replace(l_sql_template, '@link', '@MONITOR_'||upper(i.instance_name)||'_LINK');
22 end loop;
23* end;
declare
*
ERROR at line 1:
ORA-02020: too many database links in use
ORA-06512: at line 21

-------------------------------------------------------------------------------
-- if i reduce amount of instance to one instance dyncamic sql is working
-------------------------------------------------------------------------------
SQL> declare
l_sql_template varchar2(2000):=
q'{
insert into ISG_ORA_DB_ALLGEMEIN
select trunc(sysdate,'dd'),
i.INSTANCE_NAME,
i.HOST_NAME,
d.NAME as database_name,
d.OPEN_MODE,
d.DATABASE_ROLE,
d.CREATED,
d.DBID,
i.VERSION,
v.BANNER
from V$INSTANCE@link i, V$DATABASE@link d, V$VERSION@link v
where v.BANNER LIKE 'Oracle%' or v.BANNER like 'Personal Oracle%'
}';
begin
for i in ( select instance_name from ISG_ORA_DB where instance_name = 'ap12')
loop
execute immediate replace(l_sql_template, '@link', '@MONITOR_'||upper(i.instance_name)||'_LINK');
end loop;
end;
/
Connor McDonald
May 24, 2018 - 2:23 am UTC

In your loop, try issue an

alter session close database link XXX

once you've used it to collect your data

Now it works

Oliver Kolm, May 24, 2018 - 6:40 am UTC

Hello Connor,

now it works. Here is the whole statement for completeness.

declare
l_sql_template varchar2(2000):=
q'{
insert into ISG_ORA_DB_ALLGEMEIN
select trunc(sysdate,'dd'),
i.INSTANCE_NAME,
i.HOST_NAME,
d.NAME as database_name,
d.OPEN_MODE,
d.DATABASE_ROLE,
d.CREATED,
d.DBID,
i.VERSION,
v.BANNER
from V$INSTANCE@link i, V$DATABASE@link d, V$VERSION@link v
where v.BANNER LIKE 'Oracle%' or v.BANNER like 'Personal Oracle%'
}';

l_sql_session_close varchar2(200):=
q'{
alter session close database link @dblink
}';

begin
for i in ( select instance_name from ISG_ORA_DB )
loop
execute immediate replace(l_sql_template, '@link', '@MONITOR_'||upper(i.instance_name)||'_LINK');
commit;
execute immediate replace(l_sql_session_close, '@dblink', 'MONITOR_'||upper(i.instance_name)||'_LINK');
end loop;
end;
/

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database