where is the sequence in Oracle associated with the Objectid field in ArcGIS, and how to manage it?

Question

I am working in ArcGIS 10.1 with tables from Oracle, now under control of SDE, after making use of the option: 'Register with Geodatabase'. All these tables have a column named 'Objectid' with an associated sequence controlled by SDE. However, my goal here is not just using ArcGIS exclusively but keeping the old functionality in Oracle, i.e. through SQL Developer, not via SDE. My biggest problem now is how I could handle the sequence associated with the 'Objectid' field.

So, where in the system is this sequence stored, I cannot see it in my Oracle schema's sequences.

In the end, how could I add records to the tables directly in Oracle without corrupting and/or badly affecting the SDE sequence?

Answer 1

One way of doing it:

1. Close ArcGIS and ArcCatalog.
2. In SQL Developer (Oracle 11g), the sequence of any Oracle table, if registered with the Geodatabase, will be created by SDE under a name similar to this one: 'R5834'
3. If you click in SQL Developer onto the tab 'Other Users' and then onto the 'SDE' user, you should be able to see the table 'TABLE_REGISTRY'. Open it and in the 'REGISTRATION_ID' column try to find which number is associated with the table you are looking for (i.e. 5834)
4. Go back to the table and see which is the last objectid value or either way go to the sequence R5834 in Oracle and see which is the last number. You need to remove that sequence, but save the SQL sentence first.
5. Generate the sequence again by SQL, but this time, change INCREMENT BY 16 to INCREMENT BY 1, and add to START WITH the last number of your current sequence, but adding one extra unit (+ 1).
6. Grant access to SDE to that sequence: grant select on R5834 to SDE;
7. Run select DBMS_PIPE.REMOVE_PIPE('ARCSDE_IDPIPE5834') from dual;

8. Create an INSERT trigger that does the following:

   CREATE OR REPLACE TRIGGER "schema"."trigger_name" BEFORE INSERT ON schema.table FOR EACH ROW BEGIN IF :NEW.OBJECTID = -1 THEN select SDE.VERSION_USER_DDL.NEXT_ROW_ID('schema',5834) INTO :NEW.OBJECTID FROM DUAL; END IF; END;

Resulting in:

If you use ArcGIS, the OBJECTID field will be populated by SDE automatically, but if you use Oracle directly, for instance from SQL Developer, just by typing -1 in the OBJECTID column in every new row, these values (-1) will be replaced by valid numbers from the sequence when you commit.

You can just use in the trigger any number you like, -1 is just an example, but choose a negative one.

I haven't tested this however at multiple editing by different users and/or by using Oracle and ArcGIS at the same time.

Answer 2

Find the registration ID:

SELECT 
    T.REGISTRATION_ID
    , T.TABLE_NAME 
FROM SDE.TABLE_REGISTRY T
WHERE 
    T.TABLE_NAME LIKE '%YOUR_TABLE%'

(let's say the registration ID is 555).

Build this statement:

SELECT SDE.VERSION_USER_DDL.NEXT_ROW_ID('OWNER_NAME', 555) FROM dual