Systems Engineering and RDBMS

Temporary tables – Oracle

Posted by decipherinfosys on May 3, 2007

In database development, we often have a need to use temp tables for data storage for processing logic within the code. Let’s take a look at the temporary table support available in Oracle. In next 2 blogs, we will cover the same feature in SQL Server and DB2 LUW as well.

In Oracle, temporary tables are created in a similar way as regular or permanent tables using ‘CREATE GLOBAL TEMPORARY TABLE’ syntax. Once created, temporary tables always reside in the schema, however data in temporary tables is available only during the transaction or during the session. Once the transaction or session ends, data no longer exist in the temporary table. Action followed by ‘ON COMMIT’ clause decides whether table is transaction-specific or session-specific.

Transaction-specific temporary tables:

Run following sql from SQL*Plus prompt to create global temporary table.

CREATE GLOBAL TEMPORARY TABLE TEMP_TRAN
(
COL1 NUMBER(9),
COL2 VARCHAR(30),
COL3 DATE,
CONSTRAINT PK_TEMP_TRAN PRIMARY KEY(COL1)
) ON COMMIT DELETE ROWS
/

In above create statement, ‘ON COMMIT DELETE ROWS’ indicates that this temporary table is transaction-specific. Once data is committed for transaction, it is deleted from this temporary table. Let us create some data now and see what happens.

INSERT INTO TEMP_TRAN VALUES(1,’Decipher’,sysdate);
INSERT INTO TEMP_TRAN VALUES(2,’Information’,sysdate);
INSERT INTO TEMP_TRAN VALUES(3,’systems’,sysdate);

Once records are created, view them using

SQL>SELECT * FROM TEMP_TRAN;

You will see records previously inserted into the table.

COL1 COL2 COL3
———- —————————— ———
1 Decipher 27-MAR-07
2 Information 27-MAR-07
3 systems 27-MAR-07

Now connect to another SQL*Plus session and issue the select command. You will get the message ‘no rows selected’. This proves that data in temporary tables are private to session. Now insert few more records from this new session.

INSERT INTO TEMP_TRAN VALUES(1,’Oracle’,sysdate);
INSERT INTO TEMP_TRAN VALUES(2,’SQLServer’,sysdate);
INSERT INTO TEMP_TRAN VALUES(3,’DB2′,sysdate);

Switchback to original session and issue the ‘COMMIT’ to commit the changes. You can verify once again the existence of data using select statement used previously before committing the transaction. Once you issue ‘COMMIT’, try selecting data from temporary tables.

SELECT * FROM TEMP_TRAN;

Since transaction is committed, data from temporary table got deleted and you will receive the message ‘no rows selected’.

Session-specific temporary tables:

Creation of session-specific temporary table has the same syntax, except ON COMMIT keyword is followed by PRESERVE ROWS. Run following SQL to create session-specific temporary table.

CREATE GLOBAL TEMPORARY TABLE TEMP_SESSION
(
COL1 NUMBER(9),
COL2 VARCHAR(30),
COL3 DATE
) ON COMMIT PRESERVE ROWS
/

As mentioned above, when ‘ON COMMIT PRESERVE ROWS’ is specified, then temporary table is session-specific. Data persist through out the session. Data will be dropped from temporary table only
• When session is terminated normally or abruptly.
• Delete or truncate command is issued on the temporary table.

Let us create some data now.

INSERT INTO TEMP_SESSION VALUES(1,’Decipher’,sysdate);
INSERT INTO TEMP_SESSION VALUES(2,’Information’,sysdate);
INSERT INTO TEMP_SESSION VALUES(3,’systems’,sysdate);

Once records are created, view them using

SQL>SELECT * FROM TEMP_SESSION;

You will see records previously inserted into the table.

COL1 COL2 COL3
———- —————————— ———
1 Decipher 27-MAR-07
2 Information 27-MAR-07
3 systems 27-MAR-07

Now commit the records. As temporary table is defined as session-specific, data will remain available in the table. After committing records, execute select statement again. For sure you will see the same output as shown above.

Open another SQL*Plus session now and connect to the same schema. Execute select statement again and you will not see any records in temp_session table for new session. Yes, data is still private to each session and no other session can view the data of other session.

Insert some more records into the table and commit the changes.

INSERT INTO TEMP_SESSION VALUES(1,’Oracle’,sysdate);
INSERT INTO TEMP_SESSION VALUES(2,’SQLServer’,sysdate);
INSERT INTO TEMP_SESSION VALUES(3,’DB2′,sysdate);

COMMIT;

Issue select statement and you will see the records you have inserted just now. Now let us switch back to original session and issue truncate command in original session. Following is the output of truncate command followed by select statement and its output.

SQL>TRUNCATE TABLE TEMP_SESSION;

Table truncated.

SQL> select * from temp_session;

no rows selected

Again switch back to another session in which we created some new records. Issue the select statement to make sure that when temp_session table was truncated from other session, it did not delete data of this session.

SQL> SELECT * FROM TEMP_SESSION;

COL1 COL2 COL3
———- —————————— ———
1 Oracle 27-MAR-07
2 SQLServer 27-MAR-07
3 DB2 27-MAR-07

In general, following are the key points when temporary tables are involved:
• Global temporary tables are available to all the sessions.
• In Oracle, temporary tables are same as global temporary table and Oracle will not allow creation of temporary tables without using keyword ‘GLOBAL’.
• Every session can insert data into tables simultaneously. But since data is private for each session, data inserted by one session cannot be viewed by other session.
• When ‘TRUNCATE’ command is used against session-specific temporary table, records inserted by that particular session get deleted. Records inserted by other session are still available to respective sessions.
• DML locks are not obtained against data in temporary tables.
• Indexes can be created for temporary tables but data in the indexes are also governed by same scope as of temporary tables.
• Triggers and views can be created on temporary tables.

2 Responses to “Temporary tables – Oracle”

  1. […] Temporary tables – Oracle […]

  2. […] Temporary tables – Oracle […]

Sorry, the comment form is closed at this time.

 
%d bloggers like this: