Wednesday, October 6, 2021

Oracle Database 19c -- How to run a script for each PDB


With a CDB database, it might be required to run a script for each PDB. 

There is the server side $ORACLE_HOME/rdbms/admin/ script which can be used to run scripts and SQL statements within a CDB.

There are cases when access to the database host is limited, and using DBMS_SQL package for switching between PDBs is impractical


Use the "FOR EACH" extension for SQLPlus provided at Oracle SQL*Plus -- Implementation of "FOR EACH" contruct

Example of how to run a script for each PDB:

col pdb_list new_val pdb_list
select listagg(name, ',') within group (order by name) pdb_list from v$pdbs where open_mode='READ WRITE';

@@for_each "&pdb_list" "<script.sql>"


No comments: