Using SQL command to alter table

You can use ALTER TABLE command to change the definition of the table.

Create a sample table :

CREATE TABLE stream_acct(
acct_id INTEGER,
acct_data JSON, 
PRIMARY KEY(acct_id)
)

Add/remove schema fields

Example : Add schema field to the table schema.

ALTER TABLE stream_acct(ADD acct_balance INTEGER)

Explanation: Adding a field does not affect the existing rows in the table. If a field is added, its default value or NULL will be used as the value of this field in existing rows that do not contain it. The field to add maybe a top-level field (i.e. A table column) or it may be deeply nested inside a hierarchical table schema. As a result, the field is specified via a path.

Example : Remove schema fields in the table schema.

ALTER TABLE stream_acct(DROP acct_balance)

Explanation: You can drop any field in the schema other than the primary key. If you try removing the primary key field, you get an error as shown below.

ALTER TABLE stream_acct(DROP acct_id)

Output( showing error):

Error handling command ALTER TABLE stream_acct(DROP acct_id):
Error: at (1, 27) Cannot remove a primary key field: acct_id

Alter TTL value

Example : Modify the Time-To-Live value of the table

Time-to-Live (TTL) is a mechanism that allows you to set a time frame on table rows, after which the rows expire automatically, and are no longer available. By default, every table that you create has a TTL value of zero, indicating that it has no expiration time.

You can use ALTER TABLE command to change this value for any table. You can specify the TTL with a number, followed by either HOURS or DAYS.

ALTER TABLE stream_acct USING TTL 5 days

Note:

Altering the TTL value for a table does not change the TTL value for existing rows in the table. Rather, it will only change the default TTL value placed in rows created subsequent to the alter table. To modify the TTL of every record in a table, you must iterate through each record of the table and update its TTL value.

Add/remove a region

Example : Add a region

The add regions clause lets you link an existing Multi-Region Table (MR Table) with new regions in a multi-region Oracle NoSQL Database environment. You use this clause to expand MR Tables to new regions.

Associate a new region with an existing MR Table using the DDL command shown below.

ALTER TABLE myTable ADD REGIONS FRA;

Explanation: Here, myTable is an MR table and FRA is an existing region.

Example : Remove a region

The drop regions clause lets you disconnect an existing MR Table from a participating region in a multi-region Oracle NoSQL Database environment. You use this clause to contract MR Tables to fewer regions.

To remove an MR Table from a specific region in a Multi-Region NoSQL Database setup, you must run the following steps from all the other participating regions.

ALTER TABLE myTable DROP REGIONS FRA

Here, myTable is a MR Table and FRA is the region to be dropped. You can supply a comma_separated_list_of_regions if you want to drop more than one region.

Enable before-images

Example : Enable before-images for table write operations

The before-image of any write is the table row before it gets updated or deleted by a DML operation You can use the ALTER TABLE statement with the ENABLE BEFORE IMAGE clause to enable the generation of before-images for any write operations on the table as shown in the example below:

ALTER TABLE stream_acct ENABLE BEFORE IMAGE USING TTL 10 DAYS

The statement above enables before-images generation with a TTL value of 10 days. The before-images for writes on the stream_acct table are stored on the disk for 10 days after they are generated. After this duration, the before-images expire freeing up the disk space and will not appear in the stream.

Note:

In addition to enabling before-images on the table using the SQL statement, you must also enable before-images in the subscription configuration through the Streams API. For more details on Oracle NoSQL Database Streams API, see Streams Developer's Guide.

You can also enable before-images without a TTL definition as follows:

ALTER TABLE stream_acct ENABLE BEFORE IMAGE

The statement above enables before-images generation on the stream_acct table. The generated before-images remain for 24 hours, unless adjusted by a TTL value.