Accessing Table/View from PL/SQL Raises ORA-942 After Container Is Switched by the Same PL/SQL Code (Doc ID 2775107.1)

Last updated on JULY 20, 2024

Applies to:

Symptoms

The "V$" views or any other table/view owned by a custom schema cannot be referenced inside PL/SQL code when the PDB is changed by executing 'alter session set container' command from the same PL/SQL code. However, the same table or view can be accessed from PL/SQL when the PDB is changed using a different PL/SQL program or the PDB is changed manually outside the PL/SQL code. The issue can be demonstrated using the following test case.

The issue is not only restricted to SYS owned dictionary tables/views, the "ORA-00942" can be seen with any table/view owned by the schema('C##TEST') itself or any other custom schema.

Changes

It's newly installed or upgraded multi-tenant database.

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.