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;
/
Wednesday, July 20, 2016
How to test a DB-Link as SYS
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment