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

Name	Type	Operators	Description
_ctx	jsonb		Steampipe context in JSON form.
compartment_id	text	=	The OCID of the compartment in Tenant in which the resource is located.
connection_strings	jsonb		The connection strings used to connect to the oracle pluggable database.
container_database_id	text		The OCID of the CDB.
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.
freeform_tags	jsonb		Free-form tags for resource. This tags can be applied by any user with permissions on the resource.
id	text	=	The OCID of the pluggable database.
is_restricted	boolean		The 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_details	text		Detailed message for the lifecycle state.
lifecycle_state	text	=	The current state of the pluggable database.
open_mode	text		The mode that pluggableDatabase is in. Open mode can only be changed to READ_ONLY or MIGRATE directly from the backend.
pdb_name	text	=	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.
region	text		The OCI region in which the resource is located.
sp_connection_name	text	=, !=, ~~, ~~, !~~, !~~	Steampipe connection name.
sp_ctx	jsonb		Steampipe context in JSON form.
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 pluggable database was created.
title	text		Title 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