• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
Techgoeasy

Techgoeasy

Learn Oracle, PHP, HTML,CSS,Perl,UNIX shell scripts

  • Home
  • Oracle
    • Oracle database
    • Oracle Ebusiness Suite
    • Oracle weblogic
    • Oracle Performance Tuning
    • Oracle Hyperion
    • Oracle Cloud Tutorials
  • SQL
  • interview questions
  • Linux
  • PHP and HTML
  • Downloads
  • General
Home » Oracle » Creating a Global Temporary Table in Oracle

Creating a Global Temporary Table in Oracle

September 6, 2018 by techgoeasy Leave a Comment

-Oracle allows us  create a temporary table.

-The definition of a temporary table is visible to all sessions, but the data in a temporary table is visible only to the session that inserts the data into the table.

-We have to use the CREATE GLOBAL TEMPORARY TABLE statement to create a temporary table.

-The ON COMMIT clause indicates if the data in the table is transaction-specific (the default) or session-specific

DELETE ROWS

This creates a temporary table that is transaction specific. A session becomes bound to the temporary table with a transactions first insert into the table. The binding goes away at the end of the transaction. The database truncates the table (delete all rows) after each commit.

PRESERVE ROWS

This creates a temporary table that is session specific. A session gets bound to the temporary table with the first insert into the table in the session. This binding goes away at the end of the session or by issuing a TRUNCATE of the table in the session. The database truncates the table when you terminate the session.

 

Temporary tables are useful in applications where a result set is to be buffered, perhaps because it is constructed by running multiple DML operations

CREATE GLOBAL TEMPORARY TABLE GL_DATA_TEMP
(startdate DATE,
enddate DATE,
gl_id CHAR(20))
ON COMMIT DELETE ROWS;

Indexes can be created on temporary tables. They are also temporary and the data in the index has the same session or transaction scope as the data in the underlying table..

If the TRUNCATE statement is issued against a temporary table, only the session specific data is truncated. There is no effect on the data of other sessions.

If you rollback a transaction, the data you entered is lost, although the table definition persists.

Data in temporary tables is stored in temp segments in the temp tablespace which does not generate any redo so operation using global temporary table are relatively faster. But undo are still generated  in undo tablespace which has redo logging. So redo operation are not totally eliminated in Global temporary tables but they are relatively lower

Data in temporary tables is automatically deleted at the end of the database session, even if it ends abnormally.

Views can be created against temporary tables and combinations of temporary and permanent tables. They can be have triggers associated with them

With 12.1(12c database) Oracle release , concept of temporary undo has been introduced which  allows the undo segments for global temporary tables to be stored in the temporary tablespace. This allows global temporary tables to be used in physical standby databases and read-only databases, as well as removing the need to create redo.

ALTER SYSTEM SET TEMP_UNDO_ENABLED = TRUE;
ALTER SYSTEM SET TEMP_UNDO_ENABLED = FALSE;

 

Filed Under: Oracle, Oracle Sql

Reader Interactions

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Primary Sidebar



Subscribe to our mailing list

Enter your email address to subscribe to this blog and receive notifications of new posts by email

Recent Posts

  • How the sql query is executed in Oracle
  • How to do sql query tuning in Oracle
  • How to enable 10053 trace in Oracle
  • How to find the bind variable of the sql id
  • How to list parameter set at session level in Oracle

Copyright © 2023 : TechGoEasy

  • Hire me
  • Privacy Policy
  • Contact Us
  • New? Start Here
  • About Us