|
Creating and Maintaining a TEMPORARY table
Introduction
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
In this
exercise you will learn how to create a
TRANSACTION and SESSION temporary table.
Connect to SQLPlus as the oracle user.
SQL> CONNECT
oracle/learning
Create a
TRANSACTION temporary table
First, let's create a TRANSACTION temporary
table.
SQL> CREATE GLOBAL
TEMPORARY TABLE test_temp
(col1 NUMBER(5) PRIMARY KEY,
col2 VARCHAR2(10) check (col2 BETWEEN 'A'
AND 'T'))
ON COMMIT DELETE ROWS
/
Note that when the ON COMMIT
DELETE ROWS option is used, it means that
the temporary table is in the TRANSACTION,
not the SESSION temporary table.
Then, insert three records into the
test_temp table.
SQL> INSERT
INTO test_temp VALUES (100,'Borna')
/
SQL> INSERT
INTO test_temp VALUES (200,'Dana')
/
SQL> INSERT
INTO test_temp VALUES (300,'Mehri')
/
Now, query the test_temp table.
SQL> SELECT * FROM
test_temp
/
Save the transaction.
SQL> COMMIT
/
Query the test_temp table again.
SQL> 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.
Drop the test_temp table.
SQL> DROP TABLE test_temp
/
Create a SESSION temporary table
Now, let's
create a SESSION temporary table.
SQL> 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.
Insert three records into the test_temp
table.
SQL> INSERT
INTO test_temp VALUES (100,'Borna')
/
SQL> INSERT INTO test_temp VALUES
(200,'Dana')
/
SQL> INSERT INTO test_temp VALUES (300,'Mehri')
/
Next, query
the test_temp table.
SQL> SELECT * FROM
test_temp
/
Now, save the transaction.
SQL> COMMIT
/
Query the TEST_TEMP table one more time.
SQL> 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.
Now,
disconnect the session.
SQL> DISCONNECT
Connect again as the oracle user.
SQL> CONNECT
oracle/learning
Query the
test_temp table again.
SQL> 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.
Drop the test_temp table.
SQL> DROP TABLE
test_temp
/
Questions:
Q: How do you
create a TRANSACTION temporary table?
Q: How do you
create a SESSION temporary table?
Q: Describe
the ON COMMIT DELETE ROWS option.
Q: Describe
the ON COMMIT PRESERVE ROWS option.
Q: How do you
drop a transaction or session temporary
table?
Q: What do the
following SQL statements do?
SQL> CREATE
GLOBAL TEMPORARY TABLE test_temp
(col1 NUMBER(5) PRIMARY KEY,
col2 VARCHAR2(10) check (col2 BETWEEN 'A'
AND 'T'))
ON COMMIT DELETE ROWS
/
SQL> CREATE GLOBAL TEMPORARY TABLE test_temp
(col1 NUMBER(5) PRIMARY KEY,
col2 VARCHAR2(10) check (col2 BETWEEN 'A'
AND 'T'))
ON COMMIT PRESERVE ROWS
/ |
