I have a table that has a unique increment column created using command "GENERATED AS IDENTITY" and the data type is NUMBER(20,0). Now, the dev want to change the data type to NUMBER(19,0). Since there are hundreds of tables and millions of data, creating a new set of tables just for one column change is not ideal. So, I manage to create a workflow as follows:
ALTER TABLE my_schema.my_table
ADD REC_ID_TEMP NUMBER(19,0);
UPDATE my_schema.my_table
SET REC_ID_TEMP = REC_ID;
ALTER TABLE my_schema.my_table
DROP COLUMN REC_ID;
ALTER TABLE my_schema.my_table
ADD REC_ID NUMBER(19,0) GENERATED BY DEFAULT AS IDENTITY;
UPDATE my_schema.my_table
SET REC_ID = REC_ID_TEMP;
ALTER TABLE my_schema.my_table
MODIFY REC_ID NUMBER(19,0) GENERATED AS IDENTITY MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1515 //Must start with the last REC_ID value 1// CACHE 20 NOORDER NOCYCLE NOKEEP NOSCALE NOT NULL ENABLE;
This is the best way possible that I can think of with this peanut size brain of mine. This way, I can make sure that the REC_ID is the exact same as before I change the data type. But, the problem is, if you read my the last line of my script, I have to check the last REC_ID for the hundreds of table before I can run the last script. I need help to figure out a way that I can retain the original state.
CodePudding user response：
You can use the START WITH LIMIT VALUE clause instead of specifying a number. From the documentation:
START WITH LIMIT VALUE, which is specific to identity_options, can only be used with ALTER TABLE MODIFY. If you specify START WITH LIMIT VALUE, then Oracle Database locks the table and finds the maximum identity column value in the table (for increasing sequences) or the minimum identity column value (for decreasing sequences) and assigns the value as the sequence generator's high water mark. The next value returned by the sequence generator will be the high water mark INCREMENT BY integer for increasing sequences, or the high water mark - INCREMENT BY integer for decreasing sequences.
CodePudding user response：
A little automation can make this as easily as calling a procedure per table, eg
SQL>
SQL> create table t1 as select * from scott.emp;
Table created.
SQL> create table t2 as select * from dba_objects;
Table created.
SQL> create table t3 as select * from dba_procedures;
Table created.
SQL>
SQL> alter table t1 add rec_id number(20) generated by default as identity start with 100000;
Table altered.
SQL> alter table t2 add rec_id number(20) generated by default as identity start with 100000;
Table altered.
SQL> alter table t3 add rec_id number(20) generated by default as identity start with 100000;
Table altered.
SQL>
SQL> update t1 set rec_id = rownum;
14 rows updated.
SQL> update t2 set rec_id = rownum;
81264 rows updated.
SQL> update t3 set rec_id = rownum;
37644 rows updated.
SQL>
SQL> alter table t1 add primary key ( rec_id);
Table altered.
SQL> alter table t2 add primary key ( rec_id);
Table altered.
SQL> alter table t3 add primary key ( rec_id);
Table altered.
SQL>
SQL> alter table t1 modify rec_id number(20) generated always as identity start with 100000;
Table altered.
SQL> alter table t2 modify rec_id number(20) generated always as identity start with 100000;
Table altered.
SQL> alter table t3 modify rec_id number(20) generated always as identity start with 100000;
Table altered.
SQL>
SQL> create or replace
2 procedure fix_up_my_recid(p_table varchar2) is
3 begin
4 execute immediate 'alter table '||p_table||' add tmp$rec_id number(19,0)';
5
6 execute immediate 'update '||p_table||' set tmp$rec_id = rec_id';
7
8 execute immediate 'alter table '||p_table||' set unused column rec_id';
9
10 execute immediate 'alter table '||p_table||' add rec_id number(19,0) generated by default as identity';
11
12 execute immediate 'update '||p_table||' set rec_id = tmp$rec_id';
13
14 execute immediate 'alter table '||p_table||' set unused column tmp$rec_id';
15
16 execute immediate 'alter table '||p_table||' move online';
17
18 execute immediate 'alter table '||p_table||' modify rec_id number(19,0) generated always as identity start with limit value';
19
20 end;
21 /
Procedure created.
SQL> sho err
No errors.
SQL>
SQL>
SQL>
SQL> desc t1
Name Null? Type
----------------------------------------------------------------------- -------- ------------------------------------------------
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
REC_ID NOT NULL NUMBER(20)
SQL> exec fix_up_my_recid('T1')
PL/SQL procedure successfully completed.
SQL> exec fix_up_my_recid('T2')
PL/SQL procedure successfully completed.
SQL> exec fix_up_my_recid('T3')
PL/SQL procedure successfully completed.
SQL> desc t1
Name Null? Type
----------------------------------------------------------------------- -------- ------------------------------------------------
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
REC_ID NOT NULL NUMBER(19)
SQL>
SQL>
SQL>
SQL>
SQL> desc t2
Name Null? Type
----------------------------------------------------------------------- -------- ------------------------------------------------
OWNER VARCHAR2(128)
OBJECT_NAME VARCHAR2(128)
SUBOBJECT_NAME VARCHAR2(128)
OBJECT_ID NUMBER
DATA_OBJECT_ID NUMBER
OBJECT_TYPE VARCHAR2(23)
CREATED DATE
LAST_DDL_TIME DATE
TIMESTAMP VARCHAR2(19)
STATUS VARCHAR2(7)
TEMPORARY VARCHAR2(1)
GENERATED VARCHAR2(1)
SECONDARY VARCHAR2(1)
NAMESPACE NUMBER
EDITION_NAME VARCHAR2(128)
SHARING VARCHAR2(18)
EDITIONABLE VARCHAR2(1)
ORACLE_MAINTAINED VARCHAR2(1)
APPLICATION VARCHAR2(1)
DEFAULT_COLLATION VARCHAR2(100)
DUPLICATED VARCHAR2(1)
SHARDED VARCHAR2(1)
CREATED_APPID NUMBER
CREATED_VSNID NUMBER
MODIFIED_APPID NUMBER
MODIFIED_VSNID NUMBER
REC_ID NOT NULL NUMBER(19)
SQL> desc t3
Name Null? Type
----------------------------------------------------------------------- -------- ------------------------------------------------
OWNER VARCHAR2(128)
OBJECT_NAME VARCHAR2(128)
PROCEDURE_NAME VARCHAR2(128)
OBJECT_ID NUMBER
SUBPROGRAM_ID NUMBER
OVERLOAD VARCHAR2(40)
OBJECT_TYPE VARCHAR2(13)
AGGREGATE VARCHAR2(3)
PIPELINED VARCHAR2(3)
IMPLTYPEOWNER VARCHAR2(128)
IMPLTYPENAME VARCHAR2(128)
PARALLEL VARCHAR2(3)
INTERFACE VARCHAR2(3)
DETERMINISTIC VARCHAR2(3)
AUTHID VARCHAR2(12)
RESULT_CACHE VARCHAR2(3)
ORIGIN_CON_ID NUMBER
POLYMORPHIC VARCHAR2(5)
REC_ID NOT NULL NUMBER(19)
SQL>
Note that I've not used DROP COLUMN because that's incredibly expensive. Generally better to just go with SET UNUSED, and I've thrown in an MOVE ONLINE at the end because all those updates could make a mess of your table row structure.