Skip to Main Content
  • Questions

  • Help to use a PL/SQL script for GG row count comparison

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Saravanan.

Asked: July 25, 2020 - 3:35 pm UTC

Last updated: July 28, 2020 - 9:03 am UTC

Version: 12.1.0.2

Viewed 1000+ times

You Asked

Hi Experts,

I was trying to GG row comparition script from https://grigorian.tech/GoldenGate_Row_Comparison_Script/GoldenGate_Row_Comparison_Script.htm That was not working. So i reduced the script to find the problem in my Database.

create or replace procedure rowcounts(v_srcschema in varchar2)
is
begin
dbms_output.put_line(v_srcschema);
for vtable in (select table_name from all_tables where owner=v_srcschema order by 1) loop
dbms_output.put_line(vtable.table_name);
end loop;
 exception
       when others
       then
              dbms_output.put_line(sqlerrm);
end;
/


It works fine for

SQL> exec rowcounts('SYS');
SYS
AUDIT_ACTIONS
AW$AWCREATE
AW$AWCREATE10G
AW$AWMD
AW$AWREPORT
AW$AWXML
AW$EXPRESS
DATA_PUMP_XPL_TABLE$
DUAL
HS$_PARALLEL_METADATA
HS_BULKLOAD_VIEW_OBJ
HS_PARTITION_COL_NAME
HS_PARTITION_COL_TYPE
IMPDP_STATS
KU$NOEXP_TAB
KU$XKTFBUE
KU$_DATAPUMP_MASTER_10_1
KU$_DATAPUMP_MASTER_11_1
KU$_DATAPUMP_MASTER_11_1_0_7
KU$_DATAPUMP_MASTER_11_2
KU$_DATAPUMP_MASTER_12_0
KU$_LIST_FILTER_TEMP
KU$_LIST_FILTER_TEMP_2
ODCI_PMO_ROWIDS$
ODCI_SECOBJ$
ODCI_WARNINGS$
PLAN_TABLE$
PSTUBTBL
SAM_SPARSITY_ADVICE
SPD_SCRATCH_TAB
STMT_AUDIT_OPTION_MAP
SYSTEM_PRIVILEGE_MAP
TABLE_PRIVILEGE_MAP
USER_PRIVILEGE_MAP
WRI$_ADV_ASA_RECO_DATA
WRI$_HEATMAP_TOPN_DEP1
WRI$_HEATMAP_TOPN_DEP2
WRR$_REPLAY_CALL_FILTER
XS$VALIDATION_TABLE

PL/SQL procedure successfully completed.

But when i use any application schema, it returns null.

SQL> exec rowcounts('APP_SCHEMA'); -- (App Username Masked)
APP_SCHEMA
PL/SQL procedure successfully completed.

I get results from my login when i run sql

SQL> select table_name from all_tables where owner='APP_SCHEMA' order by 1
...
...
17 rows selected.


My Privs

User, his roles and privileges
--------------------------------------------------------------------------------
SG224441
CONNECT
CREATE SESSION
SET CONTAINER
DBA_SCHEMA
ALTER ANY INDEX
ALTER ANY PROCEDURE
ALTER ANY SEQUENCE
ALTER ANY TABLE
ALTER ANY TYPE
ALTER SESSION
CONNECT
CREATE SESSION
SET CONTAINER
CREATE ANY DIRECTORY
CREATE ANY INDEX
CREATE ANY MATERIALIZED VIEW
CREATE ANY PROCEDURE
CREATE ANY SEQUENCE
CREATE ANY SYNONYM
CREATE ANY TABLE
CREATE ANY TRIGGER
CREATE ANY TYPE
CREATE ANY VIEW
DELETE ANY TABLE
DROP ANY DIRECTORY
DROP ANY INDEX
DROP ANY MATERIALIZED VIEW
DROP ANY PROCEDURE
DROP ANY SEQUENCE
DROP ANY SYNONYM
DROP ANY TABLE
DROP ANY TRIGGER
DROP ANY TYPE
DROP ANY VIEW
EXECUTE ANY PROCEDURE
EXECUTE ANY PROGRAM
EXECUTE ANY TYPE
EXP_FULL_DATABASE
ADMINISTER RESOURCE MANAGER
ADMINISTER SQL MANAGEMENT OBJECT
BACKUP ANY TABLE
CREATE SESSION
CREATE TABLE
EXECUTE ANY PROCEDURE
EXECUTE ANY TYPE
EXECUTE_CATALOG_ROLE
HS_ADMIN_EXECUTE_ROLE
EXEMPT REDACTION POLICY
READ ANY FILE GROUP
RESUMABLE
SELECT ANY SEQUENCE
SELECT ANY TABLE
SELECT_CATALOG_ROLE
HS_ADMIN_SELECT_ROLE
GRANT ANY OBJECT PRIVILEGE
INSERT ANY TABLE
RESOURCE
CREATE CLUSTER
CREATE INDEXTYPE
CREATE OPERATOR
CREATE PROCEDURE
CREATE SEQUENCE
CREATE TABLE
CREATE TRIGGER
CREATE TYPE
SELECT ANY SEQUENCE
SELECT ANY TABLE
UPDATE ANY TABLE
SELECT_CATALOG_ROLE
HS_ADMIN_SELECT_ROLE
UNLIMITED TABLESPACE


10046 Trace
============

for SYS

PARSING IN CURSOR #140258405816256 len=58 dep=1 uid=132 oct=3 lid=132 tim=6276618231745 hv=2380292834 ad='e3ca4f1d0' sqlid='4mhm1nq6y0sr2'
SELECT OWNER,TABLE_NAME FROM ALL_TABLES WHERE OWNER='SYS'
END OF STMT
PARSE #140258405816256:c=105352,e=120595,p=0,cr=30,cu=0,mis=1,r=0,dep=1,og=1,plh=292766746,tim=6276618231745
EXEC #140258405816256:c=32,e=32,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,plh=292766746,tim=6276618231832
WAIT #140258405816256: nam='Disk file operations I/O' ela= 16 FileOperation=2 fileno=1 filetype=2 obj#=-1 tim=6276618328194
WAIT #140258405816256: nam='Disk file operations I/O' ela= 271 FileOperation=2 fileno=0 filetype=15 obj#=37 tim=6276618328745
WAIT #140258405816256: nam='db file sequential read' ela= 9701 file#=1 block#=105948 blocks=1 obj#=37 tim=6276618338360
WAIT #140258405816256: nam='gc current block 2-way' ela= 730 p1=1 p2=147800 p3=1 obj#=37 tim=6276618345203
WAIT #140258405816256: nam='Disk file operations I/O' ela= 73 FileOperation=2 fileno=0 filetype=15 obj#=37 tim=6276618482627
WAIT #140258405816256: nam='db file sequential read' ela= 6872 file#=1 block#=29087 blocks=1 obj#=37 tim=6276618489461
WAIT #140258405816256: nam='Disk file operations I/O' ela= 64 FileOperation=2 fileno=0 filetype=15 obj#=37 tim=6276618501631
WAIT #140258405816256: nam='db file sequential read' ela= 3933 file#=1 block#=20400 blocks=1 obj#=37 tim=6276618505533
WAIT #140258405816256: nam='Disk file operations I/O' ela= 92 FileOperation=2 fileno=0 filetype=15 obj#=37 tim=6276618579968
WAIT #140258405816256: nam='db file sequential read' ela= 9066 file#=1 block#=17055 blocks=1 obj#=37 tim=6276618588985
WAIT #140258405816256: nam='Disk file operations I/O' ela= 87 FileOperation=2 fileno=0 filetype=15 obj#=37 tim=6276618690296
WAIT #140258405816256: nam='db file sequential read' ela= 364 file#=1 block#=89007 blocks=1 obj#=37 tim=6276618690621
FETCH #140258405816256:c=496956,e=537595,p=5,cr=63379,cu=0,mis=0,r=39,dep=1,og=1,plh=292766746,tim=6276618769501
STAT #140258405816256 id=1 cnt=39 pid=0 pos=1 obj=0 op='FILTER (cr=63379 pr=5 pw=0 time=137273 us)'
STAT #140258405816256 id=2 cnt=1277 pid=1 pos=1 obj=0 op='HASH JOIN RIGHT OUTER (cr=60715 pr=5 pw=0 time=167769 us cost=1368 size=17202 card=14
1)'
STAT #140258405816256 id=3 cnt=306 pid=2 pos=1 obj=47 op='INDEX FULL SCAN I_USER2 (cr=1 pr=0 pw=0 time=212 us cost=1 size=1224 card=306)'
STAT #140258405816256 id=4 cnt=1277 pid=2 pos=2 obj=0 op='NESTED LOOPS OUTER (cr=60714 pr=5 pw=0 time=163612 us cost=1367 size=16638 card=141)'
STAT #140258405816256 id=5 cnt=1277 pid=4 pos=1 obj=0 op='NESTED LOOPS OUTER (cr=59224 pr=5 pw=0 time=153260 us cost=1087 size=15369 card=141)'
STAT #140258405816256 id=6 cnt=1277 pid=5 pos=1 obj=0 op='NESTED LOOPS OUTER (cr=59077 pr=5 pw=0 time=149937 us cost=1085 size=14664 card=141)'
STAT #140258405816256 id=7 cnt=1277 pid=6 pos=1 obj=0 op='HASH JOIN (cr=55751 pr=5 pw=0 time=133200 us cost=802 size=12972 card=141)'
STAT #140258405816256 id=8 cnt=86 pid=7 pos=1 obj=16 op='TABLE ACCESS FULL TS$ (cr=91 pr=0 pw=0 time=493 us cost=30 size=258 card=86)'
STAT #140258405816256 id=9 cnt=1277 pid=7 pos=2 obj=0 op='NESTED LOOPS (cr=55660 pr=5 pw=0 time=129387 us cost=772 size=12549 card=141)'
STAT #140258405816256 id=10 cnt=42872 pid=9 pos=1 obj=0 op='NESTED LOOPS (cr=34584 pr=5 pw=0 time=120400 us cost=633 size=53466 card=938)'
STAT #140258405816256 id=11 cnt=1 pid=10 pos=1 obj=0 op='NESTED LOOPS (cr=2 pr=0 pw=0 time=4122 us cost=4 size=20 card=1)'
STAT #140258405816256 id=12 cnt=1 pid=11 pos=1 obj=22 op='TABLE ACCESS BY INDEX ROWID USER$ (cr=2 pr=0 pw=0 time=19 us cost=1 size=20 card=1)'
STAT #140258405816256 id=13 cnt=1 pid=12 pos=1 obj=46 op='INDEX UNIQUE SCAN I_USER1 (cr=1 pr=0 pw=0 time=8 us cost=0 size=0 card=1)'
STAT #140258405816256 id=14 cnt=1 pid=11 pos=2 obj=0 op='VIEW (cr=0 pr=0 pw=0 time=4099 us cost=3 size=0 card=1)'
STAT #140258405816256 id=15 cnt=1 pid=14 pos=1 obj=0 op='HASH JOIN (cr=0 pr=0 pw=0 time=4098 us cost=3 size=33 card=1)'
STAT #140258405816256 id=16 cnt=1 pid=15 pos=1 obj=0 op='FIXED TABLE FULL X$KSPPI (cr=0 pr=0 pw=0 time=1160 us cost=1 size=29 card=1)'
STAT #140258405816256 id=17 cnt=3978 pid=15 pos=2 obj=0 op='FIXED TABLE FULL X$KSPPCV (cr=0 pr=0 pw=0 time=2117 us cost=1 size=15912 card=3978)
'
STAT #140258405816256 id=18 cnt=42872 pid=10 pos=2 obj=18 op='TABLE ACCESS BY INDEX ROWID BATCHED OBJ$ (cr=34582 pr=5 pw=0 time=107654 us cost=
630 size=34706 card=938)'
STAT #140258405816256 id=19 cnt=46502 pid=18 pos=1 obj=37 op='INDEX RANGE SCAN I_OBJ2 (cr=485 pr=5 pw=0 time=20072 us cost=11 size=0 card=958)'
STAT #140258405816256 id=20 cnt=1277 pid=9 pos=2 obj=4 op='TABLE ACCESS CLUSTER TAB$ (cr=21076 pr=0 pw=0 time=127536 us cost=1 size=32 card=1)'
STAT #140258405816256 id=21 cnt=8982 pid=20 pos=1 obj=3 op='INDEX UNIQUE SCAN I_OBJ# (cr=11545 pr=0 pw=0 time=68996 us cost=0 size=0 card=1)'
STAT #140258405816256 id=22 cnt=1125 pid=6 pos=2 obj=14 op='TABLE ACCESS CLUSTER SEG$ (cr=3326 pr=0 pw=0 time=13037 us cost=2 size=12 card=1)'
STAT #140258405816256 id=23 cnt=1125 pid=22 pos=1 obj=9 op='INDEX UNIQUE SCAN I_FILE#_BLOCK# (cr=2201 pr=0 pw=0 time=7681 us cost=1 size=0 card
=1)'
STAT #140258405816256 id=24 cnt=75 pid=5 pos=2 obj=36 op='INDEX RANGE SCAN I_OBJ1 (cr=147 pr=0 pw=0 time=2603 us cost=2 size=5 card=1)'
STAT #140258405816256 id=25 cnt=1119 pid=4 pos=2 obj=36 op='INDEX RANGE SCAN I_OBJ1 (cr=1490 pr=0 pw=0 time=8835 us cost=2 size=9 card=1)'
STAT #140258405816256 id=26 cnt=39 pid=1 pos=2 obj=0 op='HASH JOIN SEMI (cr=2664 pr=0 pw=0 time=188268 us cost=3 size=12 card=1)'
STAT #140258405816256 id=27 cnt=2554 pid=26 pos=1 obj=0 op='FIXED TABLE FULL X$KZSRO (cr=0 pr=0 pw=0 time=12308 us cost=0 size=6 card=2)'
STAT #140258405816256 id=28 cnt=303 pid=26 pos=2 obj=62 op='INDEX RANGE SCAN I_OBJAUTH1 (cr=2664 pr=0 pw=0 time=18243 us cost=3 size=18 card=2)
'
STAT #140258405816256 id=29 cnt=0 pid=1 pos=3 obj=0 op='FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=17 us cost=0 size=14 card=2)'
CLOSE #140258405816256:c=4,e=3,dep=1,type=3,tim=6276618771583


for APP Schema

PARSING IN CURSOR #140258405816256 len=58 dep=1 uid=132 oct=3 lid=132 tim=6276618231745 hv=2380292834 ad='e3ca4f1d0' sqlid='4mhm1nq6y0sr2'
SELECT OWNER,TABLE_NAME FROM ALL_TABLES WHERE OWNER='SYS'
END OF STMT
PARSE #140258405816256:c=105352,e=120595,p=0,cr=30,cu=0,mis=1,r=0,dep=1,og=1,plh=292766746,tim=6276618231745
EXEC #140258405816256:c=32,e=32,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,plh=292766746,tim=6276618231832
WAIT #140258405816256: nam='Disk file operations I/O' ela= 16 FileOperation=2 fileno=1 filetype=2 obj#=-1 tim=6276618328194
WAIT #140258405816256: nam='Disk file operations I/O' ela= 271 FileOperation=2 fileno=0 filetype=15 obj#=37 tim=6276618328745
WAIT #140258405816256: nam='db file sequential read' ela= 9701 file#=1 block#=105948 blocks=1 obj#=37 tim=6276618338360
WAIT #140258405816256: nam='gc current block 2-way' ela= 730 p1=1 p2=147800 p3=1 obj#=37 tim=6276618345203
WAIT #140258405816256: nam='Disk file operations I/O' ela= 73 FileOperation=2 fileno=0 filetype=15 obj#=37 tim=6276618482627
WAIT #140258405816256: nam='db file sequential read' ela= 6872 file#=1 block#=29087 blocks=1 obj#=37 tim=6276618489461
WAIT #140258405816256: nam='Disk file operations I/O' ela= 64 FileOperation=2 fileno=0 filetype=15 obj#=37 tim=6276618501631
WAIT #140258405816256: nam='db file sequential read' ela= 3933 file#=1 block#=20400 blocks=1 obj#=37 tim=6276618505533
WAIT #140258405816256: nam='Disk file operations I/O' ela= 92 FileOperation=2 fileno=0 filetype=15 obj#=37 tim=6276618579968
WAIT #140258405816256: nam='db file sequential read' ela= 9066 file#=1 block#=17055 blocks=1 obj#=37 tim=6276618588985
WAIT #140258405816256: nam='Disk file operations I/O' ela= 87 FileOperation=2 fileno=0 filetype=15 obj#=37 tim=6276618690296
WAIT #140258405816256: nam='db file sequential read' ela= 364 file#=1 block#=89007 blocks=1 obj#=37 tim=6276618690621
FETCH #140258405816256:c=496956,e=537595,p=5,cr=63379,cu=0,mis=0,r=39,dep=1,og=1,plh=292766746,tim=6276618769501
STAT #140258405816256 id=1 cnt=39 pid=0 pos=1 obj=0 op='FILTER (cr=63379 pr=5 pw=0 time=137273 us)'
STAT #140258405816256 id=2 cnt=1277 pid=1 pos=1 obj=0 op='HASH JOIN RIGHT OUTER (cr=60715 pr=5 pw=0 time=167769 us cost=1368 size=17202 card=14
1)'
STAT #140258405816256 id=3 cnt=306 pid=2 pos=1 obj=47 op='INDEX FULL SCAN I_USER2 (cr=1 pr=0 pw=0 time=212 us cost=1 size=1224 card=306)'
STAT #140258405816256 id=4 cnt=1277 pid=2 pos=2 obj=0 op='NESTED LOOPS OUTER (cr=60714 pr=5 pw=0 time=163612 us cost=1367 size=16638 card=141)'
STAT #140258405816256 id=5 cnt=1277 pid=4 pos=1 obj=0 op='NESTED LOOPS OUTER (cr=59224 pr=5 pw=0 time=153260 us cost=1087 size=15369 card=141)'
STAT #140258405816256 id=6 cnt=1277 pid=5 pos=1 obj=0 op='NESTED LOOPS OUTER (cr=59077 pr=5 pw=0 time=149937 us cost=1085 size=14664 card=141)'
STAT #140258405816256 id=7 cnt=1277 pid=6 pos=1 obj=0 op='HASH JOIN (cr=55751 pr=5 pw=0 time=133200 us cost=802 size=12972 card=141)'
STAT #140258405816256 id=8 cnt=86 pid=7 pos=1 obj=16 op='TABLE ACCESS FULL TS$ (cr=91 pr=0 pw=0 time=493 us cost=30 size=258 card=86)'
STAT #140258405816256 id=9 cnt=1277 pid=7 pos=2 obj=0 op='NESTED LOOPS (cr=55660 pr=5 pw=0 time=129387 us cost=772 size=12549 card=141)'
STAT #140258405816256 id=10 cnt=42872 pid=9 pos=1 obj=0 op='NESTED LOOPS (cr=34584 pr=5 pw=0 time=120400 us cost=633 size=53466 card=938)'
STAT #140258405816256 id=11 cnt=1 pid=10 pos=1 obj=0 op='NESTED LOOPS (cr=2 pr=0 pw=0 time=4122 us cost=4 size=20 card=1)'
STAT #140258405816256 id=12 cnt=1 pid=11 pos=1 obj=22 op='TABLE ACCESS BY INDEX ROWID USER$ (cr=2 pr=0 pw=0 time=19 us cost=1 size=20 card=1)'
STAT #140258405816256 id=13 cnt=1 pid=12 pos=1 obj=46 op='INDEX UNIQUE SCAN I_USER1 (cr=1 pr=0 pw=0 time=8 us cost=0 size=0 card=1)'
STAT #140258405816256 id=14 cnt=1 pid=11 pos=2 obj=0 op='VIEW (cr=0 pr=0 pw=0 time=4099 us cost=3 size=0 card=1)'
STAT #140258405816256 id=15 cnt=1 pid=14 pos=1 obj=0 op='HASH JOIN (cr=0 pr=0 pw=0 time=4098 us cost=3 size=33 card=1)'
STAT #140258405816256 id=16 cnt=1 pid=15 pos=1 obj=0 op='FIXED TABLE FULL X$KSPPI (cr=0 pr=0 pw=0 time=1160 us cost=1 size=29 card=1)'
STAT #140258405816256 id=17 cnt=3978 pid=15 pos=2 obj=0 op='FIXED TABLE FULL X$KSPPCV (cr=0 pr=0 pw=0 time=2117 us cost=1 size=15912 card=3978)
'
STAT #140258405816256 id=18 cnt=42872 pid=10 pos=2 obj=18 op='TABLE ACCESS BY INDEX ROWID BATCHED OBJ$ (cr=34582 pr=5 pw=0 time=107654 us cost=
630 size=34706 card=938)'
STAT #140258405816256 id=19 cnt=46502 pid=18 pos=1 obj=37 op='INDEX RANGE SCAN I_OBJ2 (cr=485 pr=5 pw=0 time=20072 us cost=11 size=0 card=958)'
STAT #140258405816256 id=20 cnt=1277 pid=9 pos=2 obj=4 op='TABLE ACCESS CLUSTER TAB$ (cr=21076 pr=0 pw=0 time=127536 us cost=1 size=32 card=1)'
STAT #140258405816256 id=21 cnt=8982 pid=20 pos=1 obj=3 op='INDEX UNIQUE SCAN I_OBJ# (cr=11545 pr=0 pw=0 time=68996 us cost=0 size=0 card=1)'
STAT #140258405816256 id=22 cnt=1125 pid=6 pos=2 obj=14 op='TABLE ACCESS CLUSTER SEG$ (cr=3326 pr=0 pw=0 time=13037 us cost=2 size=12 card=1)'
STAT #140258405816256 id=23 cnt=1125 pid=22 pos=1 obj=9 op='INDEX UNIQUE SCAN I_FILE#_BLOCK# (cr=2201 pr=0 pw=0 time=7681 us cost=1 size=0 card
=1)'
STAT #140258405816256 id=24 cnt=75 pid=5 pos=2 obj=36 op='INDEX RANGE SCAN I_OBJ1 (cr=147 pr=0 pw=0 time=2603 us cost=2 size=5 card=1)'
STAT #140258405816256 id=25 cnt=1119 pid=4 pos=2 obj=36 op='INDEX RANGE SCAN I_OBJ1 (cr=1490 pr=0 pw=0 time=8835 us cost=2 size=9 card=1)'
STAT #140258405816256 id=26 cnt=39 pid=1 pos=2 obj=0 op='HASH JOIN SEMI (cr=2664 pr=0 pw=0 time=188268 us cost=3 size=12 card=1)'
STAT #140258405816256 id=27 cnt=2554 pid=26 pos=1 obj=0 op='FIXED TABLE FULL X$KZSRO (cr=0 pr=0 pw=0 time=12308 us cost=0 size=6 card=2)'
STAT #140258405816256 id=28 cnt=303 pid=26 pos=2 obj=62 op='INDEX RANGE SCAN I_OBJAUTH1 (cr=2664 pr=0 pw=0 time=18243 us cost=3 size=18 card=2)
'
STAT #140258405816256 id=29 cnt=0 pid=1 pos=3 obj=0 op='FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=17 us cost=0 size=14 card=2)'
CLOSE #140258405816256:c=4,e=3,dep=1,type=3,tim=6276618771583

Please assist

and Connor said...

You are most likely hitting this

https://asktom.oracle.com/Misc/RolesAndProcedures.html

Since you ultimately want to run a count on every table, you'll need the SELECT ANY TABLE privilege granted *directly* not via a role to your user

Is this answer out of date? If it is, please let us know via a Comment
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Security

All of the vital components for a secure database are covered in the Security guide.