Oracle GTT

Oracle Global Temporary Tables

oracle_global_temporary_tables

Applications often use some form of temporary data store for processes that are to complicated to complete in a single pass. Often, these temporary stores are defined as database tables or PL/SQL tables.

Temporary Tables

Oracle support two types of temporary tables.

  1. Global Temporary Tables : Available since Oracle 8i.
  2. Private Temporary Tables : Available since Oracle 18c. details here.

Creation of Global Temporary Tables

The data in a global temporary table is private, such that data inserted by a session can only be accessed by that session. The session-specific rows in a global temporary table can be preserved for the whole session, or just for the current transaction.

The ON COMMIT DELETE ROWS clause indicates the data should be deleted at the end of the transaction, or the end of the session.

CREATE GLOBAL TEMPORARY TABLE gtt_temp_table (
  id          NUMBER,
  description VARCHAR2(20)
)
ON COMMIT DELETE ROWS;

-- Insert, but don't commit, then check contents of GTT.
INSERT INTO gtt_temp_table VALUES (1, 'ONE');

SELECT COUNT(*) FROM gtt_temp_table;

  COUNT(*)
----------
         1

SQL>

-- Commit and check contents.
COMMIT;

SELECT COUNT(*) FROM gtt_temp_table;

  COUNT(*)
----------
         0

SQL>

In contrast, the ON COMMIT PRESERVE ROWS clause indicates that rows should persist beyond the end of the transaction. They will only be removed at the end of the session.

CREATE GLOBAL TEMPORARY TABLE gtt_temp_table (
  id          NUMBER,
  description VARCHAR2(20)
)
ON COMMIT PRESERVE ROWS;

-- Insert and commit, then check contents of GTT.
INSERT INTO gtt_temp_table VALUES (1, 'ONE');
COMMIT;

SELECT COUNT(*) FROM gtt_temp_table;

  COUNT(*)
----------
         1

SQL>

-- Reconnect and check contents of GTT.
CONN tiger/scott

SELECT COUNT(*) FROM gtt_temp_table;

  COUNT(*)
----------
         0

SQL>

Note − Oracle does not allow you to specify any foreign key constraints on temporary tables. Attempting to create a foreign key on a temporary table will produce an ORA-14455 error.

Oracle Error: ORA-14455

Error Description:
Attempt to create referential integrity constraint on temporary table

Error Cause:
An attempt was made to create a referential integrity constraint on a temporary table. This is not supported.

For example, attempting to create a foreign key on a temporary table will produce ORA-14455, since Oracle does not support foreign keys in temporary tables.

Action:
Use triggers and/or do not attempt to create foreign keys on a temporary table.

Restrictions

  • Temporary tables cannot be partitioned, clustered, or index organized.

  • You cannot specify any foreign key constraints on temporary tables.

  • Temporary tables cannot contain columns of nested table.

  • You cannot specify the following clauses of the LOB_storage_clause: TABLESPACE, storage_clause, or logging_clause.

  • Parallel UPDATE, DELETE and MERGE are not supported for temporary tables.

  • The only part of the segment_attributes_clause you can specify for a temporary table is TABLESPACE, which allows you to specify a single temporary tablespace.

  • Distributed transactions are not supported for temporary tables.

  • A temporary table cannot contain INVISIBLE columns.


If you like dEexams.com and would like to contribute, you can also write your article here or mail your article to admin@deexams.com . See your article appearing on the dEexams.com main page and help others to learn.


Please write comments if you find anything incorrect, or you want to share more information about the topic discussed above.


Python if , elif and else

Python Conditions and If statements

  • 0
Python for beginners

Learning Python Part 1

  • 3
Struct Alignment and Padding

Struct Alignment and Padding in C++ And C

  • 0
Friend function

Friend function C++

  • 0
Pointers

C++ Pointers

  • 0
Structures

C++ Structures

  • 0
Types of Inheritance in C++

Inheritance and access specifiers C++

  • 0
Java date pattern

Java Date Pattern Syntax

  • 0
Java Date and Calendar

Java Date formats

  • 0
JAVA Data Type

Data types in Java

  • 0
Java unreachable code

Unreachable Code Error in Java

  • 0

Post Comment

Comments(0)

WEB TECHNOLOGY

Articles

×

Forgot Password

Please enter your email address below and we will send you information to change your password.