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:

Viewing Tables

You can view Oracle NoSQL Database Cloud Service tables from the NoSQL console.

To view tables:
  1. Access the NoSQL console from the Infrastructure Console. See Accessing the Service from the Infrastructure Console .
  2. You can view all the tables in your tenancy from the NoSQL console.

Viewing Indexes

You can view Oracle NoSQL Database Cloud Service the list of indexes created for a NoSQL table from the NoSQL console.

To view 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.

Upload data into tables

You can insert all data of a table using a single upload action.

To upload data:

  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 upload data into a table, Click the table name. The Table Details page opens up.
  3. On the Table Details page, click Upload Data.
  4. A new page opens. You can either drop the file in the given textbox or upload the file from your local storage. Note: The file to be uploaded must be in JSON format.
Note

Note: If the table is a Global Active table, then uploading data in one region also adds the data to all the regional table replicas.

Viewing Table Details

Learn how to view Oracle NoSQL Database Cloud Service table details from the NoSQL console.

To view table details:
  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. A column in the list (Child tables) shows how many child tables are owned by the specified table.
  4. The list of child tables for a given parent table can be viewed by clicking the Child tables link under Resources on the parent table's details page.
  5. A column in the list (Schema state) takes in one of the two values, Mutable and Frozen. The schema for a table with no replication is mutable and for a Global Active table schema is frozen (schema cannot be changed).
  6. A column in the list (Global active) indicates if it is a singleton table (no replication) or a Global Active table.
  7. The NoSQL console lists all the tables in the tenancy. To view table details, do either of the following:
    • Click the table name, or
    • Click the action menu corresponding to the table name and select View Details.
    The Table Details page opens up.
  8. From the Table Details page, you can view all table columns, indexes, rows, and metrics. The fields Schema state and Local replica initialization completeness are shown only if the table is a Global Active table.
    Description of singletab_set.png follows

Viewing Table DDL

You can view the DDL statement used to create a table for the Table Details page.
To view table DDL:
  1. In the Table Details page, click View Table DDL.
    The View Table DDL window displays the table DDL statement.
  2. Now, you can select and copy the table DDL statement from the window. Click OK to close the window.

Viewing Table DDL

Learn how to view Oracle NoSQL Database Cloud Service table DDL from the NoSQL console.

To view table DDL:
  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 View Table DDL.
    The View Table DDL window displays the table DDL statement.
  4. Now, you can select and copy the table DDL statement from the window. Click OK to close the window.

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).

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.

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.

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.

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.

Viewing Table Metrics

Learn how to view Oracle NoSQL Database Cloud Service table metrics from the NoSQL console.

To view table metrics:
  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 Metrics tab under Resources.
    Table metrics such as Read Units, Write Units, Storage GB, Read Throttle Count, Write Throttle Count, and Storage Throttle Count show up. You can filter the metrics by date, change interval, and statistic value.
  4. For each of the metrics displayed on this page, you can perform the following actions:
    • View Query in Metrics Explorer: This page lets you write and edit queries in Monitoring Query Language (MQL), using metrics from either your application or an Oracle Cloud Infrastructure service. If you're not familiar with MQL, see Monitoring Query Language (MQL) Reference. To learn more about this page, see Metrics Explorer.
    • Copy Chart URL: Click this option to copy the default metrics chart URL for any future reference.
    • Copy Query (MQL): Click this option to copy the MQL query used to create the default metrics chart. If you're not familiar with MQL, see Monitoring Query Language (MQL) Reference.
    • Create an Alarm on this Query: Click this option to create alarms to monitor your cloud resources. To learn about alarms, see Managing Alarms.

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.