Reset ObjectID when truncating table

1688
4
06-22-2023 11:58 AM
Status: Closed
Labels (1)
Bud
by
Honored Contributor

When using the Truncate Table geoprocessing tool:

It would be helpful if we had the option to reset the ObjectID sequence, so that new rows start at ObjectID 1, etc.

Use Case:
We have large tables that we clear and reload on a schedule. We don't want to deal with huge ObjectIDs over time. And we don't want to delete the table and recreate it. Resetting the ObjectID would solve this issue.

Oracle 18c 10.7.1 EGDB

4 Comments
MichaelVolz

Bud:

When I use the Truncate Table tool it resets the OBJECTID back to 1 for me so when I append records they start at 1.  I used to use the Delete Rows method which did not reset the OBJECTID and that caused problems over time.

MarceloMarques

you can truncate the table with SQL as long as it is not registered as versioned ( traditional / branch ).

truncate table table_A;

https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/TRUNCATE-TABLE.html#GUID-B76E584...

then after the table is truncated, you can use SQL to reset the sequence.

alter sequence serial restart start with 1;

SQL Language Reference (oracle.com)

Example:

MarceloMarques_0-1687467467837.png

MarceloMarques_1-1687467609699.png

MarceloMarques_2-1687467645603.png

MarceloMarques_3-1687467755549.png

 

SSWoodward
Status changed to: Closed

Aftenoon @Bud,

Could you let me know what version of Pro you are using so I can try to reproduce in your same environment?

The Truncate GP tool does reset your Object IDs after running.  This is how it is designed to work. If this is not the behavior you're seeing, it means that the specific workflow being executed is exposing a bug. 

Since this is a bug and not and Idea, I'm going to close this thread. Please make sure to file a bug for this that contains your specific workflow so that we can figure out what's going on and get a fix.

MarceloMarques

Indeed. I tested with Pro 3.1.2 just to make sure, and the "Truncate Table" GP tool does reset the "Sequence" back to "1". See screenshots below.

MarceloMarques_0-1687820075887.pngMarceloMarques_1-1687820212433.png

MarceloMarques_2-1687820431518.png

MarceloMarques_3-1687820554448.png

MarceloMarques_5-1687820637630.png