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