everythingOracle.bizhat.com

 

'Everything you wanted
to know about Oracle'

Training References Syntax Tell A Friend Contact Us

 

 

DBA Fundamentals

01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27
28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57
<< Previous

Chapter # 17

Next >>


 

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
/

     Reviews and Templates for FrontPage
     

Copyright � everythingOracle.bizhat.com 2006 All Rights Reserved.