Global Temporary tables

Well in this post i am not going to discuss about the Global temporary table. Details information is here. Today we will discuss the difference between on commit preserve rows and on commit delete rows.

In on commit delete rows the data is stored in transaction specific.

But in case of on commit preserve rows the data is stored in session specific. But in both case the data is not available for different session.

Here is the example : –

  • Create a temporary table as
  • CREATE GLOBAL TEMPORARY TABLE TT_EMP_DETAILS
    (
        emp_id NUMBER(5),
        emp_name VARCHAR2(50)
    )
    /

  • By default it will the table is ON COMMIT DELETE ROWS.
  • Now insert record into TT_EMP_DETAILS
  • INSERT INTO TT_EMP_DETAILS (emp_id,emp_name) VALUES(1223,’David’)
    /

  • Now select statement it will return the record.
  • Now commit the transaction and execute the select statement, their won’t be any data. This is because the table is created ON COMMIT DELETE ROWS.
  • But if you create the table as ON COMMIT PRESERVE ROWS the data will be available till the session is closed. But for the same session. If user open the different session then it wont be their.
Advertisements

Published by

Anirban

I am software engineer by profession. Sometimes i post some topics about technology and some other topics which i find out interesting. My hobbies include watching movies,music, reading books(both technical and story books(fiction only)).

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s