arcsde oracle表objectid 超大值问题处理

有一表采用Sql 语句插入记录，objectid 超大

 

我自己造的一个：

 

 

 怎么改：

grant connect,resource,dba to user1;
grant create sequence to user1;

create or replace procedure P_ResetObjectID(owner in NVARCHAR2, tablename in NVARCHAR2)
is
/*
重置sde table objectID
*/
reg_id         pls_integer;
owner_l        VARCHAR2(64);
table_l        VARCHAR2(64);
pipe_name         VARCHAR2(30);
max_id           NUMBER;
stmt           varchar2(512);
sequence_name     VARCHAR2(64);
begin
    owner_l := UPPER(owner);
    table_l := UPPER(tablename);
    stmt := 'SELECT registration_id FROM SDE.table_registry '||
            'WHERE owner ='''|| owner_l ||''' AND table_name = '''|| table_l ||'''';
    EXECUTE IMMEDIATE stmt INTO  reg_id;
    if reg_id is null then return;
    end if;
    pipe_name := 'ArcSDE_IdPipe' || TO_CHAR (reg_id);
    --清除arcgis缓存管道
    stmt := 'select sys.dbms_pipe.remove_pipe('''||pipe_name||''') from dual ';
    EXECUTE IMMEDIATE stmt;
    --更新oid
    stmt := 'update '||table_l||' set objectid=rownum ';
    EXECUTE IMMEDIATE stmt;
    --获取最大oid
    stmt := 'select max(objectid)  from '||table_l;
    EXECUTE IMMEDIATE stmt INTO  max_id;
    if max_id is NULL then return;
    end if;
    --修改序列的当前值   下面drop 可能没有，第一次要注释掉
    sequence_name := 'R' || TO_CHAR (reg_id);
    stmt := 'drop  sequence '||sequence_name||' ';
    EXECUTE IMMEDIATE stmt;
    stmt := 'create sequence '||sequence_name||' minvalue 1 maxvalue 2147483647 start with '||max_id||' increment by 16 cache 20';
    EXECUTE IMMEDIATE stmt;
    stmt := 'grant select on '||owner_l||'.'||sequence_name||' to public';
    EXECUTE IMMEDIATE stmt;
end P_ResetObjectID;

应在去除用户连接、无用户编辑时，执行

begin
P_ResetObjectID('user1','DC_DJaaaXX');
end;

 INT最大数才2147483648

参照这个

https://www.dandelioncloud.cn/article/details/1585607600828043266

朱雀  2022-10-27 12:21 206阅读  0赞