Re: Check invalid synonym with dblink

From: Andy Hassall <andy_at_andyh.co.uk>
Date: Fri, 29 Aug 2003 22:40:12 +0100
Message-ID: <bghvkv0coi0egui808ah690t91clgfel19@4ax.com>

On 29 Aug 2003 06:18:32 -0700, v2000vvv_at_hotmail.com (KENY) wrote:

>I'm looking for a script or method to check the validity of synonyms
>which point on an invalidate dblink object.

 Since there's no record of the validity of remote objects in the local database, you'll probably have to use dynamic SQL (e.g. execute immediate) to query all_objects_at_db_link for the object.

 Something vaguely like, completely untested:

declare

    v_status user_objects.status%TYPE;
begin

    for v_obj in (select owner, table_owner, table_name, db_link

                  from   user_synonyms 
                  where  db_link is not null)
    loop
        execute immediate 'select status from all_objects@'||v_obj.db_link||
                          'where  owner = :1 and object_name = :2'
        into v_status
        using v_obj.table_owner, v_obj.table_name;

        dbms_output.put_line(v_obj.table_owner'||'.'||v_obj.table_name||
                             '@'||v_obj.db_link||' Status: '||v_status);

    end loop;
end;
/

 Or if you mean the database link itself is invalid, then that'll raise an exception when trying to query across it, so you can catch that too.

--
Andy Hassall (andy@andyh.co.uk) icq(5747695) (http://www.andyh.co.uk)
Space: disk usage analysis tool (http://www.andyhsoftware.co.uk/space)

Received on Fri Aug 29 2003 - 16:40:12 CDT