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 :
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
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”
|
1 2 3 |
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:
|
1 2 3 4 5 6 7 8 9 |
select * from V$DIAG_ALERT_EXT where message_group like 'startup%'; MESSAGE_GROUP ------------------------------------------------------------------- startup 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:
|
1 2 3 4 5 6 7 8 9 10 11 |
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. |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
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