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

More undocumented stuff at http://awads.net/wp/tag/undocumented/

Warning: Use at your own risk.

Answer 2

Not a hidden feature, but Finegrained-access-control (FGAC), also known as row-level security, is something I have used in the past and was impressed with the efficiency of its implementation. If you are looking for something that guarantees you can control the granularity of how rows are exposed to users with differing permissions - regardless of the application that is used to view data (SQL*Plus as well as your web app) - then this a gem.

The built-in fulltext indexing is more widely documented, but still stands out because of its stability (just try running a full-reindexing of fulltext-indexed columns on similar data samples on MS-SQL and Oracle and you'll see the speed difference).

Answer 3

The Buffer Cache Hit Ratio is virtually meaningless as a predictor of system efficiency

Answer 4

Bypass the buffer cache and read straight from disk using direct path reads.

alter session set "_serial_direct_read"=true;

Causes a tablespace (9i) or fast object (10g+) checkpoint, so careful on busy OLTP systems.

Answer 5

You can view table data as of a previous time using Flashback Query, with certain limitations.

Select *
  from my_table as of timestamp(timestamp '2008-12-01 15:21:13')

11g has a whole new feature set around preserving historical changes more robustly.

Answer 6

I just found out about the pseudo-column Ora_rowSCN. If you don't set your table up for this, this pcolumn gives you the block SCN. This could be really useful for the emergency, "Oh crap I have no auditing on this table and wonder if someone has changed the data since yesterday."

But even better is if you create the table with Rowdependecies ON. That puts the SCN of the last change on every row. This will help you avoid a "Lost Edit" problem without having to include every column in your query.

IOW, when you app grabs a row for user modification, also select the Ora_rowscn. Then when you post the user's edits, include Ora_rowscn = v_rscn in addition to the unique key in the where clause. If someone has touched the row since you grabbed it, aka lost edit, the update will match zero rows since the ora_rowscn will have changed.

So cool.