Temporary Table Notes

Article by author Kamran Agayev Agamehdi

Kamran Agayev Agamehdi  is an Oracle Certified Professional DBA (9i, 10g) with over 5 years experience with UNIX Systems and with Oracle Databases and author of Oracle Backup & Recovery by Rampant Tech Press.

To hold data only in your session or transaction life, you can create temporary table which data will be deleted after completion of session or transaction. There’re some specific features of temporary tables. In this post I show some of them on examples

Each session has its own data

SQL> CREATE USER user1 IDENTIFIED BY test;

User created.

SQL> CREATE USER user2 IDENTIFIED BY test;

User created.

SQL> grant connect, resource, create public synonym to user1;

Grant succeeded.

SQL> conn user1/test
Connected.
SQL> CREATE GLOBAL TEMPORARY TABLE tbl_temp (id number) on commit delete rows;

Table created.

SQL> grant select, insert, update, delete on tbl_temp to user2;

Grant succeeded.

SQL> CREATE PUBLIC SYNONYM tbl_temp FOR tbl_temp;

Synonym created.

SQL> INSERT INTO tbl_temp VALUES(1);

1 row created.

SQL> SELECT * FROM tbl_temp;

        ID
———-
         1

From the second session connect with USER2 and insert some data to temporary table

SQL> conn user2/test
Connected.
SQL> select * from tbl_temp;

no rows selected

SQL> insert into tbl_temp values(333);

1 row created.

SQL> select * from tbl_temp;

        ID
———-
       333

SQL>

Now switch to the first session and select data from tbl_temp

SQL> SELECT * FROM tbl_temp;

        ID
———-
         1

SQL>

As you see, each session has its own data for the same temporary table
.

If temporary table is truncated from one session, only data of that session is truncated. Meanwhile, other users will be able to see their data in their own temporary table
Now, TRUNCATE tbl_temp table from the first session and query it from another session:

SQL> truncate table tbl_temp;

Table truncated.

SQL> SELECT * FROM tbl_temp;

no rows selected

SQL>

//Query the table from the second session:

SQL> select * from tbl_temp;

        ID
———-
       333

 If you issue command COMMIT, you’ll lose all your data because you’ve create the table using ON COMMIT DELETE ROWS functionality

SQL> commit;

Commit complete.

SQL> select * from tbl_temp;

no rows selected

SQL>

Indexes created on temporary tables are also temporary. The following example shows this:

SQL> show user
USER is “SYS”
SQL> CREATE GLOBAL TEMPORARY TABLE tbl_temp (id number) on commit preserve rows;
Table created.

SQL> create index idx_tbl_temp on tbl_temp (id);

Index created.

SQL> select num_rows, temporary from dba_indexes where index_name=’IDX_TBL_TEMP’;

  NUM_ROWS T
———- -
         0 Y
        
SQL>

SQL> BEGIN
  2  FOR i IN 1..100 LOOP
  3  INSERT INTO tbl_temp VALUES(i);
  4  END LOOP;
  5  END;
  6  /

PL/SQL procedure successfully completed.

SQL> select count(1) from tbl_temp;

  COUNT(1)
———-
       100

SQL> analyze index idx_tbl_temp compute statistics;

Index analyzed.

SQL> select num_rows from dba_indexes where index_name=’IDX_TBL_TEMP’;

  NUM_ROWS
———-
       100
       
SQL> disc
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Pr
oduction
With the Partitioning, OLAP and Data Mining options
SQL> conn / as sysdba
Connected.
SQL> select num_rows from dba_indexes where index_name=’IDX_TBL_TEMP’;

  NUM_ROWS
———-
       100
        
SQL> select count(1) from tbl_temp;

  COUNT(1)
———-
         0

SQL> analyze index idx_tbl_temp compute statistics;

Index analyzed.

SQL> select num_rows, temporary from dba_indexes where index_name=’IDX_TBL_TEMP’;

  NUM_ROWS T
———- -
         0 Y