Re: UNDO Tablespace, and how NOT to use

From: DA Morgan <damorgan_at_psoug.org>
Date: Mon, 06 Aug 2007 12:00:25 -0700
Message-ID: <1186426824.554481@bubbleator.drizzle.com>

joel garry wrote:

> so for Daniel to say something should never ever be
> done in Oracle and to blast old stuff in SQL server is actually
> laudable and timely.

And for anyone that doesn't believe me here's a simple lab you can run to prove it to yourself. I run it every year for my students at the university and I make them read it line by line.

CREATE TABLE test (
onecol NUMBER(1));

ALTER SESSION SET tracefile_identifier = 'drop_table';

ALTER SESSION SET EVENTS '10046 trace name context forever, level 12';

DROP TABLE test;

ALTER SESSION SET EVENTS '10046 trace name context OFF';

EXIT Now go to bdump/udump (while they still exit in your version of Oracle) and find the trace file. Run it through TKPROF. Here's what it looks like on my machine after copying the file to c:\temp.

c:\temp> tkprof c:\temp\orabase_ora_2736_drop_table.trc c:\temp\trace_output.txt

Enjoy a very long read.

Here's the summary from 11g

---

Trace file: c:\temp\orabase_ora_2736_drop_table.trc Trace file compatibility: 10.01.00
Sort options: default

        1  session in tracefile.
       62  user  SQL statements in trace file.
      288  internal SQL statements in trace file.
      350  SQL statements in trace file.
      107  unique SQL statements in trace file.
     7932  lines in trace file.
        4  elapsed seconds in trace file.

And that is with a single column, no synonyms, no indexes, no constraints, no dependent materialized views, no views, no triggers, and without any complicating technology such as replication.

If this looks like a good idea to anyone see your family physician.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

Received on Mon Aug 06 2007 - 14:00:25 CDT