Database Administration (MOSC)

MOSC Banner

ORA-01031 on execute immediate ALTER SESSION SET CONTAINER

edited May 2, 2024 10:15AM in Database Administration (MOSC) 4 commentsAnswered ✓

Hi

Connected with sqlplus SYS as sysdba, I would like to loop on all opened PDBs to execute some task with a plsql loop:

$ORACLE_HOME/bin/sqlplus '/ as sysdba' <<EOF
BEGIN
FOR container IN (SELECT CON_ID , NAME FROM V\$CONTAINERS WHERE CON_ID > 2 and OPEN_MODE='READ WRITE')
LOOP
EXECUTE IMMEDIATE 'ALTER SESSION SET CONTAINER = ' || container.NAME;
-- execute some task
END LOOP;
END;
/
EOF

But I get an error

ERREUR a la ligne 1 :
ORA-01031: privileges insuffisants

And indeed I just discovered that I'm unable to manually plSQL execute immediate an 'alter session set container' once I'm in the first PDB to swith to next opened PDB, while of course I can change container with SQL "alter session set container" as I'm sys as sysdba…

Tagged:

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center