Question: Temporary tables for several users at once.

From: Matthew Marek <mmarek_at_facstaff.wisc.edu>
Date: 1996/06/17
Message-ID: <31C62371.4312A2A5_at_facstaff.wisc.edu>#1/1

We have an Oracle 7.1 / Unix application which provides each concurrent user with "temporary tables" in the following way: There is really one permanent table into which each user can insert records as needed during certain operations. These inserts are never committed. At the end of the operation, the user's transaction is rolled back. The table never contains any records on a permanent basis, and no user ever sees the effects of another user's work.

Recently our team discovered a flaw in this methodology. The flaw is that when you insert say 100 records into a table, and if these inserts causes the table to be extended, then when the inserts are rolled back, the new table extents will not be re-allocated to hold more data in the future. Thus even though we are providing our users with "temporary tables", the table is actually growing in terms of disk usage over time. To repeat, a table which contains 0 permanent data, grows slowly over time. The sample SQL/Plus script below, TEST_INSERT.SQL, demostrates this phenomenon.

Could some of you Oracle gurus please offer me some feedback on the following questions?

1. In general what is a good solution for offering each user a temporary working area, whose extents will be returned to the tablespace after use?
2. If you have run the script below, could you offer an insight as to why a transaction that is rolled back, nonetheless permanently takes disk space away from future transactions? I know that I can use TRUNCATE to return the extents to the tablespace. Why doesn't a rollback clean up after itself?

Thanks in advance,
Matt Marek
mmarek_at_mail.state.wi.us

---

• TEST_INSERT
• Checks to see whether rollbacks de-allocate disk storage after insertions. --
• Assumes an object named 'TEST' is not currently defined.
• Assumes a tablespace named 'USERS' exists. -- set serveroutput on

create table test (

  col1 char(20),
  col2 char(20),
  col3 char(20),
  col4 char(20),
  col5 char(20),
  col6 char(20),
  col7 char(20),
  col8 char(20),
  col9 char(20),

  col10 char(20)
)
tablespace users
storage (initial 2k next 1k pctincrease 0) ;

declare
  nExtBeforeIns number ;
  nExtAfterIns number ;
  procedure test_insert is
  begin
    insert into test values (
'aaaaaaaaaaaaaaaaaaaa',
'bbbbbbbbbbbbbbbbbbbb',
'cccccccccccccccccccc',
'dddddddddddddddddddd',
'eeeeeeeeeeeeeeeeeeee',
'ffffffffffffffffffff',
'gggggggggggggggggggg',
'hhhhhhhhhhhhhhhhhhhh',
'iiiiiiiiiiiiiiiiiiii',
'jjjjjjjjjjjjjjjjjjjj'

    ) ;
  end ;

begin
  select count(*) into nExtBeforeIns
  from sys.dba_extents
  where segment_name='TEST'
  ;

  nExtAfterIns := 0 ;
  while not (nExtAfterIns > nExtBeforeIns) loop     test_insert ;

    select count(*) into nExtAfterIns
    from sys.dba_extents
    where segment_name='TEST'
    ;

  end loop ;

  rollback ;

  select count(*) into nExtAfterIns
  from sys.dba_extents
  where segment_name='TEST'
  ;

  if (nExtAfterIns > nExtBeforeIns) then     dbms_output.put_line('Problem detected -- extents were not de-allocated.') ;
  else
    dbms_output.put_line('Problem not detected -- extents were de-allocated.') ;
  end if ;

end ;

.

/

drop table test ;

---

Received on Mon Jun 17 1996 - 00:00:00 CEST