Searching for unknown characters in an Oracle database

Question

Is there a way to search an Oracle database (some sort of regex I suspect) to find unknown characters (which often appear as □ □)?

Answer 1

There is no standard way to search over entire Oracle database. You would need a tedious script that walks over various types of Oracle objects in dba_objects, and then descends into each (for a trivial example if an object is a table you need to parse the columns, and if a column contains a character data, REGEXP_LIKE; but there are more types of objects, for example a package - do you want to search package's literals too?). I would instead make manually an explicit list of queries over tables and columns.

Answer 2

Try something like this:

select co11, ...
from tab1
where col1 like '%'||chr(9)||'%'  -- ascii code for tab
   or col1 like '%'||chr(20)||'%' -- ascii code for newline
--...
;