Hidden features in Oracle

Question

I enjoyed the answers and questions about hidden features in sql server

What can you tell us about Oracle?
Hidden tables, inner workings of ..., secret stored procs, package that has good utils...

Answer 1

The OVERLAPS predicate is undocumented.

http://oraclesponge.wordpress.com/2008/06/12/the-overlaps-predicate/

Answer 2

Snapshot tables. Also found in Oracle Lite, and extremely useful for rolling your own replication mechanism.

Answer 3

Q: How to call a stored with a cursor from TOAD?

A: Example, change to your cursor, packagename and stored proc name

declare cursor PCK_UTILS.typ_cursor;  

begin   
    PCK_UTILS.spc_get_encodedstring(  
        'U',  
        10000002,  
        null,  
        'none',  
        cursor);  
end;

Answer 4

The Model Clause (available for Oracle 10g and up)

Answer 5

If you get the value of PASSWORD column on DBA_USERS you can backup/restore passwords without knowing them:

 ALTER USER xxx IDENTIFIED BY VALUES 'xxxx';

Answer 6

Frequent rebuilding of indexes is almost always a waste of time.