ORA-00942: table or view does not exist .

The complete error is :
select xactts, subguid from MSrepl7 where pubsrv = ? and pubdb = ? and indagent = 0 and subtype = 0
ORA-00942: table or view does not exist

We were setting up heterogeneous replicaton between SQL and Oracle 9i.This was done successfully.But when we tried synchronize the Articles (actually we were replicating only a view)we got stuck at this error.

It was clear that the error is coming from the subscriber .But we were not replicating the object : MSrepl7 and we dont know whether this is a table or view .Since we were not replicating it , I was sure that this is a table or view that replication is creating .I saw a KB article which talks about this table for DB2 : http://support.microsoft.com/KB/313332 .

Later I found from other oracle subscribers that MSrepl7 is nothing but a replica of msreplication_subscriptions table in SQL Server subscribers. This table is looked up and matched with msrepl_transactions .The columns that are compared are transaction_timestamp in MSrepl7 with xact_seqno in msrepl_transactions table .

moving forward we wanted to find why this table does not exist on the subscriber . I suspected that it should be created while Subscription setup or while we reinitialize subscription or while synchronizing .


To see if its really being created I enabled tracing on Distribution agent since its this job which is failing .
-Output C:\Temp\OUTPUTFILE.txt -Outputverboselevel 2.

Ran the Agent again which failed with same error .checked the file:


OLE DB Subscriber 'ERPDEV.WORLD': create table MSrepl7 (pubsrv varchar2 (128), pubdb varchar2 (128), publcn varchar2 (128), indagent number (1, 0),subtype number (10, 0), dstagent varchar2 (100),timecol date,descr varchar2 (255), xactts raw (16), updmode number (3, 0), agentid raw (16), subguid raw (16), subid raw (16), immsync number (1, 0))

Connecting to Distributor 'MCMSMESVS1.distribution'
[4/21/2010 1:32:46 AM]MCMSMESVS1.distribution: {call master..sp_MScheck_agent_instance(N'MCMSMESVS1-MES-ERPDEV.WORLD-1', 10)}
OLE DB Subscriber 'ERPDEV.WORLD': select xactts, subguid from MSrepl7 where pubsrv = ? and pubdb = ? and indagent = 0 and subtype = 0
Agent message code 20046. ORA-00942: table or view does not exist

[4/21/2010 1:32:46 AM]MCMSMESVS1.distribution: {call sp_MSadd_distribution_history(1, 6, ?, ?, 0, 0, 0.00, 0x01, 1, ?, -1, 0x01, 0x01)}
Adding alert to msdb..sysreplicationalerts: ErrorId = 12,


This means that the table is being created.But its not there .Strange.

To check whats happening ,I connected to Oracle server and tried to create a test table ....


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production

SQL> create table test(t int);
create table test(t int)
*
ERROR at line 1:
ORA-01031: insufficient privileges



Looks like when we start synchronizing it does not create sufficient objects at Oracle side due to permission issue .Sadly , SQL also does not throw any errors that we cannot create the object due to permission issue (might not have put the Try catch for this error).

So,I requested the Oracle DBA in charge of this to give the appropriate permissions to the login that is executing the distribution agent job and execute the Distributor job again .

BINGO ........the issue was resolved ...hope this blog helps someone someday ..

Regards
Abhay