steampipe plugin install oci

Table: oci_nosql_table - Query OCI NoSQL Database Tables using SQL

Oracle NoSQL Database is a fully managed NoSQL database cloud service that provides on-demand throughput and storage-based provisioning. It is designed for today's most demanding applications that require low latency responses, flexible data models, and elastic scaling for dynamic workloads. With Oracle NoSQL Database, you can focus on application development and deployment without worrying about infrastructure provisioning, patching, and scaling.

Table Usage Guide

The oci_nosql_table table provides insights into NoSQL Database Tables within Oracle Cloud Infrastructure (OCI). As a Database Administrator or Developer, you can explore table-specific details through this table, including the schema, storage, and associated metadata. Utilize it to discover information about tables, such as their capacity, the indexes they use, and the time of their last modification.

Examples

Basic info

Explore which NoSQL tables are in use, along with their creation times and lifecycle states, to gain insights into resource allocation and usage patterns. This can help optimize database management and resource planning.

select
name,
id,
lifecycle_state,
time_created
from
oci_nosql_table;
select
name,
id,
lifecycle_state,
time_created
from
oci_nosql_table;

List tables that are not active

Explore which NoSQL tables in your Oracle Cloud Infrastructure are not currently active. This can help in managing resources and identifying any tables that may be underutilized or no longer needed.

select
name,
id,
lifecycle_state,
time_created
from
oci_nosql_table
where
lifecycle_state <> 'ACTIVE';
select
name,
id,
lifecycle_state,
time_created
from
oci_nosql_table
where
lifecycle_state <> 'ACTIVE';

List tables with disk storage greater than 1024 GB

Explore which tables are using significant disk storage space to manage resources more effectively. This helps in identifying tables that might be consuming more storage than expected, assisting in efficient resource allocation and cost management.

select
name,
id,
lifecycle_state,
time_created
from
oci_nosql_table
where
cast(table_limits -> 'maxStorageInGBs' as INTEGER) > 1024;
select
name,
id,
lifecycle_state,
time_created
from
oci_nosql_table
where
CAST(
json_extract(table_limits, '$.maxStorageInGBs') as INTEGER
) > 1024;

Count child tables for parent tables with children

Analyze the settings to understand the distribution of child tables across parent tables. This helps in assessing the complexity of your NoSQL database structure and can guide optimization efforts.

select
t2.name as parent,
count(t1.*) as child_count
from
oci_nosql_table t1
join oci_nosql_table t2 on t1.title like t2.title || '.%'
and t1.title <> t2.title
group by
parent;
select
t2.name as parent,
count(t1.name) as child_count
from
oci_nosql_table t1
join oci_nosql_table t2 on t1.title like t2.title || '.%'
and t1.title <> t2.title
group by
parent;

Count child tables for parent tables with and without children

Determine the number of child tables linked to each parent table in your NoSQL database. This allows you to understand the complexity and structure of your data, helping in its efficient management and navigation.

select
t2.name as parent,
-- To exclude the parent table from being counted as a child, we subtract 1 from the count.
count(t1.*) - 1 as child_count
from
oci_nosql_table t1
join oci_nosql_table t2 on t1.title like t2.title || '%'
group by
parent;
select
t2.name as parent,
-- To exclude the parent table from being counted as a child, we subtract 1 from the count.
count(t1.name) - 1 as child_count
from
oci_nosql_table t1
join oci_nosql_table t2 on t1.title like t2.title || '%'
group by
parent;

Schema for oci_nosql_table

NameTypeOperatorsDescription
_ctxjsonbSteampipe context in JSON form.
compartment_idtext=The OCID of the compartment in Tenant in which the resource is located.
ddl_statementtextA DDL statement representing the schema.
defined_tagsjsonbDefined tags for resource. Defined tags are set up in your tenancy by an administrator. Only users granted permission to work with the defined tags can apply them to resources.
freeform_tagsjsonbFree-form tags for resource. This tags can be applied by any user with permissions on the resource.
idtext=Unique identifier that is immutable.
is_auto_reclaimablebooleanTrue if this table can be reclaimed after an idle period.
lifecycle_detailstextA message describing the current state in more detail.
lifecycle_statetext=The state of a table.
nametext=Immutable human-friendly table name.
regiontextThe OCI region in which the resource is located.
schemajsonbThe schema of the table.
sp_connection_nametext=, !=, ~~, ~~*, !~~, !~~*Steampipe connection name.
sp_ctxjsonbSteampipe context in JSON form.
system_tagsjsonbSystem tags for resource. System tags can be viewed by users, but can only be created by the system.
table_limitsjsonbVarious limit for the table.
tagsjsonbA map of tags for the resource.
tenant_idtext=, !=, ~~, ~~*, !~~, !~~*The OCID of the Tenant in which the resource is located.
tenant_nametextThe name of the Tenant in which the resource is located.
time_createdtimestamp with time zoneThe time the the table was created.
time_of_expirationtimestamp with time zoneIf lifecycleState is INACTIVE, indicates when this table will be automatically removed.
time_updatedtimestamp with time zoneThe time the the table was updated.
titletextTitle of the resource.

Export

This table is available as a standalone Exporter CLI. Steampipe exporters are stand-alone binaries that allow you to extract data using Steampipe plugins without a database.

You can download the tarball for your platform from the Releases page, but it is simplest to install them with the steampipe_export_installer.sh script:

/bin/sh -c "$(curl -fsSL https://steampipe.io/install/export.sh)" -- oci

You can pass the configuration to the command with the --config argument:

steampipe_export_oci --config '<your_config>' oci_nosql_table