Here’s a fun bit of Oracle database nuance that you may not see unless you are doing a lot of work with LOBS (Large Object Datatypes – BLOB, CLOB, NCLOB, and BFILE). Indexes of LOB datatype columns will show up in the DBA_INDEXES and USER_INDEXES view – However, if you check the ALL_INDEXES view, they are simply not listed. This may become an issue if you create all of your objects under one user, which is then locked and another user is granted privileges to select from that object (A good, secure design practice to prevent tables, views, and other objects from being modified).
Oracle does this because LOB indexes cannot be modified, therefore there is really little need to address them at all. If you try to update the LOB primary index, you’ll run into an ORA-14326, if you try to alter or drop the index, expect an ORA-22864.
Also, LOBs in Oracle 10gR2 and prior are now referred to as BasicFiles. This is because in 11g, Oracle has made some stout improvements to the handling of LOBs via the new SecureFiles. SecureFiles offer some incredible benefits over the old LOBs in terms of security and efficiency. The only thing I can’t figure out is why Oracle doesn’t seem to list this as a huge reason to upgrade!