AWR SQL_TEXT Not Available, but available from SQL Query output
(Doc ID 3001115.1)
Last updated on JULY 20, 2024
Applies to:
Oracle Database - Enterprise Edition - Version 19.20.0.0.0 and laterInformation in this document applies to any platform.
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
To view full details, sign in with your My Oracle Support account. |
|
Don't have a My Oracle Support account? Click to get started! |
In this Document
Goal |
Solution |
References |