Managing Tables and Indexes Using Console

Learn how to manage Oracle NoSQL Database Cloud Service tables and indexes from the Console.

This article has the following topics:

Related Topics

Editing Tables

You can update reserved capacity (if the table is not an Always Free NoSQL table) and Time to Live (TTL) values for your Oracle NoSQL Database Cloud Service tables from the NoSQL console.

To edit tables:
  1. Access the NoSQL console from the Infrastructure Console. See Accessing the Service from the Infrastructure Console .
  2. The NoSQL console lists all the tables in the tenancy.
  3. The value of Time to Live (TTL) can be updated.
    • To update the value of Time to Live (TTL), click the Edit link next to the Time to live (Days) field.
    • You can also update the value of Time to Live (TTL) by clicking the action menu corresponding to the table name you wish to change and select Edit default time to live.
    • If the table is a child table, only the Time to live (TTL) value can be updated. To update the value of Time to Live (TTL), click the Edit link next to the Time to live (Days) field.

      Note:

      You cannot edit the reserved capacity of a child table directly. Only the corresponding values of the parent table can be edited.
    • Table Time to Live (Days): (optional) Specify the default expiration time for the rows in the table. After this time, the rows expire automatically, and are no longer available. The default value is zero, indicating no expiration time.

      Note:

      Updating Table Time to Live (TTL) will not change the TTL value of any existing data in the table. The new TTL value will only apply to those rows that are added to the table after this value is modified and to the rows for which no overriding row-specific value has been supplied.
  4. If your table is not an Always Free NoSQL table, then the reserved capacity and the usage model can be modified.
    • Under More Actions, click Edit reserved capacity.
    • You can also update the Reserved Capacity by clicking the action menu corresponding to the table name you wish to change and select Edit reserved capacity.

      Modify the following values for the table:

    • Read Capacity (ReadUnits): Enter the number of read units. See Estimating Capacity to learn about read units.
    • Write Capacity (WriteUnits): Enter the number of write units. See Estimating Capacity to learn about write units.
    • Disk Storage (GB): Specify the disk space in gigabytes (GB) to be used by the table. See Estimating Capacity to learn about storage capacity.

      You can also modify the Capacity mode from Provisioned Capacity to on Demand Capacity or vice-versa. If you provision units greater than what the on Demand capacity can offer, and then If you switch from Provisioned capacity to On Demand capacity, the capacity of the table will be reduced. You should take into consideration the reduction in the capacity due to the switch in this scenario.

  5. (Optional) To dismiss the changes, click Cancel.
To view help for the current page, click the help link at the top of the page.
If the table that is edited is a Global Active table:
  • Change in storage capacity or the value of table TTL have global scope (change in one regional table replica is automatically propagated to all regional table replicas).
  • Change in read units, write units or change in capacity mode from On-Demand to provisioned or vice-versa have a local scope (change only in the regional table replica where it is initiated).

Moving Tables

Learn how to move Oracle NoSQL Database Cloud Service table to a different compartment from the NoSQL console.

To move a table:
  1. Access the NoSQL console from the Infrastructure Console. See Accessing the Service from the Infrastructure Console .
  2. The NoSQL console lists all the tables in the tenancy.
  3. In the Table Details page, click Move Table.
  4. Alternatively, Click the action menu corresponding to the table name and select Move table.
  5. In the Move Resource to a Different Compartment window, modify the following values for the table:
    • Choose New Compartment: Select the new compartment from the select list.
  6. Click Move table.
  7. (Optional) To dismiss the changes, click the Cancel link on the top right corner.
To view help for the current page, click the help link at the top of the page.

Note:

You cannot move a child table to another compartment. If the parent table is moved to a new compartment, all the descendant tables within the hierarchy will be automatically moved to the target compartment in a single operation.

Deleting Tables

Learn how to delete Oracle NoSQL Database Cloud Service tables from the NoSQL console.

To delete tables:
  1. Access the NoSQL console from the Infrastructure Console. See Accessing the Service from the Infrastructure Console .
  2. The NoSQL console lists all the tables in the tenancy. To delete the table, do either of the following:
    • Click the table name. In the Table Details page, click the Delete button, or
    • Click the action menu corresponding to the table name you wish to delete and select Delete.
    • If a table has child tables, then the child table should be deleted first before deleting the parent table.
    The Delete Table confirmation dialog opens.
  3. Click Delete.
    The table is deleted.

If the table is a Global Active table, you can drop the table only if there are no regional replicas currently added to the table. Otherwise, the regional replicas should be deleted first before dropping the table.

Deleting Indexes

Learn how to delete Oracle NoSQL Database Cloud Service indexes from the NoSQL console.

To delete indexes:
  1. Access the NoSQL console from the Infrastructure Console. See Accessing the Service from the Infrastructure Console .
  2. The NoSQL console lists all the tables in the tenancy.
  3. In the Table Details page, select the Indexes tab under Resources.
    You will see a list of all the indexes added to the table.
  4. Click the action menu corresponding to the index you wish to delete, and select Delete.
    The Delete Index confirmation dialog opens.
  5. Click Delete.
    The index is deleted.

Altering Singleton Tables

Learn how to alter singleton tables by adding in simple or advanced mode, or deleting columns using the NoSQL console.

The NoSQL console lets you alter the Oracle NoSQL Database Cloud Service tables in two modes:
  1. Simple Input Mode: You can use this mode to alter the NoSQL Database Cloud Service table declaratively, that is, without writing a DDL statement.
  2. Advanced DDL Input Mode: You can use this mode to alter the NoSQL Database Cloud Service table using a DDL statement.

Note: In a Global Active table, the schema of the table cannot be changed. You cannot add new fields or remove existing fields from a Global Active table.

Adding Table Columns: Simple Input Mode

Learn how to add table columns to an Oracle NoSQL Database Cloud Service table by using the Simple Input table column update mode.

To add table columns:
  1. Access the NoSQL console from the Infrastructure Console. See Accessing the Service from the Infrastructure Console.
  2. The NoSQL console lists all the tables in the tenancy. In the Table Details page, select the Columns tab under Resources.
    You will see a list of all the columns added to the table.
  3. Click Add Columns.
  4. In the Add Columns window, select Simple Input for Table Column Update Mode.
  5. In the Columns section, enter non-primary column details:
    Description of createtable_col.png follows
    Description of the illustration createtable_col.png

    • Column Name: Enter the column name. Ensure that you conform to column naming requirements described in Oracle NoSQL Database Cloud Service Limits .
    • Type: Select the data type for your column.
    • Precision:This is applicable for TIMESTAMP typed columns only. Timestamp values have precision in fractional seconds that range from 0 to 9. For example, a precision of 0 means that no fractional seconds are stored, 3 means that the timestamp stores milliseconds and 9 means a precision of nanoseconds. 0 is the minimum precision, and 9 is the maximum.
    • Size: This is applicable for BINARY typed columns only. Specify the size in bytes to make the binary a fixed binary.
    • Default Value: (optional) Supply a default value for the column.

      Note:

      Default values can not be specified for binary and JSON data type columns.
    • Value is Not Null: Click this option to specify that a column must always have a value.
    • + Another Column: Click this button to add more columns.
  6. Click Add Columns.
    The new columns are added to the table.
To view help for the current page, click the help link at the top of the page.

Adding Table Columns: Advanced DDL Input Mode

Learn how to add table columns to an Oracle NoSQL Database Cloud Service table by using the Advanced DDL table column update mode.

To add table columns:
  1. Access the NoSQL console from the Infrastructure Console. See Accessing the Service from the Infrastructure Console .
  2. The NoSQL console lists all the tables in the tenancy.
  3. Click Add Columns.
  4. In the Add Columns window, select Advanced DDL Input for Table Column Update Mode.
  5. Enter the update table DDL statement. For an example, See Alter Table in Developers Guide.
  6. Click Add Columns.
    The new columns are added to the table.

Deleting Table Columns

Learn how to delete columns from a singleton table.

To delete table columns:
  1. Access the NoSQL console from the Infrastructure Console. See Accessing the Service from the Infrastructure Console.
  2. The NoSQL console lists all the tables in the tenancy. In the Table Details page, select the Columns tab under Resources. You will see a list of all the columns added to the table.
  3. Click the three vertical dots at the end of any non-primary key column to be deleted. You get a confirmation message if the column can be deleted. Once you confirm, the column is deleted.

    Note:

    The columns of a Global Active table cannot be deleted as the schema is frozen.

Change the schema of a Global Active table

You should avoid changing the schema of a Global Active table as any changes to the schema involve re-synchronizing data to all replicas.

You can instead leverage the flexibility available with the JSON column to bring about the schema changes. If you are not able to avoid a schema change to a Global Active table, follow the steps outlined below:
  1. Drop the replicas of the table whose schema has to be changed.
  2. In the table details, click Unfreeze to change the schema of the singleton table back to Mutable.
  3. Change the schema of the table.
  4. In the table details, click Freeze to freeze the schema of the table. The Schema state changes to Frozen.
  5. Re-create the regional table replicas.

Drop a regional replica

You can drop a regional replica of a Global Active table.

From the Tables page, Under Resources, click Replicas. All Replicas of the Global Active table are listed. To remove a regional replica, click the three dots on the right side of the regional replica and choose Delete. Once you provide a confirmation, the regional replica is deleted. Once all the regional replicas are removed, the Global Active table becomes a singleton table.

Dropping replicas of parent-child tables:

Dropping a replica is to drop the table in the replica region. A parent-child table has dependencies. When the child table is a Global Active table, before you drop a replica from a parent table, you need to drop the replica from the child table first. You drop a replica from the bottom to the top of the table hierarchy in a parent-child table (drop the child from the replica first and then the parent). If the child table is a Global Active table, use drop-replica from the other replica region to drop it. If the child table is a singleton table, use drop-table to drop it from the replica region.

Example: You have three parent-child tables - A, A.B, and A.B.C. The parent-child tables have the following replicas:
  • Table A is a GAT having replicas on iad, phx, and sjc
  • Table A.B is a GAT having replicas on iad, phx
  • Table A.B.C is a singleton table in the region phx
To drop replica phx from table A, its child tables A.B and A.B.C should be dropped from phx firstly from bottom to top:
  1. on phx, drop table A.B.C
  2. on iad, drop replica phx from table A.B

Change a Global Active table to a Singleton table

You can remove all replicas from a Global Active table and it changes back to a singleton table.

  • In the Table Details page, click Replicas under Resources. You get a list of regional replicas that are added to the table.
  • Click the action menu corresponding to the regional replica you wish to delete and select Delete.

Once all the replicas are deleted, the table becomes a singleton table. You can verify this by looking at the list of tables in your database. Under Tables, you can see that the Global active option for this table has a value No.