🛠️ Session-Specific Data with Oracle Temporary Tables

🛠️ Practice: Session-Specific Data Using Temporary Tables

Temporary tables are perfect for storing data that only needs to persist during a user session or a transaction. Below are hands-on tasks you can try to master session-specific data handling in Oracle SQL.

1. Create a Global Temporary Table (GTT)

CREATE GLOBAL TEMPORARY TABLE SESSION_CART (
  ITEM_ID   NUMBER,
  ITEM_NAME VARCHAR2(100),
  QUANTITY  NUMBER
)
ON COMMIT DELETE ROWS;

This table holds a shopping cart's temporary data. Data will be deleted after each transaction, perfect for a single operation.

2. Insert session data

INSERT INTO SESSION_CART (ITEM_ID, ITEM_NAME, QUANTITY) VALUES (101, 'Premium Rice', 10);
INSERT INTO SESSION_CART (ITEM_ID, ITEM_NAME, QUANTITY) VALUES (102, 'Mustard Oil', 5);

3. Query session data

SELECT * FROM SESSION_CART;

4. Commit transaction and check data again

COMMIT;

SELECT * FROM SESSION_CART; -- Data will be deleted due to ON COMMIT DELETE ROWS

---

5. Create a Private Temporary Table (PTT)

CREATE PRIVATE TEMPORARY TABLE ORA$PTT_USER_SESSION (
  SESSION_ID VARCHAR2(50),
  LOGIN_TIME TIMESTAMP,
  TEMP_NOTE  VARCHAR2(200)
);

This PTT stores temporary session notes visible only in your session, dropped automatically when session ends.

6. Insert session-specific notes

INSERT INTO ORA$PTT_USER_SESSION (SESSION_ID, LOGIN_TIME, TEMP_NOTE)
VALUES ('S12345', SYSTIMESTAMP, 'User started shopping cart');

7. Query session notes

SELECT * FROM ORA$PTT_USER_SESSION;

8. End session

When you disconnect, the private temporary table and its data are dropped automatically.

---

Practice these steps to master session-specific data handling using Oracle temporary tables!