OracleMultitenant

Pluggable databases and their state

Every PDB with its open mode, whether it's restricted, when it opened and its size. RESTRICTED = YES usually means a plug-in violation.

Not yet verified. How scripts are tested

1CLEAR COLUMNS2SET LINESIZE 120 PAGESIZE 100 TRIMOUT ON TAB OFF3COLUMN con_id     HEADING "CON" FORMAT 9994COLUMN name       HEADING "PDB" FORMAT A255COLUMN open_mode  HEADING "OPEN MODE"  FORMAT A126COLUMN restricted HEADING "RESTRICTED" FORMAT A107COLUMN opened     FORMAT A168COLUMN size_gb    HEADING "SIZE GB"    FORMAT 99,990.99 10SELECT con_id, name, open_mode, restricted,11       TO_CHAR(open_time, 'YYYY-MM-DD HH24:MI') AS opened,12       ROUND(total_size / POWER(1024, 3), 1) AS size_gb13FROM v$pdbs14ORDER BY con_id;

Save it as ora-pdbs.sql and run it with SQL> @ora-pdbs.

Open in denrepoRelated tool: Connection string builder

Helps with

• ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
• ORA-65011: Pluggable database %s does not exist.
• ORA-65096: invalid common user or role name

Part of these runbooks

• Users can't connect

More Oracle scripts: Multitenant

• Tablespace usage across all PDBsThe tablespace report for every container at once, fullest first, so you don't have to switch into each PDB.
• Unresolved plug-in violationsWhy a PDB opened in restricted mode: patch mismatches, parameter conflicts and missing options, with Oracle's suggested fix in the message.
• Open PDBs and keep them open after restartOpens every PDB, then saves the state so they reopen automatically when the CDB restarts. The first query shows what's currently saved.