Replication SQL Scripts

Count of Objects replicated in Database

SELECT   base_sname “OWNER”, COUNT (UNIQUE base_oname) NUMBER_OF_OBJECTS
FROM   REPCAT$_GENERATED
GROUP BY   base_sname;

List number of replicated objects with corresponding groups

SELECT   gname Replication_Group, COUNT ( * ) NUMBER_OF_TABLES
FROM   REPCAT$_REPOBJECT
WHERE   TYPE = 2
GROUP BY   gname
ORDER BY   1;                                                              — List number of replicated objects

SELECT   COUNT ( * ) NUMBER_OF_OBJECTS, TYPE TYPE_OF_OBJECT
FROM   REPCAT$_REPOBJECT
GROUP BY   TYPE;

List replication Group and Object Name

SELECT   gname Replication_Group, oname OBJECT_NAME
FROM   REPCAT$_REPOBJECT
WHERE   TYPE = 2
ORDER BY   1

Can I locate which tables, indexes and other objects are replicated?

SELECT   BASE_TYPE “Type of Object”,
COUNT (UNIQUE base_oname) NUMBER_OF_OBJECTS
FROM   REPCAT$_GENERATED
WHERE   BASE_TYPE = &1
GROUP BY   base_type;

where BASE_TYPE 1 INDEX
2 TABLE
4 VIEW
5 SYNONYM
6 SEQUENCE
7 PROCEDURE
8 FUNCTION
9 PACKAGE
10 PACKAGE BODY
12 TRIGGER
13 TYPE
14 TYPE BODY
32 INDEX TYPE

What is a size in MB of each replicated object?

SELECT   Segment_Name TABLE_NAME, ROUND (SUM (bytes) / 1000000) Size_In_MB
FROM   dba_segments
WHERE   segment_name IN (  SELECT   UNIQUE oname FROM REPCAT$_REPOBJECT)
GROUP BY   segment_name
ORDER BY   2 DESC;