ExamFinch.com My Study

Study guide

11.4 Global Temporary Tables & External Tables

Practice this exam

Oracle Database SQL Certified Associate 1Z0-071

Track your progress as you read

Finished sections get a check mark.

1 Chapter 1: Relational Database Concepts & Logical Modeling

1.1 Relational Database Architecture & Terminology 1.2 Entity Relationship Modeling & SQL Mapping 1.3 Database Normalization & Design Fundamentals

2 Chapter 2: Retrieving Data with SQL SELECT

2.1 SQL SELECT Fundamentals & Projection 2.2 Literal Values, Concatenation, and Quote Operators 2.3 Handling NULLs & Distinct Rows in Projection

3 Chapter 3: Restricting and Sorting Query Data

3.1 WHERE Clause Filtering & Comparison Operators 3.2 Logical Conditions & Operator Precedence 3.3 Sorting Data with ORDER BY 3.4 Substitution Variables & Session Directives

4 Chapter 4: Customizing Output with Single-Row Functions

4.1 Character Manipulation & Case Conversion 4.2 Numeric & Mathematical Functions 4.3 Date Arithmetic & Date Manipulation Functions 4.4 Nesting Single-Row Functions

5 Chapter 5: Conversion Functions and Conditional Expressions

5.1 Implicit vs Explicit Datatype Conversion 5.2 TO_CHAR, TO_NUMBER, and TO_DATE Functions 5.3 General NULL-Handling Functions 5.4 Conditional Expressions: CASE and DECODE

6 Chapter 6: Reporting Aggregated Data with Group Functions

6.1 Aggregate Functions & NULL Mechanics 6.2 GROUP BY Clause & Aggregation Rules 6.3 Filtering Groups with the HAVING Clause

7 Chapter 7: Displaying Data from Multiple Tables (Joins)

7.1 ANSI/ISO SQL Equijoins & Natural Joins 7.2 Non-Equijoins, Self Joins, and Cartesian Products 7.3 Outer Joins: LEFT, RIGHT, and FULL

8 Chapter 8: Solving Queries with Subqueries

8.1 Single-Row Subqueries 8.2 Multiple-Row Subqueries & Inline Views 8.3 Correlated Subqueries & EXISTS Logic 8.4 Subquery Cardinality & NULL Traps

9 Chapter 9: Set Operators

9.1 UNION and UNION ALL Operators 9.2 INTERSECT and MINUS Operators 9.3 Set Operator Guidelines & ORDER BY Rules

10 Chapter 10: Managing Tables with DML & Transaction Control

10.1 INSERT, UPDATE, and DELETE Statements 10.2 MERGE and Multitable Inserts 10.3 Transaction Management: COMMIT, ROLLBACK, and SAVEPOINT

11 Chapter 11: Managing Tables and Relationships with DDL

11.1 CREATE TABLE & Oracle Built-In Datatypes 11.2 Integrity Constraints 11.3 Modifying Tables & Managing Constraints 11.4 Global Temporary Tables & External Tables

12 Chapter 12: Views

12.1 Simple vs Complex Views 12.2 DML Operations on Views & View Constraints

13 Chapter 13: Indexes, Synonyms, and Sequences

13.1 Sequences 13.2 Indexes 13.3 Synonyms

14 Chapter 14: Controlling User Access & Privileges

14.1 System Privileges 14.2 Object Privileges 14.3 Roles & Privilege Management

15 Chapter 15: Oracle Data Dictionary Views

15.1 Data Dictionary Structure & View Prefixes 15.2 Querying Schema & Object Metadata 15.3 Inspecting Constraints, Privileges, and Comments

16 Chapter 16: Managing Data in Different Time Zones

16.1 Datetime Data Types & Time Zone Offsets 16.2 Interval Data Types & Arithmetic 16.3 Time Zone Functions & Session Environment

11.4 Global Temporary Tables & External Tables

Key Takeaways Explain This Topic AI

Global Temporary Tables (GTT) store permanent metadata in the data dictionary while allocating session-private or transaction-private data rows in temporary tablespaces.

ON COMMIT DELETE ROWS creates transaction-specific GTTs (data cleared on COMMIT/ROLLBACK), whereas ON COMMIT PRESERVE ROWS creates session-specific GTTs (data cleared on session disconnect).

GTTs generate minimal redo logs (only redo for undo) and eliminate shared buffer cache locking contention, providing high-performance scratchpad staging.

External Tables allow querying flat operating system files (CSV, TSV) using standard SQL SELECT queries via access drivers such as ORACLE_LOADER.

External tables are strictly read-only virtual structures: DML statements (INSERT, UPDATE, DELETE), indexes, and constraints (other than optional NOT NULL) are prohibited.

Last updated: August 2026

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:

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.

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 Syntax: CREATE GLOBAL TEMPORARY TABLE t a b _ n a m e (...) ON COMMIT [DELETE | PRESERVE] ROWS; \text{Syntax: } \text{CREATE GLOBAL TEMPORARY TABLE } tab\_name \text{ (...) ON COMMIT [DELETE | PRESERVE] ROWS;} Syntax: CREATE GLOBAL TEMPORARY TABLE t ab _ nam e (...) 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

Feature ON COMMIT DELETE ROWS (Default) ON COMMIT PRESERVE ROWS

Scope Transaction-specific. Session-specific.

Data Lifetime Data exists only for the duration of the transaction. Data persists across commits throughout the entire session.

Data Cleared When? Executing COMMIT , ROLLBACK , or session disconnect. Terminating the session or issuing explicit TRUNCATE TABLE .

Use Case Single-transaction procedural processing, complex multi-step DML calculations. Multi-step web application sessions, shopping carts, reporting wizards.

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

Access Driver Supported File Formats Read Capability Write Capability (Unload)

ORACLE_LOADER (Default) Text files (CSV, TSV, fixed-width, delimited text). YES (Standard SQL queries). NO (Strictly read-only).

ORACLE_DATAPUMP Proprietary Oracle binary dump files. YES YES (via CREATE TABLE ... AS SELECT ).

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
  • Architectural Attribute Standard Table Global Temporary Table (GTT) External Table
  • Metadata Storage Permanent (Data Dictionary) Permanent (Data Dictionary) Permanent (Data Dictionary)

Data Storage Location Database Datafiles ( *.dbf ) Temporary Tablespace (RAM/Temp) OS File System (CSV/Flat files)

  • Data Scope & Visibility Shared across all sessions Session or Transaction Private Shared across all sessions
  • DML Operations ( INSERT / UPDATE / DELETE ) Fully Supported Fully Supported Prohibited ( ORACLE_LOADER )
  • Index Support All index types supported Supported (Private entries) Prohibited
  • Constraint Support All 5 constraint types All 5 constraint types Only optional NOT NULL
  • Redo Log Generation Full Redo & Undo Redo for Undo only (Minimal) None (Read-only)