steampipe plugin install oci

Table: oci_database_autonomous_database - Query OCI Database Autonomous Databases using SQL

The Autonomous Database is a feature of the Oracle Cloud Infrastructure Database service. It automates management tasks such as tuning, patching, backups and more, allowing developers to focus on higher-value tasks. The Autonomous Database supports a variety of workloads, including OLTP, data warehousing, and JSON document store.

Table Usage Guide

The oci_database_autonomous_database table provides insights into Autonomous Databases within Oracle Cloud Infrastructure Database service. As a database administrator or developer, explore specific details about these databases, including their configurations, statuses, and associated metadata. Utilize this table to manage and monitor your databases, ensuring they are optimized, secure, and functioning as expected.

Examples

Basic info

Gain insights into the lifecycle state and creation time of your autonomous databases to better understand their status and duration of existence. This is particularly useful for database management and auditing purposes.

select
db_name,
display_name,
lifecycle_state,
time_created
from
oci_database_autonomous_database;
select
db_name,
display_name,
lifecycle_state,
time_created
from
oci_database_autonomous_database;

List databases that are not available

Discover the databases that are currently not available. This can be useful to identify potential issues or disruptions in your database services.

select
db_name,
display_name,
lifecycle_state,
time_created
from
oci_database_autonomous_database
where
lifecycle_state <> 'AVAILABLE';
select
db_name,
display_name,
lifecycle_state,
time_created
from
oci_database_autonomous_database
where
lifecycle_state <> 'AVAILABLE';

List databases with a data storage size greater than 1024 GB

Discover the databases that have a storage size exceeding 1024 GB. This query is useful to monitor and manage your database storage, aiding in efficient resource allocation and preventing potential storage shortages.

select
db_name,
display_name,
lifecycle_state,
time_created
from
oci_database_autonomous_database
where
data_storage_size_in_gbs > 1024;
select
db_name,
display_name,
lifecycle_state,
time_created
from
oci_database_autonomous_database
where
data_storage_size_in_gbs > 1024;

Get KMS key details for the databases

Discover the encryption key details for your databases to gain insights into their security measures. This can be particularly useful for assessing the strength of your data protection and identifying areas for potential improvement.

select
d.db_name,
d.display_name,
d.kms_key_id,
k.name as key_name,
k.algorithm as key_algorithm,
k.current_key_version,
k.protection_mode
from
oci_database_autonomous_database as d,
oci_kms_key as k
where
k.id = d.kms_key_id;
select
d.db_name,
d.display_name,
d.kms_key_id,
k.name as key_name,
k.algorithm as key_algorithm,
k.current_key_version,
k.protection_mode
from
oci_database_autonomous_database as d,
oci_kms_key as k
where
k.id = d.kms_key_id;

Get KMS vault details for the databases

Determine the areas in which your databases are connected to specific KMS vaults. This can help you understand the security measures in place for your data, as well as identify potential areas for improvement or optimization.

select
d.db_name,
d.display_name,
d.vault_id,
v.display_name as vault_display_name,
v.crypto_endpoint,
v.vault_type,
v.management_endpoint
from
oci_database_autonomous_database as d,
oci_kms_vault as v
where
v.id = d.vault_id;
select
d.db_name,
d.display_name,
d.vault_id,
v.display_name as vault_display_name,
v.crypto_endpoint,
v.vault_type,
v.management_endpoint
from
oci_database_autonomous_database as d,
oci_kms_vault as v
where
v.id = d.vault_id;

Schema for oci_database_autonomous_database

NameTypeOperatorsDescription
_ctxjsonbSteampipe context in JSON form.
apex_detailsjsonbInformation about Oracle APEX Application Development.
are_primary_whitelisted_ips_usedbooleanThis field will be null if the Autonomous Database is not Data Guard enabled or Access Control is disabled.
autonomous_container_database_idtext=The Autonomous Container Database OCID.
available_upgrade_versionsjsonbList of Oracle Database versions available for a database upgrade. If there are no version upgrades available, this list is empty.
backup_configjsonbAutonomous Database configuration details for storing manual backups in the Object Storage service.
compartment_idtext=The OCID of the compartment in Tenant in which the resource is located.
connection_stringsjsonbThe connection string used to connect to the Autonomous Database. The username for the Service Console is ADMIN. Use the password you entered when creating the Autonomous Database for the password value.
connection_urlsjsonbThe URLs for accessing Oracle Application Express (APEX) and SQL Developer Web with a browser from a Compute instance within your VCN or that has a direct connection to your VCN. Note that these URLs are provided by the console only for databases on dedicated Exadata infrastructure.
cpu_core_countbigintThe number of OCPU cores to be made available to the database.
data_safe_statustextStatus of the Data Safe registration for this Autonomous Database.
data_storage_size_in_gbsbigintThe quantity of data in the database, in gigabytes.
data_storage_size_in_tbsbigintThe quantity of data in the database, in terabytes.
db_nametextThe database name.
db_versiontext=A valid Oracle Database version for Autonomous Database.
db_workloadtext=The Autonomous Database workload type.
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.
display_nametext=The user-friendly name for the Autonomous Database. The name does not have to be unique.
failed_data_recovery_in_secondsbigintIndicates the number of seconds of data loss for a Data Guard failover.
freeform_tagsjsonbFree-form tags for resource. This tags can be applied by any user with permissions on the resource.
idtext=The OCID of the Autonomous Database.
infrastructure_typetext=The infrastructure type this resource belongs to.
is_access_control_enabledbooleanIndicates if the database-level access control is enabled.
is_auto_scaling_enabledbooleanIndicates if auto scaling is enabled for the Autonomous Database CPU core count.
is_data_guard_enabledboolean!=, =Indicates whether the Autonomous Database has Data Guard enabled.
is_dedicatedbooleanTrue if the database uses dedicated Exadata infrastructure.
is_free_tierboolean!=, =Indicates if this is an Always Free resource. The default value is false.
is_previewbooleanIndicates if the Autonomous Database version is a preview version.
is_refreshable_clonebooleanIndicates whether the Autonomous Database is a refreshable clone.
key_store_idtextThe OCID of the key store.
key_store_wallet_nametextThe wallet name for Oracle Key Vault.
kms_key_idtextThe OCID of the key container that is used as the master encryption key in database transparent data encryption (TDE) operations.
kms_key_version_idtextThe OCID of the key container version that is used in database transparent data encryption (TDE) operations KMS Key can have multiple key versions.
license_modeltextThe Oracle license model that applies to the Oracle Autonomous Database.
lifecycle_detailstextInformation about the current lifecycle state.
lifecycle_statetext=The current state of the Autonomous Database.
nsg_idsjsonbA list of the OCIDs of the network security groups (NSGs) that this resource belongs to.
open_modetextThe `DATABASE OPEN` mode. You can open the database in `READ_ONLY` or `READ_WRITE` mode.
operations_insights_statustextStatus of Operations Insights for this Autonomous Database.
permission_leveltextThe Autonomous Database permission level.
private_endpointtextThe private endpoint for the resource.
private_endpoint_iptextThe private endpoint Ip address for the resource.
private_endpoint_labeltextThe private endpoint label for the resource.
refreshable_modetextThe refresh mode of the clone. AUTOMATIC indicates that the clone is automatically being refreshed with data from the source Autonomous Database.
refreshable_statustextThe refresh status of the clone. REFRESHING indicates that the clone is currently being refreshed with data from the source Autonomous Database.
regiontextThe OCI region in which the resource is located.
roletextThe role of the Autonomous Data Guard-enabled Autonomous Container Database.
service_console_urltextThe URL of the Service Console for the Autonomous Database.
source_idtextThe OCID of the source Autonomous Database that was cloned to create the current Autonomous Database.
sp_connection_nametext=, !=, ~~, ~~*, !~~, !~~*Steampipe connection name.
sp_ctxjsonbSteampipe context in JSON form.
standby_dbjsonbAutonomous Data Guard standby database details.
standby_whitelisted_ipsjsonbThe client IP access control list (ACL). This feature is available for autonomous databases on shared Exadata infrastructure and on Exadata Cloud@Customer. Only clients connecting from an IP address included in the ACL may access the Autonomous Database instance. For shared Exadata infrastructure, this is an array of CIDR (Classless Inter-Domain Routing) notations for a subnet or VCN OCID.
subnet_idtextThe OCID of the subnet the resource is associated with.
system_tagsjsonbSystem tags for resource. System tags can be viewed by users, but can only be created by the system.
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 date and time the Autonomous Database was created.
time_deletion_of_free_autonomous_databasetimestamp with time zoneThe date and time the Always Free database will be automatically deleted because of inactivity. If the database is in the STOPPED state and without activity until this time, it will be deleted.
time_maintenance_begintimestamp with time zoneThe date and time when maintenance will begin.
time_maintenance_endtimestamp with time zoneThe date and time when maintenance will end.
time_of_last_failovertimestamp with time zoneThe timestamp of the last failover operation.
time_of_last_refreshtimestamp with time zoneThe date and time when last refresh happened.
time_of_last_refresh_pointtimestamp with time zoneThe refresh point timestamp (UTC). The refresh point is the time to which the database was most recently refreshed. Data created after the refresh point is not included in the refresh.
time_of_last_switchovertimestamp with time zoneThe timestamp of the last switchover operation for the Autonomous Database.
time_of_next_refreshtimestamp with time zoneThe date and time of next refresh.
time_reclamation_of_free_autonomous_databasetimestamp with time zoneThe date and time the Always Free database will be stopped because of inactivity. If this time is reached without any database activity, the database will automatically be put into the STOPPED state.
titletextTitle of the resource.
used_data_storage_size_in_tbsbigintThe amount of storage that has been used, in terabytes.
vault_idtextThe OCID of the Oracle Cloud Infrastructure vault.
whitelisted_ipsjsonbThe client IP access control list (ACL).

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_autonomous_database