Wednesday, July 20, 2016

How to test a DB-Link as SYS

VARIABLE owner VARCHAR2(30)
VARIABLE link VARCHAR2(128)
set serveroutput on
set define off
set verify off
set feed off
DECLARE 
cur_remote INTEGER;
lv_usr INTEGER;
lv_ignore INTEGER;
lv_db_name VARCHAR2(256);
lv_link_name VARCHAR2(128);

BEGIN

:owner:='$db_link->[0]';
:link:='$db_link->[1]';

FOR x IN ( select dbl.owner, dbl.db_link, dbl.username, dbl.host, du.user_id from 
dba_db_links dbl left join dba_users du on (dbl.owner = du.username)
where dbl.owner=:owner and dbl.db_link=:link
) LOOP

cur_remote := sys.dbms_sys_sql.open_cursor();

IF ( x.owner != 'PUBLIC' ) THEN
dbms_sys_sql.parse_as_user(
cur_remote,
'select property_value db_name
from database_properties\@'||x.db_link||
' where property_name=''GLOBAL_DB_NAME''',
DBMS_SQL.NATIVE,
x.user_id); 
ELSE
dbms_sys_sql.parse(
cur_remote,
'select property_value db_name
from database_properties\@'||x.db_link||
' where property_name=''GLOBAL_DB_NAME''',
DBMS_SQL.NATIVE); 
END IF;
 
dbms_sys_sql.define_column(cur_remote, 1, lv_db_name,256); 

lv_ignore := dbms_sys_sql.execute(cur_remote); 
 
IF dbms_sys_sql.fetch_rows(cur_remote)>0 THEN 
dbms_sys_sql.column_value(cur_remote, 1, lv_db_name); 
dbms_sys_sql.close_cursor(cur_remote); 
END IF;

-- dbms_output.put_line('x.db_link||','||sys_context('userenv','db_name')||','||x.owner||','||regexp_substr(lv_db_name,'[^.]+')||','||x.username);

sys.pk_compliance_sys.log_dblink_compliance (x.db_link, x.owner, regexp_substr(lv_db_name,'[^.]+'),
                             x.username, '', '' );
 
dbms_output.put_line('registered link '||x.db_link||' with taget db: '||regexp_substr(lv_db_name,'[^.]+'));

END LOOP;

EXCEPTION 
WHEN OTHERS THEN 
IF dbms_sys_sql.is_open(cur_remote) THEN 
dbms_sys_sql.CLOSE_CURSOR(cur_remote); 
END IF; 
RAISE;  
END;
/

No comments: