Some notes on Temporary Tables
Posted by Kamran Agayev A. on 25th September 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
Posted in Administration | 3 Comments »