Using SQL command to alter table
You can use ALTER TABLE command to change the definition of the table.
CREATE TABLE demo_acct(
acct_id INTEGER,
acct_data JSON,
PRIMARY KEY(acct_id)
)
ALTER TABLE demo_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.
ALTER TABLE demo_acct(DROP acct_balance)
ALTER TABLE demo_acct(DROP acct_id)
Error handling command ALTER TABLE demo_acct(DROP acct_id):
Error: at (1, 27) Cannot remove a primary key field: acct_id
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.
HOURS
or
DAYS
.ALTER TABLE demo_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.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.
ALTER TABLE <table_name> ADD REGIONS <region_name>
Explanation: Here, table_name is an MR table and region_name 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.
ALTER TABLE <table_name> DROP REGIONS <comma_separated_list_of_regions>
Here, table_name is a MR Table and comma_separated_list_of_regions is a list of regions to be dropped.