Document ID:	   33515.1
Subject:            Script to Help with Diagnosis of Replication Setup Problems
Modified:           31 Jan 96 
Author:            TOSCROFT


rem These scripts can be used to diagnose set up problems with replication.
rem select on dba_repschema, dba_repobject, dba_repcat and dba_repcatlog
rem should be granted to the person running the script.
rem 
rem Compile them then run from SQLPlus with:
rem 
rem spool <filename>
rem set serverout on
rem execute rep_diag.rep_diag;
rem

create or replace package rep_diag is

procedure rep_diag;
procedure rep_schema;
procedure rep_obj;
procedure rep_err;
procedure rep_stat;

end rep_diag;
/
create or replace package body rep_diag is

procedure rep_diag is

begin

        rep_schema;
        rep_stat;
        rep_obj;
        rep_err;

end rep_diag;

procedure rep_schema as

   cursor c1 is select sname, dblink, masterdef
                from sys.dba_repschema;

begin

        dbms_output.put_line('Replicated Schema Details');
        dbms_output.put_line('-------------------------');
        dbms_output.put_line('.');

        for i in c1 loop

                dbms_output.put_line('Schema Name is:    '||i.sname);
                dbms_output.put_line('Is the Masterdef?: '||i.masterdef);
                dbms_output.put_line('DBLink name is:    '||i.dblink);
                dbms_output.put_line('.');

        end loop;

end rep_schema;

procedure rep_obj as

   cursor c2 is select sname, oname, type, status
                from sys.dba_repobject;                  

begin

        dbms_output.put_line('Objects Registered for Replication');
        dbms_output.put_line('----------------------------------');

        for i in c2 loop

                dbms_output.put_line('.');
                dbms_output.put_line('Schema Owner: '||i.sname);
                dbms_output.put_line('Object Name:  '||i.oname);
                dbms_output.put_line('Object Type:  '||i.type);
                dbms_output.put_line('Status:       '||i.status);
                dbms_output.put_line('.');

        end loop;
        

end rep_obj;

procedure rep_err is

        cursor c3 is select request, status, message, errnum 
                     from sys.dba_repcatlog;

begin

        dbms_output.put_line('Repcat Errors');
        dbms_output.put_line('-------------');

        for i in c3 loop

                dbms_output.put_line('.');
                dbms_output.put_line('Request: '||i.request);
                dbms_output.put_line('Status:  '||i.status);
                dbms_output.put_line('Message: '||i.message);
                dbms_output.put_line('Error:   '||i.errnum);
                dbms_output.put_line('.');

        end loop;

end rep_err;

procedure rep_stat is

        cursor c4 is select sname, master, status
                     from sys.dba_repcat;

begin

        dbms_output.put_line('Replication Status');
        dbms_output.put_line('------------------');

        for i in c4 loop

                dbms_output.put_line('.');
                dbms_output.put_line('Schema: '||i.sname);
                dbms_output.put_line('Master?:'||i.master);
                dbms_output.put_line('Status: '||i.status);
                dbms_output.put_line('.');

        end loop;

end rep_stat;
        
end rep_diag;