Kamran Agayev's Oracle Blog

Oracle Certified Master

Some notes on Temporary Tables

Posted by Kamran Agayev A. on September 25th, 2009

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. Let’s see it from the following example

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

3 Responses to “Some notes on Temporary Tables”

  1. Mohamed Azar Says:

    SQL> insert into tbl_temp values(1);

    1 row created.

    SQL> select * from tbl_temp;

    ID
    ———-
    1

    SQL> show user;
    USER is “USER1”
    SQL> conn user2/test;
    ERROR:
    ORA-01045: user USER2 lacks CREATE SESSION privilege; logon denied

    Warning: You are no longer connected to ORACLE

    May i know what i mistake?

  2. Mohamed Azar Says:

    It’s working

    Regards

    S.Azar
    DBA

  3. Kamran Agayev A. Says:

    It should work :) I try to post only working examples :)

Leave a Reply

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>