AWR SQL_TEXT Not Available, but available from SQL Query output (Doc ID 3001115.1)

Last updated on JULY 20, 2024

Applies to:

Goal

On : 19.20.0.0.0 version, RDBMS

a.In multi-tenant environment, when generating AWR report from a PDB as below, some SQL Text in the SQL section shows "** SQL Text Not Available **"
SQL> alter session set container=mypdb1;
SQL> @?/rdbms/admin/awrrpt


1w1s80x529tsg ** SQL Text Not Available **



b.From the PDB, SQL query shows valid SQL Text
SQL> alter session set container=mypdb1;
SQL> select sql_text from v$sql where sql_id='1w1s80x529tsg';


SQL_TEXT
--------------------------------------------------------------------------------
BEGIN SCOTT.PKG1.FUNC(:P1,:P2,:RET_P3,:RET_P4); END;



c.In M000 trace file, error ORA-01502 can be seen
Filename = mysid1_m000_11111.trc

*** 2023-11-04T18:00:45.588289+08:00 (CDB$ROOT(1))
*** MODULE NAME:(MMON_SLAVE) 2023-11-04T18:00:45.588327+08:00
*** ACTION NAME:(Auto ADDM Slave Action) 2023-11-04T18:00:45.588333+08:00
*** CONTAINER ID:(1) 2023-11-04T18:00:45.588338+08:00
)
*** SQLSTR: total-len=582, dump-len=582,
SQLSTR={INSERT INTO wrh$_sqltext ...}
*** KEUUXS - encountered error: (ORA-01502: index 'SYS.WRH$_SQLTEXT_PK' or partition of such index is in unusable state

Solution

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