This method:
1 Note all the constraints on the transaction-time table.
2 Drop all the constraints from the transaction-time table.
3 Use NONTEMPORAL ALTER TABLE to add two new columns of type TIMESTAMP(6) WITH TIME ZONE. For the purposes of this procedure, assume the columns are named sys_start and sys_end. These will hold the beginning and ending bound values of the new SYSTEM_TIME derived period column.
4 Use NONTEMPORAL UPDATE to populate the new columns with the start and end values of the existing transaction-time columns or derived period column.
5 Use NONTEMPORAL ALTER TABLE to drop the existing transaction-time column. Use the WITHOUT DELETE option to preserve the historical closed rows, which would otherwise be deleted automatically when you drop the transaction-time column:
ALTER TABLE transaction_time_table_name
DROP transaction_time_column WITHOUT DELETE
6 Use ALTER TABLE to create the SYSTEM_TIME derived period column and to add attributes to the set the sys_start and sys_end columns in the same ALTER TABLE statement:
ALTER TABLE transaction_time_table_name
ADD PERIOD FOR SYSTEM_TIME(sys_start,sys_end)
ADD sys_start TIMESTAMP(6) WITH TIME ZONE NOT NULL
GENERATED ALWAYS AS ROW START
add sys_end TIMESTAMP(6) WITH TIME ZONE NOT NULL
GENERATED ALWAYS AS ROW END;
7 Add system versioning to make the new table an ANSI system-time temporal table:
ALTER TABLE transaction_time_table_name
ADD SYSTEM VERSIONING;
8 Recreate all the constraints that were dropped in step 2. Note that ANSI constraints behave as NONSEQUENCED constraints.