iSelfSchooling.com  Since 1999     References  |  Search more  | Oracle Syntax  | Free Online Oracle Problem Solver

    .Home      .Subscribe     .Login      .Start Learning      .Certification      .Cancel      .Password Reminder      .Password Change     .Sign out

 

. Online Accounting        .Copyright & User Agreement   |
    .Vision      .Biography     .Acknowledgement

.Contact Us      .Comments/Suggestions       .Email2aFriend    |

 

Online Oracle Training for beginners and advanced - The most comprehensive Oracle tutorial

The authors do not guarantee or take any responsibility for the accuracy, or completeness of the information.

DBAs - Fundamentals

 

Lesson 01 | Lesson 02 | Lesson 03 | Lesson 04 | Lesson 05 | Lesson 06 | Lesson 07 | Lesson 08 | Lesson 09 | Lesson 10 | Lesson 11 | Lesson 12 | Lesson 13 | Lesson 14 | Lesson 15 | Lesson 16 | Lesson 17 | Lesson 18 | Lesson 19 | Lesson 20 | Lesson 21 | Lesson 22 | Lesson 23 | Lesson 24 | Lesson 25 | Lesson 26 |

 

Lesson 17

“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
/