Difference Between Temporary Table and Global Temporary Table in Oracle

Let's talk about some key pointers differentiating temporary tables and global temporary tables in Oracle. 

In Oracle, a temporary table is a table that exists only for the duration of a session or a transaction. It is automatically dropped at the end of the session or transaction. 

A global temporary table, on the other hand, is a temporary table that is visible to all sessions and persists until the last session using it terminates. However, the data within a global temporary table is session-specific, meaning each session sees only its own data. Global temporary tables are useful for sharing temporary data across multiple sessions without interference between sessions.

1. Scope:

   - Temporary tables: Exist only within a session or transaction.

   - Global temporary tables: Visible to all sessions but the data is session-specific.

2. Persistence:

   - Temporary tables: Automatically dropped at the end of the session or transaction.

   - Global temporary tables: Persist until the last session using them terminates, then they are automatically truncated.

3. Data Isolation:

   - Temporary tables: Isolated within the session or transaction where they are created.

   - Global temporary tables: Data is session-specific, each session sees only its own data.

4. Usage:

   - Temporary tables: Ideal for temporary storage within a specific session or transaction.

   - Global temporary tables: Useful for sharing temporary data across multiple sessions without interference between sessions.

5. Naming Conventions:

   - Temporary tables: Usually named with a single '#' prefix or specified as TEMPORARY in the table definition.

   - Global temporary tables: Typically prefixed with '##' or specified as GLOBAL TEMPORARY in the table definition.

6. Session Management:

   - Temporary tables: Managed by the session that created them.

   - Global temporary tables: Managed at a global level, available to all sessions.

Here are examples illustrating the creation and usage of both temporary tables and global temporary tables in Oracle:

1. Temporary Table:

-- Create a temporary table

CREATE GLOBAL TEMPORARY TABLE temp_table (

    id NUMBER,

    name VARCHAR2(50)

);

-- Insert data into the temporary table

INSERT INTO temp_table (id, name) VALUES (1, 'John');

INSERT INTO temp_table (id, name) VALUES (2, 'Jane');

-- Query the temporary table within the same session

SELECT * FROM temp_table;

2. Global Temporary Table:

-- Create a global temporary table

CREATE GLOBAL TEMPORARY TABLE global_temp_table (

    id NUMBER,

    name VARCHAR2(50)

) ON COMMIT DELETE ROWS; -- Specifies that data is deleted at the end of the transaction

-- Insert data into the global temporary table

INSERT INTO global_temp_table (id, name) VALUES (1, 'Alice');

INSERT INTO global_temp_table (id, name) VALUES (2, 'Bob');

-- Query the global temporary table from another session

-- Each session sees its own data

SELECT * FROM global_temp_table;

In both cases, the tables are used similarly to regular tables for data insertion, retrieval, and manipulation. However, the temporary table is session-specific, while the global temporary table persists across sessions but is session-specific in terms of data visibility.

Here are five frequently asked questions (FAQs) about temporary tables and global temporary tables in Oracle:-

1. Can temporary tables and global temporary tables be indexed?

   - Yes, both temporary tables and global temporary tables can be indexed like regular tables. Indexes can improve query performance when accessing data from these tables.

2. What happens if a session crashes while using temporary tables or global temporary tables?

   - If a session crashes, Oracle automatically cleans up the temporary tables associated with that session. For global temporary tables, data from the crashed session is automatically removed, ensuring data isolation and integrity.

3. Can I perform DDL operations (e.g., ALTER TABLE) on temporary tables and global temporary tables?

   - No, DDL operations that modify the structure of temporary tables are not allowed. This includes altering columns, dropping columns, or renaming the table itself. However, you can perform DML operations like INSERT, UPDATE, and DELETE.

4. Are temporary tables and global temporary tables visible to other sessions?

   - Temporary tables are visible only within the session or transaction where they are created. Global temporary tables are visible to all sessions, but each session sees its own data, providing data isolation.

5. Do temporary tables and global temporary tables support constraints like primary keys or foreign keys?

   - Yes, both temporary tables and global temporary tables support constraints such as primary keys, foreign keys, unique constraints, and check constraints. These constraints help enforce data integrity and consistency within the tables.