Thread: Change column datatype from INT to BIGINT
Hi everyone
Hope you’re doing good!
We need to change the data type of one of the columns in the large table from INT to BIGINT because it reaches the maximum integer value. What is the best way to do this conversion from INT to BIGINT with no downtime or very minimal impact, I look forward to receiving help from the experts here. Thanks
I guess it depends on how far you are from the column reaching its max value and if it's also a primary key on the table or not.
Assuming you still have enough time for this conversion:
If column is NOT primary key:
1. Ensure you have enough space in your database
2. Add a new bigint column
3. Add a trigger to populate a new column from the original as the changes come in
4. Back populate new column for the existing records
5. Take a small downtime window:
a) rename old column to something like column_old
b) rename new column as old column
6. validate and drop old column
If the column is also a Primary key, there are few more steps required before you can swap
~ Michael
On Mon, Mar 11, 2024 at 1:12 PM srinivasan s <srinioracledba7@gmail.com> wrote:
Hi everyoneHope you’re doing good!We need to change the data type of one of the columns in the large table from INT to BIGINT because it reaches the maximum integer value. What is the best way to do this conversion from INT to BIGINT with no downtime or very minimal impact, I look forward to receiving help from the experts here. Thanks