"Dreams come true.
Without that possibility, nature would not incite us to have
them." John Updike (1932 ) |
Read
first then play the video:
DBA-VIDEO -Creating
and Maintaining a TEMPORARY table
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 iself user.
SQL> CONNECT iself/schooling
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 iself user.
SQL> CONNECT iself/schooling
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
/
"Love is the
difficult realization that something other than oneself is real."
Iris Murdoch (1919 - 1999) |
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
/
|