When hash indexes should be used in Oracle?
You'd use a hash value as an identifier for rows of data whenever that's "practical", i.e. you actually need a simple identifier for all your rows, there are no candidate (natural) key fields that are practical (too wide for example), and an ordinary (sequential for instance) generated identifier doesn't cut it (for instance you need that row identifier to be "global" - if the same row was created in two distinct databases, they should have the same identifier).

(One non-trivial example of such a thing would be Git. Each object stored in a git repository is uniquely identified by a SHA-1 hash, which is pretty handy to refer unambiguously to a given commit.)

If you need to use something like that, indeed you'll need to create it yourself with Oracle (adding that hash as a column to the table and indexing it, or with a function-based index).

You could go even further by creating your very own index type with application domain indexes if a plain hash isn't good enough &ndash; full-text indexing is, I believe, implemented this way.

There is something built-in to Oracle that is hash-based and doesn't actually need an index to get fast row retrieval: hash clusters. You can theoretically retrieve the target row with as little as a single-block I/O (which normal index+table lookups can't match, and even IOTs can't match unless the table is really small). Do read the When to Use Hash Clusters docs though, they are quite peculiar, and you need a good key (one or more columns) in the first place to use them.