V$DIAG_ALERT_EXT and equality conditions – Keep Learning Keep Living

Hello,

I don’t know if this is a bug but something small took my attention. Codes below are run on 19.8 and 19.12 versions. Lately, I was working on alert log messages and to do that I use V$DIAG_ALERT_EXT view which is a row by row representation of alert log file. while I was looking for values in MESSAGE_GROUP first I got distinct values :

select distinct message_group from V$DIAG_ALERT_EXT;
MESSAGE_GROUP                                                      
-------------------------------------------------------------------
VOS
background_proc
startup
process end
process start
shutdown
sqltune

schema_ddl
create
admin_ddl

select distinct message_group from V$DIAG_ALERT_EXT;

MESSAGE_GROUP

-------------------------------------------------------------------

VOS

background_proc

startup

process end

process start

shutdown

sqltune

schema_ddl

create

admin_ddl

seems fine until now, then I wanted to list everything for message group value is “startup”

select * from V$DIAG_ALERT_EXT where message_group = 'startup';

no rows selected

select * from V$DIAG_ALERT_EXT where message_group = 'startup';

no rows selected

at first I though I type “startup” incorrectly but it was correct. then I though there might be some invisiable charanter at the end but there were none any! So I tried a “like” search:

select * from V$DIAG_ALERT_EXT where message_group like 'startup%';

MESSAGE_GROUP                                                      
-------------------------------------------------------------------
startup
startup
...

530 rows selected.

select * from V$DIAG_ALERT_EXT where message_group like 'startup%';

MESSAGE_GROUP

-------------------------------------------------------------------

startup

...

530 rows selected.

so rows are there conditions are true but result is missing with an equality search.

then I tested this on others columns like host_id, organization_id etc and same behavior! I decided to create a table using CTAS and test it on it if there is something different on the data:

create table tmp as select * from v$diag_Alert_ext where message_group = 'startup';

select message_group from tmp where message_group = 'startup';

MESSAGE_GROUP                                                      
-------------------------------------------------------------------
startup
startup
...

530 rows selected.

create table tmp as select * from v$diag_Alert_ext where message_group = 'startup';

select message_group from tmp where message_group = 'startup';

MESSAGE_GROUP

-------------------------------------------------------------------

startup

...

530 rows selected.

it worked! so there is nothing wrong with data. equality search is working on dummy table. Also if you check column data types you can see that they are VARCHAR2(67). As a last step I wanted to look at the data using DUMP function:

select message_group, dump(message_group) from tmp 
where message_group = 'startup' and rownum = 1;

MESSAGE_GROUP    DUMP(MESSAGE_GROUP)
-------------    --------------------------
startup          Typ=1 Len=7: 115,116,97,114,116,117,112



select message_group, dump(message_group) from V$DIAG_ALERT_EXT 
where message_group like 'startup%' and rownum = 1;

MESSAGE_GROUP    DUMP(MESSAGE_GROUP)
-------------    --------------------------
startup          Typ=1 Len=7: 115,116,97,114,116,117,112

select message_group, dump(message_group) from tmp

where message_group = 'startup' and rownum = 1;

MESSAGE_GROUP DUMP(MESSAGE_GROUP)

------------- --------------------------

startup Typ=1 Len=7: 115,116,97,114,116,117,112

select message_group, dump(message_group) from V$DIAG_ALERT_EXT

where message_group like 'startup%' and rownum = 1;

MESSAGE_GROUP DUMP(MESSAGE_GROUP)

------------- --------------------------

startup Typ=1 Len=7: 115,116,97,114,116,117,112

so data is exactly the same. either this is a small bug on V$DIAG_ALERT_EXT or it is a feature 🙂 I am not sure but just in case consider searching v$diag_Alert_ext with like conditions (using % sign at the end) not an equality.

wish you a healthy days.

Edit 2021-11-18 : I confirm that equality condition works properly on 19.13

Leave a Reply Cancel reply