This method:
- does not require the NONTEMPORAL privilege.
- can be used on transaction-time tables that are row-partitioned on the beginning or ending bound of the transaction-time period.
- Create a new table with columns that match the non-transaction-time columns of the existing table. Add two new TIMESTAMP(6) WITH TIME ZONE columns that will hold the beginning and ending bound values for the ANSI system-time derived period column. For the purposes of this procedure, assume the columns are named sys_start and sys_end.
- Use a NONSEQUENCED INSERT ... SELECT to copy the rows of the transaction-time table into the new table.
-
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 new_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; - Note the constraints on the transaction-time table.
- Drop the transaction-time table.
- Rename the new table as the old table.
-
Add system versioning to make the new table an ANSI system-time temporal table:
ALTER TABLE new_table_name ADD SYSTEM VERSIONING; - Recreate all the constraints that were dropped in step 2. Note that ANSI constraints behave as NONSEQUENCED constraints.