In the given example, we created a Global Temporary Table (GTT) named session_transactions using the ON COMMIT PRESERVE ROWS clause. This setup ensures that the data inserted into the table remains available throughout the entire database session, even after a COMMIT is issued. This is particularly useful in real-time scenarios like banking applications, where multiple transactions (credits or debits) are temporarily recorded during a user’s session for validation, batch processing, or reporting. Each session maintains its own copy of data, providing data isolation between users. The inserted rows will stay intact across commits until the user ends the session or logs out. Once the session ends, the data is automatically deleted, ensuring no residual data persists. This behavior helps in managing temporary, session-based data without cluttering the permanent database tables.
📘 Use Case (Real-Time Scenario)
Suppose you are working in a banking application where a user wants to perform multiple transactions (credit/debit) in a session. You want to store these transactions temporarily during the session for logging or validation — but the data should persist throughout the session, not be deleted after each commit. Later, you process or discard them at session end.
🔧 Step-by-Step SQL Implementation
✅ Step 1: Create the Global Temporary Table
CREATE GLOBAL TEMPORARY TABLE session_transactions (
txn_id NUMBER,
user_id NUMBER,
amount NUMBER(10,2),
txn_type VARCHAR2(10), -- CREDIT or DEBIT
txn_time TIMESTAMP DEFAULT SYSTIMESTAMP
) ON COMMIT PRESERVE ROWS;
🔹 Explanation:
This creates a temporary table session_transactions.
ON COMMIT PRESERVE ROWS means data remains intact even after a COMMIT, as long as the session is active.
✅ Step 2: Insert Sample Data
INSERT INTO session_transactions (txn_id, user_id, amount, txn_type)
VALUES (1001, 501, 2000.00, 'DEBIT');
COMMIT;
🔹 Explanation:
Even after COMMIT, the row will still exist because of PRESERVE ROWS.
✅ Step 3: Query the Data
SELECT * FROM session_transactions;
🔹 You will still see the data, proving it survives across commits in the same session.
✅ Step 4: Open a New Session (or reconnect) and Query Again
SELECT * FROM session_transactions;
🔹 Now, the table will be empty, because GTT data is session-specific. It doesn't persist between sessions.
✅ Summary of ON COMMIT PRESERVE ROWS
Feature Behavior
After COMMIT Data remains available
After SESSION ends Data is deleted
Use Case Session-based tasks like logging, staging
Top comments (0)