You can convert an already existing table into a temporal table, which changes the structure of the table. This section describes how the structure of the tables changes. The following sample existing table is converted into all three temporal tables types in the examples in this section:.

Table policy_info
(
Policy_id char[4] not null primary key,
Coverage int not null
              )
And the tables contains the following initial rows
                   POLICY_ID       COVERAGE
                   -------------        -----------
                     ABC                12000
                     DEF                 13000
                     ERT                 14000

Example 1   Converting an existing table into System-period temporal table.

You convert the sample existing table into a system-period temporal table by adding SYSTEM_PERIOD, transaction id columns, and SYSTEM_TIME period as in the following:

ALTER TABLE policy_info
   ADD COLUMN sys_start TIMESTAMP(12) NOT NULL GENERATED ALWAYS AS ROW BEGIN;
ALTER TABLE policy_info 
   ADD COLUMN sys_end TIMESTAMP(12) NOT NULL GENERATED ALWAYS AS ROW END;
ALTER TABLE policy_info
   ADD COLUMN ts_id TIMESTAMP(12) NOT NULL GENERATED ALWAYS AS TRANSACTION START ID;
ALTER TABLE policy_info ADD PERIOD SYSTEM_TIME(sys_start, sys_end);

Then you create a history table for the new temporal table using one of the following two methods:

• 	CREATE TABLE hist_policy_info
  (
   policy_id     CHAR(4) NOT NULL,
  coverage     INT NOT NULL,
  sys_start    TIMESTAMP(12) NOT NULL ,
  sys_end      TIMESTAMP(12) NOT NULL,
  ts_id            TIMESTAMP(12) NOT NULL
   	       );
   ALTER TABLE hist_policy_info ADD RESTRICT ON DROP;
  

• CREATE TABLE hist_policy_info LIKE policy_info with RESTRICT ON DROP;
  

  The RESTRICT ON DROP clause will not allow the history table to get dropped while dropping system-period temporal table. Otherwise the history table gets implicitly dropped while dropping its associated temporal table. You can create a history table without RESTRICT ON DROP. A history table cannot be explicitly dropped.

  You should not use the GENERATED ALWAYS clause while creating a history table. The primary key of the system-period temporal table also does not apply here as there could be many updates for a particular row in the base table, which triggers many inserts into the history table for the same set of primary keys. Apart from these, the structure of a history table should be exactly same as its associated system-period temporal table. The history table must have the same number and order of columns as system-period temporal table. History table columns cannot explicitly be added, dropped, or changed. You must associate a system-period temporal table with its history table with the following statement:

   ALTER TABLE policy_info ADD VERSIONING USE HISTORY TABLE hist_policy_info.
  

  The GENERATED ALWAYS columns of the table are the ones that are always populated by the database manager so you do not have any control over these columns. The database manager populates these columns based on the system time.

  The extra added SYSTEM_PERIOD and transaction id columns will have default values for already existing rows as in the following:

  POLICY_ID                                   COVERAGE    SYS_START                                         SYS_END                                                TS_ID
  --------- ----------- -------------------------------- -------------------------------- -------------------------------------------------------------------------------
  ABC             12000 0001-01-01-00.00.00.000000000000 9999-12-30-00.00.00.000000000000 0001-01-01-00.00.00.000000000000
  DEF             13000 0001-01-01-00.00.00.000000000000 9999-12-30-00.00.00.000000000000 0001-01-01-00.00.00.000000000000
  ERT             14000 0001-01-01-00.00.00.000000000000 9999-12-30-00.00.00.000000000000 0001-01-01-00.00.00.000000000000
  

  The associated history table is populated with the before images once you start updating the temporal table.

Example 2   Converting an existing table into application-period temporal table.

You can convert the sample existing table into application-period temporal table by adding time columns and a BUSINESS_TIME period as in the following:

ALTER TABLE policy_info ADD COLUMN bus_start DATE NOT NULL DEFAULT '10/10/2001'"
ALTER TABLE policy_info ADD COLUMN bus_end DATE NOT NULL DEFAULT '10/10/2002'
ALTER TABLE policy_info ADD PERIOD BUSINESS_TIME(bus_start, bus_end)

While adding time columns, you need to make sure that while entering business validity time values of the existing time columns, the bus_start column always has value lesser than bus_end because these columns specify the business validity of the rows.

The new application-period temporal table will look similar to:

POLICY_ID   COVERAGE    BUS_START  BUS_END
--------- ----------- ---------- -------------------------------
ERT             14000              10/10/2001  10/10/2002
DEF             13000             10/10/2001   10/10/2002
ABC             12000             10/10/2001   10/10/2002

Example 3   Converting an existing table into bitemporal table.

You can convert the sample existing table into bitemporal table by adding SYSTEM_PERIOD, time columns along with the SYSTEM_TIME and BUSINESS_TIME period as in the following:

ALTER TABLE policy_info
   ADD COLUMN sys_start TIMESTAMP(12) NOT NULL GENERATED ALWAYS AS ROW BEGIN;
ALTER TABLE policy_info 
   ADD COLUMN sys_end TIMESTAMP(12) NOT NULL GENERATED ALWAYS AS ROW END;
ALTER TABLE policy_info
   ADD COLUMN ts_id TIMESTAMP(12) NOT NULL GENERATED ALWAYS AS TRANSACTION START ID;
ALTER TABLE policy_info ADD PERIOD SYSTEM_TIME(sys_start, sys_end);
 
ALTER TABLE policy_info ADD COLUMN bus_start DATE NOT NULL DEFAULT '10/10/2001'"
ALTER TABLE policy_info ADD COLUMN bus_end DATE NOT NULL DEFAULT '10/10/2002'
ALTER TABLE policy_info ADD PERIOD BUSINESS_TIME(bus_start, bus_end)

While adding the time columns, you must make sure that while entering business validity time values of already existing time columns, the bus_start column always has value lesser than bus_end because these columns specify the business validity of the rows.

Then you create a history table for the new temporal table using one of the following two methods:

• 	CREATE TABLE hist_policy_info
  (
   policy_id     CHAR(4) NOT NULL,
  coverage     INT NOT NULL,
  sys_start    TIMESTAMP(12) NOT NULL ,
  sys_end      TIMESTAMP(12) NOT NULL,
  ts_id            TIMESTAMP(12) NOT NULL
   	       );
   ALTER TABLE hist_policy_info ADD RESTRICT ON DROP;
  CREATE TABLE hist_policy_info LIKE policy_info with RESTRICT ON DROP;
  

• The RESTRICT ON DROP clause will not allow the history table to get dropped while dropping system-period temporal table. Otherwise the history table gets implicitly dropped while dropping its associated temporal table. You can create a history table without RESTRICT ON DROP. A history table cannot be explicitly dropped.

  You should not use the GENERATED ALWAYS clause while creating a history table. The primary key of the system-period temporal table also does not apply here as there could be many updates for a particular row in the base table, which triggers many inserts into the history table for the same set of primary keys. Apart from these, the structure of a history table should be exactly same as its associated system-period temporal table. The history table must have the same number and order of columns as system-period temporal table. History table columns cannot explicitly be added, dropped, or changed. You must associate a system-period temporal table with its history table with the following statement:

   ALTER TABLE policy_info ADD VERSIONING USE HISTORY TABLE hist_policy_info.
  

  The GENERATED ALWAYS columns of the table are the ones that are always populated by the database manager so you do not have any control over these columns. The database manager populates these columns based on the system time.

  The extra added SYSTEM_PERIOD and transaction id columns will have default values for already existing rows as in the following:

  POLICY_ID                                   COVERAGE    SYS_START                                         SYS_END                                                TS_ID
  --------- ----------- -------------------------------- -------------------------------- -------------------------------------------------------------------------------
  ABC             12000 0001-01-01-00.00.00.000000000000 9999-12-30-00.00.00.000000000000 0001-01-01-00.00.00.000000000000
  DEF             13000 0001-01-01-00.00.00.000000000000 9999-12-30-00.00.00.000000000000 0001-01-01-00.00.00.000000000000
  ERT             14000 0001-01-01-00.00.00.000000000000 9999-12-30-00.00.00.000000000000 0001-01-01-00.00.00.000000000000
  

  The associated history table is populated with the before images once you start updating the temporal table.

The extra added SYSTEM_TIME period, transaction id and time columns will have default values for already existing rows as in the following:

POLICY_ID COVERAGE    SYS_START                        SYS_END                          TS_ID                            BUS_START         BUS_END
--------- ----------- -------------------------------- -------------------------------- -------------------------------- ---------- -------------------------------------
ABC   12000 0001-01-01-00.00.00.000000000000 9999-12-30-00.00.00.000000000000 0001-01-01-00.00.00.000000000000 10/10/2001 10/10/2002
DEF             13000 0001-01-01-00.00.00.000000000000 9999-12-30-00.00.00.000000000000 0001-01-01-00.00.00.000000000000 10/10/2001 10/10/2002
ERT             14000 0001-01-01-00.00.00.000000000000 9999-12-30-00.00.00.000000000000 0001-01-01-00.00.00.000000000000 10/10/2001 10/10/2002

The history table is populated with the before images once user starts updating the temporal table.

Example 4   Replication in Non-Oracle Environment.

In a non-oracle configuration in which you do not have temporal tables at the apply side, you can only replicate the system-period and bitemporal tables though not the associated history tables. While performing replication in this situation, you must take care of the SYSTEM_PERIOD and transaction id columns value. These columns will have some values that the target database might not support. You should first use the map conversion functions to convert these values into the format that the target database supports, and then map the columns accordingly.

For example, MySQL has a DATETIME range from 1000-01-01 00:00:00.000000 to 9999-12-31 23:59:59.999999. You cannot replicate a timestamp value of 0001-01-01-00.00.00.000000000000 to MySQL. To replicate such values, you must convert this value into the MySQL DATETIME value 1000-01-01 00:00:00.000000, and then map the columns. If you have the following row in the policy_info system-period table:

POLICY_ID                                   COVERAGE    SYS_START                                         SYS_END                                                TS_ID
--------- ----------- -------------------------------- -------------------------------- -------------------------------------------------------------------------------
ABC             12000 0001-01-01-00.00.00.000000000000 9999-12-30-00.00.00.000000000000 0001-01-01-00.00.00.000000000000

To replicate the row into MySQL, you would use the colmap() function:

map source_schema.policy_info, target target_schema.policy_info colmap
(policy_id=policy_id, coverage=coverage, sys_start= @IF( ( @NUMSTR( @STREXT(sys_
start,1,4))) > 1000, sys_start, '1000-01-01 00.00.00.000000'), sys_end=sys_end,
 ts_id= @IF( ( @NUMSTR( @STREXT(ts_id,1,4))) > 1000, ts_id, '1000-01-01
 00.00.00.000000'));