Tuesday, November 15, 2011

Global Temporary Tables (Part 2)

You can use a global temporary table (GTT) simultaneously in multiple sessions. Each of these sessions can delete, insert, select and update data in the GTT but other sessions cannot see that data. To put it in other words, each session can only see its own data. This example demonstrates this and also looks more closely at ORA-14452. It uses two sessions: session 1 (in red) and session 2 (in blue) which run concurrently. First create a GTT, which retains data after a commit statement, in session 1:

SQL> prompt Starting session 1
Starting session 1
SQL> conn /
Connected.
SQL> show user
USER is "ORACLE"
SQL> create global temporary table gtt1
  2  (session_name varchar2(12))
  3  on commit preserve rows
  4  /

Table created.
 SQL>

Add a single row to the GTT, do a commit then check that the row is still there:

SQL> insert into gtt1 values('Session 1')
  2  /

1 row created.

SQL> commit;

Commit complete.

SQL> select * from gtt1
  2  /

SESSION_NAME
------------
Session 1

SQL>

Check the GTT's object_id for future reference reference:

SQL> select object_id  from dba_objects
  2  where object_name = 'GTT1'
  3  /

 OBJECT_ID
----------
    235348

SQL> accept stop prompt 'Go to session 2'
Go to session 2

Now go to session 2 as instructed by the SQL prompt. Check that you can see the GTT and that it has the same object_id:

SQL> prompt Starting session 2
Starting session 2
SQL> conn /
Connected.
SQL> show user
USER is "ORACLE"
SQL> desc gtt1
Name                       Null?    Type
-------------------------- -------- ------------------
SESSION_NAME                        VARCHAR2(12)

SQL> select object_id from dba_objects
  2  where object_name = 'GTT1'
  3  /

 OBJECT_ID
----------
    235348

SQL>

Confirm that you cannot see the data from session 1, insert a row for session 2 and check that you can see it:

SQL> select * from gtt1
  2  /

no rows selected

SQL> insert into gtt1 values('Session 2')
  2  /

1 row created.

SQL> select * from gtt1
  2  /

SESSION_NAME
------------
Session 2

SQL>

Truncate the GTT. Check that the row belonging to session 2 has gone. We will check later to see that the row from session 1 has not been affected:

SQL> truncate table gtt1
  2  /

Table truncated.

SQL> select * from gtt1
  2  /

no rows selected

SQL>

Try to drop the table. This fails as session 1 is still using it:
  
SQL> drop table gtt1
  2  /
drop table gtt1
          *
ERROR at line 1:
ORA-14452: attempt to create, alter or drop an index
on temporary table already in use

SQL> prompt Go back to session 1
Go back to session 1

Now go back to session 1 as instructed. Check that the truncate, which session 2 did, has not removed the row for session 1:

SQL> prompt Resuming session 1
Resuming session 1
SQL> select * from gtt1
  2  /

SESSION_NAME
------------
Session 1

SQL>

Try to drop the GTT again as session 1 is the only one using it:

SQL> drop table gtt1
  2  /
drop table gtt1
          *
ERROR at line 1:
ORA-14452: attempt to create, alter or drop an index
on temporary table already in use

SQL>

That failed so look up the error message at the UNIX prompt (sorry the font is so small):

TEST9 > oerr ora 14452
14452, 00000, "attempt to create, alter or drop an index on temporary table already in use"
// *Cause:  An attempt was made to create, alter or drop an index on temporary
//          table which is already in use.
// *Action: All the sessions using the session-specific temporary table have
//          to truncate table and all the transactions using transaction
//          specific temporary table have to end their transactions.
TEST9 >

Here is the key point relating to this example: 

sessions using the session-specific temporary table have to truncate table
Session 2 has already done a truncate so we need to stay in session 1 and do a truncate there:


SQL> truncate table gtt1
  2  /

Table truncated.

SQL>

Now we can drop the GTT:

SQL> drop table gtt1
  2  /

Table dropped.

SQL>

No comments: