steampipe plugin install oci

Table: oci_database_pluggable_database - Query OCI Database Pluggable Databases using SQL

A Pluggable Database (PDB) is a portable collection of schemas, schema objects, and non-schema objects that appears to an Oracle Net client as a non-CDB. All Oracle databases before Oracle Database 12c were non-CDBs. The data and code of a PDB are physically stored in common tablespaces, which are owned by the CDB root.

Table Usage Guide

The oci_database_pluggable_database table provides insights into Pluggable Databases within Oracle Cloud Infrastructure Database service. As a database administrator, you can explore specific details about each PDB through this table, including its configuration, status, and associated metadata. Utilize it to uncover information about PDBs, such as their storage size, the number of users, and the version of the Oracle Database software.

Examples

Basic info

Explore which databases are in different stages of their lifecycle and when they were created. This allows you to assess the overall health and status of your databases.

select
pdb_name,
id,
lifecycle_state,
time_created
from
oci_database_pluggable_database;
select
pdb_name,
id,
lifecycle_state,
time_created
from
oci_database_pluggable_database;

List failed pluggable databases

This query can be used to identify any pluggable databases in the Oracle Cloud Infrastructure that have failed to initialize. By doing so, it allows users to quickly pinpoint and address any issues that may be disrupting their database operations.

select
pdb_name,
id,
lifecycle_state,
time_created
from
oci_database_pluggable_database
where
lifecycle_state = 'FAILED';
select
pdb_name,
id,
lifecycle_state,
time_created
from
oci_database_pluggable_database
where
lifecycle_state = 'FAILED';

List pluggable databases older than 90 days

Discover the segments that consist of pluggable databases older than 90 days. This is useful for identifying potential areas for system optimization or data archiving.

select
pdb_name,
id,
lifecycle_state,
time_created
from
oci_database_pluggable_database
where
time_created <= (current_date - interval '90' day)
order by
time_created;
select
pdb_name,
id,
lifecycle_state,
time_created
from
oci_database_pluggable_database
where
time_created <= date('now', '-90 day')
order by
time_created;

List unrestricted pluggable databases

Explore which pluggable databases are unrestricted in your Oracle Cloud Infrastructure. This can be useful to identify potential security vulnerabilities or for general database management purposes.

select
pdb_name,
id,
lifecycle_state,
is_restricted
from
oci_database_pluggable_database
where
not is_restricted;
select
pdb_name,
id,
lifecycle_state,
is_restricted
from
oci_database_pluggable_database
where
is_restricted = 0;

Schema for oci_database_pluggable_database

NameTypeOperatorsDescription
_ctxjsonbSteampipe context in JSON form.
compartment_idtext=The OCID of the compartment in Tenant in which the resource is located.
connection_stringsjsonbThe connection strings used to connect to the oracle pluggable database.
container_database_idtextThe OCID of the CDB.
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=The OCID of the pluggable database.
is_restrictedbooleanThe restricted mode of pluggableDatabase. If a pluggableDatabase is opened in restricted mode, the user needs both Create a session and restricted session privileges to connect to it.
lifecycle_detailstextDetailed message for the lifecycle state.
lifecycle_statetext=The current state of the pluggable database.
open_modetextThe mode that pluggableDatabase is in. Open mode can only be changed to READ_ONLY or MIGRATE directly from the backend.
pdb_nametext=The name for the pluggable database. The name is unique in the context of a Database. The name must begin with an alphabetic character and can contain a maximum of thirty alphanumeric characters. Special characters are not permitted. The pluggable database name should not be same as the container database name.
regiontextThe OCI region in which the resource is located.
sp_connection_nametext=, !=, ~~, ~~*, !~~, !~~*Steampipe connection name.
sp_ctxjsonbSteampipe context in JSON form.
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 pluggable database was created.
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_database_pluggable_database