steampipe plugin install ocisteampipe plugin install oci

Table: oci_database_db_system - Query OCI Database Service DB Systems using SQL

DB Systems in OCI Database Service provide a fully managed database service that lets you create, manage, and scale Oracle Database instances in the cloud. It is designed to support any size workload from small dev/test to large production deployments. The service provides automated backup, recovery, and patching while providing high availability and security.

Table Usage Guide

The oci_database_db_system table provides insights into DB Systems within the Oracle Cloud Infrastructure Database Service. As a database administrator or DevOps engineer, you can explore DB System-specific details through this table, including its configuration, status, and associated resources. Utilize this table to manage and monitor your OCI Database Service resources effectively, such as identifying DB Systems that require patching or understanding the distribution of DB Systems across different compartments.

Examples

Basic info

Explore which lifecycle states your Oracle Cloud Infrastructure databases are in and when they were created. This can help you manage your databases by identifying those that are active, inactive, or in transition.

select
  id,
  display_name,
  lifecycle_state,
  time_created
from
  oci_database_db_system;

select
  id,
  display_name,
  lifecycle_state,
  time_created
from
  oci_database_db_system;

List db systems that are not available

Explore which database systems are not currently available. This can be particularly useful in identifying potential issues or disruptions in your database environment.

select
  id,
  display_name,
  lifecycle_state,
  time_created
from
  oci_database_db_system
where
  lifecycle_state <> 'AVAILABLE';

select
  id,
  display_name,
  lifecycle_state,
  time_created
from
  oci_database_db_system
where
  lifecycle_state <> 'AVAILABLE';

List db systems with data storage size greater than 1024 GB

Explore which database systems have a data storage size exceeding 1024 GB to determine if there's a need for storage optimization or capacity planning. This can be beneficial in managing resources and preventing potential system overloads.

select
  id,
  display_name,
  lifecycle_state,
  time_created
from
  oci_database_db_system
where
  data_storage_size_in_gbs > 1024;

select
  id,
  display_name,
  lifecycle_state,
  time_created
from
  oci_database_db_system
where
  data_storage_size_in_gbs > 1024;

Schema for oci_database_db_system

Name	Type	Operators	Description
_ctx	jsonb		Steampipe context in JSON form, e.g. connection_name.
availability_domain	text	=	The name of the availability domain that the DB system is located in.
backup_network_nsg_ids	jsonb		A list of the OCIDs of the network security groups (NSGs) that the backup network of this DB system belongs to.
backup_subnet_id	text		The OCID of the backup network subnet the DB system is associated with.
cluster_name	text		The cluster name for exadata and 2-node RAC virtual machine DB systems.
compartment_id	text	=	The OCID of the compartment in Tenant in which the resource is located.
cpu_core_count	bigint		The number of CPU cores enabled on the DB system.
data_storage_percentage	bigint		The percentage assigned to data storage.
data_storage_size_in_gbs	bigint		The data storage size, in gigabytes, that is currently available to the DB system.
database_edition	text		The oracle database edition that applies to all the databases on the DB system.
db_system_options_storage_management	text		The storage option used in DB system.
defined_tags	jsonb		Defined 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.
disk_redundancy	text		The type of redundancy configured for the DB system.
display_name	text	=	The user-friendly name for the DB system. The name does not have to be unique.
domain	text		The domain name for the DB system.
fault_domains	jsonb		List of the fault domains in which this DB system is provisioned.
freeform_tags	jsonb		Free-form tags for resource. This tags can be applied by any user with permissions on the resource.
host_name	text		The hostname for the DB system.
id	text	=	The OCID of the DB system.
iorm_config_cache	jsonb		The IORM configuration of the DB system.
kms_key_id	text		The OCID of the key container that is used as the master encryption key in database transparent data encryption (TDE) operations.
last_maintenance_run_id	text		The OCID of the last maintenance run.
last_patch_history_entry_id	text		The OCID of the last patch history.
license_model	text		The oracle license model that applies to all the databases on the DB system.
lifecycle_details	text		Additional information about the current lifecycle state.
lifecycle_state	text	=	The current state of the DB system.
listener_port	bigint		The port number configured for the listener on the DB system.
maintenance_window	jsonb		The maintenance window of the DB system.
next_maintenance_run_id	text		The OCID of the next maintenance run.
node_count	bigint		The number of nodes in the DB system.
nsg_ids	jsonb		A list of the OCIDs of the network security groups (NSGs) that this resource belongs to.
point_in_time_data_disk_clone_timestamp	timestamp with time zone		The point in time for a cloned database system when the data disks were cloned from the source database system.
reco_storage_size_in_gb	bigint		The RECO/REDO storage size, in gigabytes, that is currently allocated to the DB system.
region	text		The OCI region in which the resource is located.
scan_dns_name	text		The FQDN of the DNS record for the SCAN IP addresses that are associated with the DB system.
scan_dns_record_id	text		The OCID of the DNS record for the SCAN IP addresses that are associated with the DB system.
scan_ip_ids	jsonb		The OCID of the single client access name (SCAN) IP addresses associated with the DB system.
source_db_system_id	text		The OCID of the DB system from where the DB system is created.
sparse_diskgroup	boolean		True, If sparse diskgroup is configured for exadata DB system.
ssh_public_keys	jsonb		The public key portion of one or more key pairs used for SSH access to the DB system.
subnet_id	text		The OCID of the subnet the DB system is associated with.
tags	jsonb		A map of tags for the resource.
tenant_id	text		The OCID of the Tenant in which the resource is located.
tenant_name	text		The name of the Tenant in which the resource is located.
time_created	timestamp with time zone		The date and time the DB system was created.
time_zone	text		The time zone of the DB system.
title	text		Title of the resource.
version	text		The oracle database version of the DB system.
vip_ids	jsonb		A list of the OCIDs of the virtual IP (VIP) addresses associated with the DB system.
zone_id	text		The OCID of the zone the DB system is associated with.

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_database_db_system