Hands-On 16 (Creating and Maintaining a TEMPORARY table)

As a DBA, you are responsible for creating and maintaining a TEMPORARY table due to your organization’s developer requirements. They need to use this space to dynamically manipulate data in the memory without using any PL/SQL tables. You will find this feature extremely handy. Your job’s responsibilities dictate that you should at least be informed of the following basic fundamental subjects:

Creating a TRANSACTION temporary table

Creating a SESSION temporary table

Maintaining a TRANSACTION temporary table

Maintaining a SESSION temporary table

Using the ON COMMIT DELETE ROWS option

Using the ON COMMIT PRESERVE ROWS option

Testing a TRANSACTION temporary table

Testing a SESSION temporary table

Dropping a TRANSACTION or SESSION temporary table

Disconnecting from a session

Commands:

CREATE GLOBAL TEMPORARY TABLE

   ON COMMIT DELETE ROWS

INSERT INTO VALUES (100,'Borna')

DROP TABLE

CREATE GLOBAL TEMPORARY TABLE

   ON COMMIT PRESERVE ROWS

DISCONNECT

-- Hands-On 16 (Creating and Maintaining a TEMPORARY table)
-- Preparation
set echo on
connect system/manager as sysdba
SET linesize 1000 pagesize 55
SET SQLPROMPT 'SQL> '
COL name FORMAT a50
col segment_name format a40
col file_name format a50
col extent_management format a20
col username format a10
col member format a50

pause

--Start

CLEAR SCR
-- In this exercise you will learn how to create 
-- a TRANSACTION and SESSION temporary table.

-- Connect to SQLPlus as the iself user.

pause


CONNECT iself/schooling

pause


CLEAR SCR
-- First, let's create a TRANSACTION temporary table.

pause

CREATE GLOBAL TEMPORARY TABLE test_temp
(col1 NUMBER(5) PRIMARY KEY,
col2 VARCHAR2(10) check (col2 BETWEEN 'A' AND 'T'))
ON COMMIT DELETE ROWS
/

-- When the ON COMMIT DELETE ROWS option is used, it means
-- that the temporary table is in the TRANSACTION, not the 
-- SESSION temporary table.

pause


CLEAR SCR
-- Then, insert three records into the test_temp table.

pause


INSERT INTO test_temp VALUES (100,'Borna')
/

INSERT INTO test_temp VALUES (200,'Dana')
/

INSERT INTO test_temp VALUES (300,'Mehri')
/

pause

CLEAR SCR
-- Now, query the test_temp table.

pause


SELECT * FROM test_temp
/

pause

CLEAR SCR
-- Save the transaction.

pause


COMMIT
/

pause

CLEAR SCR
-- Query the test_temp table again.

pause


SELECT * FROM test_temp
/

-- Notice that in the ON COMMIT DELETE ROWS option, the data
-- in a temporary table, along with data in any associated
-- index, is purged after the transaction is completed.

pause


CLEAR SCR 
-- Drop the test_temp table.

pause


DROP TABLE test_temp
/

pause


CLEAR SCR
-- Now, let's create a SESSION temporary table.

pause

CREATE GLOBAL TEMPORARY TABLE test_temp
(col1 NUMBER(5) PRIMARY KEY,
col2 VARCHAR2(10) check (col2 BETWEEN 'A' AND 'T'))
ON COMMIT PRESERVE ROWS
/

-- Notice the PRESERVE option.

pause


CLEAR SCR
-- Insert three records into the test_temp table.

pause


INSERT INTO test_temp VALUES (100,'Borna')
/

INSERT INTO test_temp VALUES (200,'Dana')
/

INSERT INTO test_temp VALUES (300,'Mehri')
/

pause

CLEAR SCR
-- Next, query the test_temp table.

pause


SELECT * FROM test_temp
/

pause

CLEAR SCR
-- Now, save the transaction.

pause

COMMIT
/

pause

CLEAR SCR
-- Query the TEST_TEMP table one more time.

pause


SELECT * FROM test_temp
/

-- Notice that when using the ON COMMIT PERSERVE ROWS option, the data
-- in a temporary table, along with data in any associated
-- index, is preserved after the transaction is completed. 

pause


CLEAR SCR 
-- Now, disconnect the session.

pause

DISCONNECT

pause


CLEAR SCR 
-- Connect again as the iself user.

pause

CONNECT iself/schooling

pause

CLEAR SCR
-- Query the test_temp table again.

pause

SELECT * FROM test_temp
/


-- Notice that the ON COMMIT PERSERVE ROWS option, the data
-- in a temporary table, along with data in any associated
-- index, is purged after the session is terminated. 

pause

CLEAR SCR 
-- Drop the test_temp table.

pause

DROP TABLE test_temp
/

pause

CLEAR SCR
-- Now, you should practice this Hands-On exercise.

-- For more information about the subject, you are encouraged
-- to read from a wide selection of available books.

-- Good luck.
--
pause
pause