11.4 Global Temporary Tables & External Tables

11.4 Global Temporary Tables & External Tables

While standard relational tables store persistent data inside database datafiles, Oracle Database provides specialized table architectures for temporary processing and external data ingestion:

1. Global Temporary Tables (GTT): High-performance staging tables whose definitions are permanent in the data dictionary, but whose data rows are private to each user session or transaction.
2. External Tables: Read-only virtual tables that treat operating system flat files (e.g., CSV, TSV, fixed-width text) as relational tables queryable via standard SQL.

Mastering their creation, lifecycle scopes, storage mechanics, and exam-tested restrictions is essential for the 1Z0-071 certification.

---

Global Temporary Tables (GTT)

A Global Temporary Table is designed to hold intermediate calculation results or staged batch data without incurring the logging and locking overhead of permanent tables.

+-------------------------------------------------------------------------+
|                   GLOBAL TEMPORARY TABLE ARCHITECTURE                   |
+-------------------------------------------------------------------------+
| Metadata (Table & Column Definitions):                                  |
| - Stored permanently in the Data Dictionary (USER_TABLES).              |
| - Visible to all database sessions with proper privileges.              |
|                                                                         |
| Data Rows & Segments:                                                   |
| - Stored in the user's TEMPORARY tablespace (temp segments).            |
| - Completely private to each session; Session A cannot see Session B's data.|
| - No multi-user locking contention.                                     |
| - Generates minimal redo (logs redo only for undo generation).          |
+-------------------------------------------------------------------------+

GTT Syntax and Lifecycles

$\text{Syntax: } \text{CREATE GLOBAL TEMPORARY TABLE } tab\_name \text{ (...) ON COMMIT [DELETE | PRESERVE] ROWS;}$

-- 1. Transaction-Specific GTT (Default):
CREATE GLOBAL TEMPORARY TABLE gtt_trans_calc (
    calc_id    NUMBER(10),
    subtotal   NUMBER(12, 2)
) ON COMMIT DELETE ROWS;

-- 2. Session-Specific GTT:
CREATE GLOBAL TEMPORARY TABLE gtt_session_stage (
    session_token  VARCHAR2(64),
    payload_data   CLOB
) ON COMMIT PRESERVE ROWS;

Transaction-Specific vs. Session-Specific GTTs

GTT Rules and Behavioral Characteristics

• Indexes, Triggers, and Views: You can create indexes, triggers, and views on a GTT. The index structure is permanent in metadata, but index entries are session/transaction-private.
• TRUNCATE Behavior: Executing TRUNCATE TABLE my_gtt; deletes rows only for the issuing session; it does not affect any other concurrent session using the table.
• DDL Locks (ORA-14452): You cannot execute ALTER TABLE or DROP TABLE on a GTT if any active session currently has uncommitted or preserved rows in that GTT.
• No Redo Logging on Data: GTT data blocks do not generate redo log entries (only undo operations generate minimal redo), resulting in dramatic performance gains for ETL and batch staging.

---

External Tables

An External Table allows Oracle SQL queries to read data directly from operating system flat files stored on the database server file system as if they were standard relational tables.

+-------------------------------------------------------------------------+
|                       EXTERNAL TABLE ARCHITECTURE                       |
+-------------------------------------------------------------------------+
| SQL Engine: SELECT * FROM sales_external_csv WHERE sale_amount > 100    |
|                                    |                                    |
|                                    v                                    |
| Data Dictionary (Metadata Only)    Access Driver (ORACLE_LOADER)        |
| - Column definitions               - Parses CSV / Delimited text        |
| - Directory mapping                - Transforms text to SQL datatypes   |
|                                    |                                    |
|                                    v                                    |
| Operating System Server Directory: [/opt/oracle/data/sales_2026.csv]     |
+-------------------------------------------------------------------------+

Step 1: Create the Directory Object

External tables access OS files through an Oracle DIRECTORY object, which acts as a secure alias for an absolute file system path:

-- Must be executed by SYS or a user with CREATE ANY DIRECTORY privilege
CREATE OR REPLACE DIRECTORY ext_data_dir AS '/opt/oracle/data/import';

-- Grant access privileges to the schema owner
GRANT READ, WRITE ON DIRECTORY ext_data_dir TO hr;

Exam Tip: Directory objects are owned by SYS and belong to a global database namespace. The directory path is not verified when the directory object is created; Oracle verifies the OS path only when an external table query attempts to access a file.

Step 2: Declare the External Table

CREATE TABLE employees_ext (
    emp_id      NUMBER(6),
    first_name  VARCHAR2(20),
    last_name   VARCHAR2(25),
    email       VARCHAR2(50),
    hire_date   DATE,
    salary      NUMBER(8, 2)
)
ORGANIZATION EXTERNAL (
    TYPE ORACLE_LOADER
    DEFAULT DIRECTORY ext_data_dir
    ACCESS PARAMETERS (
        RECORDS DELIMITED BY NEWLINE
        FIELDS TERMINATED BY ','
        MISSING FIELD VALUES ARE NULL (
            emp_id     CHAR,
            first_name CHAR,
            last_name  CHAR,
            email      CHAR,
            hire_date  CHAR DATE_FORMAT DATE MASK "YYYY-MM-DD",
            salary     CHAR
        )
    )
    LOCATION ('employees_2026.csv')
)
REJECT LIMIT UNLIMITED;

Access Drivers: ORACLE_LOADER vs. ORACLE_DATAPUMP

---

Strict External Table Restrictions (Exam Tested!)

External tables are virtual read-only access layers over flat files. Oracle enforces rigorous restrictions:

+-------------------------------------------------------------------------+
|                 EXTERNAL TABLE RESTRICTIONS SUMMARY                     |
+-------------------------------------------------------------------------+
| 1. NO DML Operations: INSERT, UPDATE, and DELETE are strictly prohibited|
|    when using ORACLE_LOADER (raises ORA-30657: operation not supported).|
| 2. NO Indexes: You CANNOT create B-tree, bitmap, or unique indexes.     |
| 3. NO Constraints: Primary key, foreign key, unique, and check          |
|    constraints are prohibited (optional inline NOT NULL is permitted).  |
| 4. NO Virtual Columns: Derived or virtual columns are not supported.    |
| 5. NO LOB Columns: CLOB, BLOB, and RAW have limited/restricted support. |
+-------------------------------------------------------------------------+

---

Comparison: Standard vs. GTT vs. External Tables