Skip to Main Content

Database Software

Announcement

For appeals, questions and feedback, please email oracle-forums_moderators_us@oracle.com

Why is there a duplicate row in LIVE workspace in table with primary key?

John OToole (Dublin)Mar 8 2018 — edited Mar 29 2018

Hi,

We are using Workspace Manager with database 11.2.0.4.

We have a version enabled table "ROAD_NETWORK" which has a primary key on the GUID.  The table has 35k rows and is updated a few hundred times a day through an internal web application.  We noticed this week that there is one duplicate row in the table when queried from the LIVE workspace.  I don't know how it got there and I can't reproduce the scenario.  I know I can easily get rid of it by deleting one of the rows by rowid, but I'm curious to hear if there is some issue which can lead to this scenario.

The table has these constraints:

SQL> select *

  2  from user_wm_constraints

  3  where table_name = 'ROAD_NETWORK';

CONSTRAINT_NAME    CO TABLE_NAME SEARCH_CONDITION                      STATUS   INDEX_OWNER INDEX_NAME   INDEX_TYPE

------------------ -- ---------- ---------------------------------     ------- ------------ ------------ ----------

ROAD_NETWORK_PK    U  ROAD_NETWORK                                     ENABLED  PRIME2      ROAD_NETWORK_PK NORMAL

SYS_C00221675      C  ROAD_NETWORK  "GUID" IS NOT NULL                 ENABLED

SYS_C00221676      C  ROAD_NETWORK  "ROAD_NETWORK_TYPE_ID" IS NOT NULL ENABLED

There is just one duplicate:

SQL> select guid, count(*)

  2  from road_network

  3  having count(*) > 1

  4  group by guid;

GUID                                   COUNT(*)

------------------------------------ ----------

e6196473-7090-5350-e040-a8c096b4679c          2

Here it is:

SQL> select guid

  2  from road_network

  3  where guid = 'e6196473-7090-5350-e040-a8c096b4679c';

GUID

------------------------------------

e6196473-7090-5350-e040-a8c096b4679c

e6196473-7090-5350-e040-a8c096b4679c

Here's the blood and guts from the LT table:

SQL> select guid, version, createtime, retiretime, nextver, delstatus, replace(ltlock, 'NAMED_GROUP_EDITOR', 'EDITOR') as ltlock

  2  from road_network_lt

  3  where guid = 'e6196473-7090-5350-e040-a8c096b4679c'

  4  order by  createtime;

GUID                                    VERSION CREATETIME                               RETIRETIME                               NEXTVER          DELSTATUS LTLOCK

------------------------------------ ---------- ---------------------------------------- ---------------------------------------- --------------- ---------- ----------------------------------------

e6196473-7090-5350-e040-a8c096b4679c          0 11-JUN-16 02.08.20.774000 PM +01:00      21-DEC-17 10.05.25.198000 AM +00:00      ,175686,                10 *-1,-9,-1*

e6196473-7090-5350-e040-a8c096b4679c     175686 21-DEC-17 10.05.25.198000 AM +00:00      07-MAR-18 11.23.16.145000 AM +00:00      ,189316,                11 *-1,-9,0*$#EDITOR_APP$#

e6196473-7090-5350-e040-a8c096b4679c     189316 07-MAR-18 11.23.16.145000 AM +00:00      07-MAR-18 11.23.30.397000 AM +00:00      ,189316,                12 *-1,-9,175686*$#EDITOR_ACCESS$#

e6196473-7090-5350-e040-a8c096b4679c     189316 07-MAR-18 11.23.30.397000 AM +00:00      07-MAR-18 11.23.30.455000 AM +00:00      ,189316,                13 *-1,-9,189316*$#EDITOR_ACCESS$#

e6196473-7090-5350-e040-a8c096b4679c     189316 07-MAR-18 11.23.30.455000 AM +00:00      07-MAR-18 11.27.47.108000 AM +00:00      ,189319,189321,         14 *-1,-9,189316*$#EDITOR_ACCESS$#

e6196473-7090-5350-e040-a8c096b4679c     189319 07-MAR-18 11.27.46.707000 AM +00:00                                               -1                      15 *-1,-9,189316*$#EDITOR_ACCESS$#

e6196473-7090-5350-e040-a8c096b4679c     189321 07-MAR-18 11.27.47.108000 AM +00:00                                               -1                      15 *-1,-9,189316*$#EDITOR_ACCESS$#

7 rows selected.

There is currently one row in the AUX table, but for a different GUID

SQL> select * from road_network_aux;

GUID                                 CHILDSTATE     PARENTSTATE   SNAPSHOTCHILD VERSIONCHILD SNAPSHOTPARENT VERSIONPARENT V WM_OPCODE

------------------------------------ -------------- ------------- ------------- ------------ -------------- ------------- - ----------

e61a4187-2ba0-7779-e040-a8c096b45c0c 86190          LIVE                     16       146449             15        137658 0 CC

This looks similar to the issue reported here: How to remove duplicate row in LIVE Workspace.

Any idea how this could have happened?

Thanks,

John

This post has been answered by Ben Speckhard-Oracle on Mar 26 2018
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Apr 26 2018
Added on Mar 8 2018
4 comments
379 views