SYNC versions didn't delete after unregistering replica (revived from the old forum)

[anthonysanchez]

Hello all,
We have a client who is experiencing the "orphaned sync versions" mentioned in this article from the old forum:

http://forums.esri.com/thread.asp?t=229883&f=2284&c=158&ESRISessionID=eDLV2sxwfg6nhGiBLXjRHuNO_R5ANd...

Here is their environment: 

oracle 10.2.0.3 on linux
arcsde 9.2 sp6

details:

18 registered replicas

86 versions with names like 'SYNC%';

I believe 45 of these versions are orphaned per a query i put together.  I'm sure it could be made cleaner using regular expressions, but I've written the ugly way in case anyone out there is still using oracle 9i.  Please let me know your thoughts on whether safe to use as a delete reference or if can be improved.

select owner,
name,
substr(name,instr(name,'_',1,2)+1,instr(name,'_',1,3)-instr(name,'_',1,2)-1) replica_id
from sde.versions
where name  like 'SYNC%'
and name not like 'SYNC_RECEIVE_REC%'
and substr(name,instr(name,'_',1,2)+1,instr(name,'_',1,3)-instr(name,'_',1,2)-1)
not in (select to_char(id) from sde.gdb_replicas)
union
select owner,
name,
substr(name,instr(name,'_',1,3)+1,instr(name,'_',1,4)-instr(name,'_',1,2)-1) replica_id
from sde.versions
where name like 'SYNC_RECEIVE_REC%'
and substr(name,instr(name,'_',1,3)+1,instr(name,'_',1,4)-instr(name,'_',1,2)-1)
not in (select to_char(id) from sde.gdb_replicas);

Thanks,

Anthony