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
- 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.