Oracle temporary tables are a convenient feature that holds data only within a session and automatically deletes it after processing is completed. In particular, “Oracle Global Temporary Tables (GTT)” are frequently used for holding intermediate data during complex batch processing. This article explains the implementation procedures, control of deletion timing, and precautions for use, assuming Oracle Database 19c.
- Conclusion / Quick Start (To-Do List)
- Background and Basics: What are Oracle Temporary Tables?
- Specific Use Cases: When to Use Temporary Tables?
- Procedure/Implementation: How to Create Temporary Tables
- Troubleshooting: Common ORA Errors
- Operational, Monitoring, and Security Precautions
- FAQ: Frequently Asked Questions
- Summary
- Future Update Points: Transition from 19c to 23ai
Conclusion / Quick Start (To-Do List)
The main steps for implementing Oracle temporary tables (Global Temporary Tables: GTT) are as follows:
- Definition Creation: Create the table definition using the
CREATE GLOBAL TEMPORARY TABLEstatement (data is not persisted, but the definition remains in the data dictionary). - Scope Selection: Choose whether to clear data at the end of a transaction (
DELETE ROWS) or keep it until the end of the session (PRESERVE ROWS). - Privilege Granting: Ensure the user has
CREATE TABLEorCREATE ANY TABLEprivileges. - Temporary Tablespace Check: Since data is stored in the
TEMPORARYtablespace, ensure sufficient free space is available.
Background and Basics: What are Oracle Temporary Tables?
Normally, data inserted into an Oracle table is persisted after a COMMIT. However, when you want to save temporary calculation results, regular tables incur significant overhead from REDO log generation and space management.
Mechanism of Global Temporary Tables (GTT)
The most significant characteristic of GTT is that “the table definition is shared by all users, but the data inside is independent for each session.”
- Confidentiality: Data inserted in your session is never visible to other sessions (users).
- Automatic Deletion: Oracle automatically cleans up data according to the defined scope (at the end of a transaction or session).
- Efficiency: Compared to permanent tables, REDO log generation is reduced, making them suitable for high-volume processing.
Beginner’s Note: Although named “Global,” the data is not shared. It means the “definition is global (common to everyone).”
Specific Use Cases: When to Use Temporary Tables?
Temporary tables provide benefits beyond being just a “temporary storage area.” They are particularly effective in the following cases:
1. Intermediate Calculations in Complex Multi-step Batch Processing
When performing calculations so complex that they are difficult to describe in a single SQL statement, intermediate results for each step are stored in a temporary table.
- Advantage: It makes it easier to verify the results of each step and simplifies SQL when the same intermediate result is referenced multiple times in subsequent processing.
2. Large-scale Data Validation and Cleansing
Data imported from external systems is stored temporarily before being reflected in the production tables.
- Advantage: You can perform data integrity checks and format conversions at high speed without locking the production tables (permanent tables).
3. Search Filter Condition Retention for Web Applications
Results filtered by complex conditions (such as ID lists) are held in a temporary table, which is then used as a base to generate multiple reports or graphs.
- Advantage: Since they are independent per session, there is no risk of mixing with other users’ search conditions. No cleanup is required as they are cleared automatically at the end of the session.
Procedure/Implementation: How to Create Temporary Tables
When creating, use the ON COMMIT clause to specify the data expiration.
- Transaction-specific (Default): Data is cleared when a
COMMITis issued. - Session-specific: Data remains until logout (disconnection).
Execution Examples: SQL Snippets
Environment: Oracle Database 19c / Oracle Linux Prerequisite Privileges: General schema with CREATE TABLE privilege
1. Deleting Data at the End of a Transaction
Suitable for batch processing where you want to empty the table immediately after one INSERT + SELECT is finished.
-- Create Global Temporary Table (Transaction specific)
CREATE GLOBAL TEMPORARY TABLE GTT_WORK_DATA (
item_id NUMBER,
item_name VARCHAR2(100),
amount NUMBER
) ON COMMIT DELETE ROWS;
-- Insert data
INSERT INTO GTT_WORK_DATA (item_id, item_name, amount) VALUES (1, 'Sample A', 500);
-- Data exists before commit
SELECT COUNT(*) FROM GTT_WORK_DATA;
-- Data will be deleted after commit
COMMIT;
-- Result will be 0
SELECT COUNT(*) FROM GTT_WORK_DATA;
Note: ON COMMIT DELETE ROWS is the default setting, so it can be omitted.
2. Retaining Data Until the End of the Session
Suitable when you want to reuse calculation results across multiple transactions.
-- Create Global Temporary Table (Session specific)
CREATE GLOBAL TEMPORARY TABLE GTT_SESSION_DATA (
user_id NUMBER,
log_msg VARCHAR2(200)
) ON COMMIT PRESERVE ROWS;
-- Insert and Commit
INSERT INTO GTT_SESSION_DATA VALUES (101, 'Process Started');
COMMIT;
-- Data still exists after commit
SELECT * FROM GTT_SESSION_DATA;
-- Data is cleared only after session disconnects.
Note: When the session is disconnected, the data is automatically truncated.
Troubleshooting: Common ORA Errors
| Error Code | Cause | Solution |
|---|---|---|
| ORA-14450 | Attempted to modify or delete a GTT currently in use by another session. | Wait for the session in use to end or disconnect the connection. |
| ORA-14452 | Attempted a DROP TABLE while data still exists. | Execute TRUNCATE TABLE or delete after ending the session. |
| ORA-01652 | Insufficient space in the temporary tablespace (TEMP). | Consider extending the size of the temporary tablespace or optimizing queries during large data inserts. |
Operational, Monitoring, and Security Precautions
Pros and Pitfalls
- Pros: No lock contention occurs. Since each user has their own data area, a strength is that no waiting occurs due to “row locks” as in regular tables.
- Pitfalls: While creating indexes is possible, attention to statistics handling is required. Since 12c, session-specific statistics can be maintained, but inappropriate statistics can lead to degradation of execution plans.
Data Recovery
Since temporary table data is deleted automatically, data lost by an accidental COMMIT cannot be recovered with a ROLLBACK (when DELETE ROWS is set).
FAQ: Frequently Asked Questions
Q1. I want to delete (DROP) a temporary table, but an error occurs. A. If there is an active session inserting data into that table, it cannot be deleted. Ensure that all sessions have ended.
Q2. Can multiple users use an “Oracle temporary table” with the same name? A. Yes. While there is only one table definition, the data is independent for each user (session), allowing simultaneous use without conflict.
Q3. Can I use the TRUNCATE statement? A. Yes, it is usable. This is effective when you want to explicitly delete all data even if the session continues.
Summary
- Oracle temporary tables are ideal for high-speed processing of temporary data and session isolation.
- GTT (Global Temporary Tables) have a permanent definition and temporary data.
ON COMMIT DELETE ROWSis for short-term,PRESERVE ROWSis for long-term (within session) retention.- Data storage destination is the
TEMPtablespace.
Future Update Points: Transition from 19c to 23ai
Since Oracle 18c, a new feature called “Private Temporary Tables (PTT)” has been introduced.
- PTT Features: Since they are defined in memory, information does not remain in the data dictionary (naming rule requires starting with
ORA$PTT_). - 23ai Perspective: Cases recommending the use of lighter PTT are increasing, but GTT remains mainstream in terms of ease of development and tool support.
Note: This content applies to Oracle Database 19c.
[reference]
Managing Tables
コメント