Re: Any way to disable UNDO (rollback) with temporary tables?

From: Daniel Morgan <damorgan_at_x.washington.edu>
Date: Thu, 04 Dec 2003 09:28:38 -0800
Message-ID: <1070558953.228917@yasure>

Howard J. Rogers wrote:

> I haven't tried this... but can you flashback on a temporary table?
>
> If so, the use for undo becomes obvious.
>
> Regards
> HJR
>

Depends on the type of global temporary table. Consider the following:

SQL> CREATE GLOBAL TEMPORARY TABLE mvcc_test AS

   2 SELECT * FROM all_objects
   3 WHERE 1=2; Table created.

SQL> INSERT INTO mvcc_test

   2 SELECT * FROM all_objects;

6465 rows created.

SQL> SELECT COUNT(*)
   2 FROM all_objects;

   COUNT(*)

---

       6465

SQL> variable x REFCURSOR
SQL> BEGIN

   2     OPEN :x FOR
   3     SELECT * FROM mvcc_test;

   4 END;
   5 /

PL/SQL procedure successfully completed.

SQL> DELETE FROM mvcc_test;

6465 rows deleted.

SQL> COMMIT; Commit complete.

SQL> SELECT COUNT(*)
   2 FROM mvcc_test;

   COUNT(*)

---

          0

SQL> print x
ERROR:
ORA-08103: object no longer exists

no rows selected

SQL> But recreating the table as follows:

SQL> CREATE GLOBAL TEMPORARY TABLE mvcc_test

   2 ON COMMIT PRESEVER ROWS AS
   3 SELECT * FROM all_objects
   4 WHERE 1=2; with on commit preserve rows the cursor can retrieve the rows from UNDO.

As sympathetic as I am to the OP's problem the reality is that the concerns are misplaced. There is no way the amount of UNDO or REDO related to a GTT loaded with the append hint is the cause of any problem worthy of all of this. If there is a problem ... it is to be found somewhere else.

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)

Received on Thu Dec 04 2003 - 11:28:38 CST