Truncating an Oracle Temp Table in a Transaction, Truncates *ALL* Temp Tables

Question

I have some C# code that creates several Oracle Temporary Tables with the "ON COMMIT DELETE ROWS" option inside of a transaction.

Inside of the transaction, I will insert a bunch of rows in to various temp tables. In some scenarios I need to Truncate a particular temp table so that I can start fresh with that table, but leave the other temp tables alone.

I found that Oracle must be doing an implicit COMMIT when you perform the Truncate, as not only was the particular temp table being Truncated, but all of my temp tables are being truncated.

Ok, I've read elsewhere that the Truncate command is considered a DDL command and that is why the commit is being processed which results in my "ON COMMIT DELETE ROWS" temp tables being cleared.

If that is true, wouldn't the act of creating a new temp table also be a DDL command and it would also trip the same commit clearing all of the other temp tables? If so, I haven't seen that behavior. I have created new temp tables in my code and found that the previously created temp tables still have their rows intact after the new temp table has been created.

Here's some C# code that demonstrates the problem (helper routines not included here):

private void RunTest()
{
  if (_oc == null)
    _oc = new OracleConnection("data source=myserver;user id=myid;password=mypassword");

  _oc.Open();
  _tran = _oc.BeginTransaction();

  string tt1 = "DMTEST1";
  AddTempTable(tt1, false);
  int TempTableRowCount0 = GetTempTableRowCount(tt1);

  AddRows(tt1, 5);
  int TempTableRowCount10 = GetTempTableRowCount(tt1);

  string tt2 = "DMTEST2";
  AddTempTable(tt2, false);
  int TempTableRowCount12 = GetTempTableRowCount(tt1); // This will have the same value as TempTableRowCount10
  AddRows(tt2, 6);
  int TempTableRowCount13 = GetTempTableRowCount(tt2); // This will have the same value as TempTableRowCount10

  string tt3 = "DMTEST3";
  AddTempTable(tt3, true); // The TRUE argument which does a TRUNCATE against the DMTEST3 table is the problem
  int TempTableRowCount14 = GetTempTableRowCount(tt1); // This will return 0, it should be = TempTableRowCount10
  int TempTableRowCount15 = GetTempTableRowCount(tt2); // This will return 0, it should be = TempTableRowCount13

  _tran.Commit();
  _tran = null;
  int TempTableRowCount20 = GetTempTableRowCount(tt1); // This should be 0 because the transaction was committed 
  int TempTableRowCount21 = GetTempTableRowCount(tt2); // and the temp tables are defined as "ON COMMIT DELETE ROWS"
}

private void AddTempTable(string TableName, bool Truncate)
{
  IDbCommand ocmd = new OracleCommand();
  ocmd.Connection = _oc;
  if (!TableExists(TableName))
  {
    ocmd.CommandText = string.Format("CREATE GLOBAL TEMPORARY TABLE {0} ({1}) ON COMMIT DELETE ROWS", TableName, "FIELD1 Float");
    int rc = ocmd.ExecuteNonQuery();
  }
  if (Truncate)
  {
    ocmd.CommandText = "TRUNCATE TABLE " + TableName;
    int rc = ocmd.ExecuteNonQuery();
  }
}

Answer 1

In Oracle, you don't create global temporary tables at runtime. You create them once when you deploy the system. Each session gets its own "copy" of the temp table automatically.

Also, if you can avoid the TRUNCATE I'd recommend it - i.e. if you can rely on the ON COMMIT DELETE ROWS which causes the data to disappear when you commit, then that's the most efficient way.

To answer your other question ("CREATE GLOBAL TEMPORARY doesn't seem to commit") - I tried it myself, and it seems to me that CREATE GLOBAL TEMPORARY does indeed commit. My test case:

create global temporary table test1 (n number) on commit delete rows;
insert into test1 values (1);
--Expected: 1
select count(*) from test1;
commit;
--Expected: 0
select count(*) from test1;
insert into test1 values (2);
--Expected: 1
select count(*) from test1;
create global temporary table test2 (n number) on commit delete rows;
--Expected: 0
select count(*) from test1;
commit;
--Expected: 0
select count(*) from test1;