Wrong Query Results

[TerryD]

ok, I know it's monday, but I'm sure I'm awake...!

Just wondered why I'm getting this result:

1 select table_name, cache from dba_tables
2* where cache != 'N'
SQL> /

TABLE_NAME CACHE
------------------------------ -----
CDEF$ N
TAB$ N
IND$ N
CLU$ N
USER$ N

etc. for all tables.

but:

1 select table_name, cache from dba_tables
2* where cache = 'N'
SQL> /

no rows selected

I have no tables cached - I know that, so I guess I must've made the most basic error in my query?!

[marist89]

Seems like the cache column has has padded values:

select table_name, cache from dba_tables
where rtrim(ltrim(cache)) != 'N'

I found this by:
select table_name, '|' || cache || '|' cache
from dba_tables
where rtrim(ltrim(cache)) != 'N'

[aracnid]

take a look at dba_tables, for cache:
cache varchar2(5)
-----
N

is the last value, so you have to put '%N'

[TerryD]

Thanks,

knew there'd been a simple explanation - For some reason I was convinced that I'd run the same query last week with 'N' - but guess I obviously didn't!

Terry.

[DBAtrix]

whenever i get a result that makes me think i'm losing my mind, i concatenate some beginning and ending characters. it has helped me out many a time.

select table_name, '[' || cache || ']' from dba_tables ;

D.