DOITLIKEITSYOURJOB - Global Temporary Tables

Working with Global Temporary Tables in Oracle

Global Temporary Tables (GTTs) in Oracle are session-specific or transaction-specific tables that allow you to store temporary data. Here’s how you can create, manage, and drop them, as well as the required permissions.

Key Characteristics of Global Temporary Tables:

Session- or Transaction-specific: Data is visible only within the session or transaction that created it.
Schema-wide: The table definition is shared, but data is private to each session.
Automatic Data Purging: Data is automatically purged at the end of the session or transaction.
Reduced Redo and Undo Logging: GTTs improve performance by reducing redo log generation.

BEGIN EXECUTE IMMEDIATE 'TRUNCATE TABLE tmp_test'; EXECUTE IMMEDIATE 'DROP TABLE tmp_test';EXCEPTION WHEN OTHERS THEN IF SQLCODE != -942 THEN RAISE; END IF;END;/
CREATE GLOBAL TEMPORARY TABLE tmp_test ON COMMIT PRESERVE ROWSAS( Select TO_CHAR(TRUNC(SYSDATE, 'Q'), 'YYYYMMDD') QTR_LOADID FROM DUAL);

Creating a Global Temporary Table

Here’s how to create a GTT in Oracle:

CREATE GLOBAL TEMPORARY TABLE temp_table ( column1 datatype, column2 datatype) ON COMMIT [DELETE | PRESERVE] ROWS;

ON COMMIT DELETE ROWS: Data is deleted at the end of each transaction.

ON COMMIT PRESERVE ROWS: Data is retained for the session.

Example of a Transaction-Specific GTT:

CREATE GLOBAL TEMPORARY TABLE temp_orders ( order_id NUMBER, customer_id NUMBER, order_total NUMBER) ON COMMIT DELETE ROWS;

Inserting Data into a GTT:

INSERT INTO temp_orders (order_id, customer_id, order_total)VALUES (1, 101, 250.75);

Dropping a GTT:

DROP TABLE temp_orders;

Permissions Required

CREATE TABLE Privilege: Required to create a table in your schema.
GRANT CREATE TABLE TO username;

Tablespace Quota: Ensure that you have sufficient quota in the tablespace.
ALTER USER username QUOTA UNLIMITED ON tablespace_name;

Optional - CREATE ANY TABLE: To create GTTs in other schemas.
GRANT CREATE ANY TABLE TO username;

Optional - DROP ANY TABLE: To drop

1. Check System Privileges for a User

To check if a user has system privileges like CREATE TABLE, CREATE ANY TABLE, etc., you can query the DBA_SYS_PRIVS or USER_SYS_PRIVS view.

For a specific user (using DBA_SYS_PRIVS, available to DBAs):
SELECT privilegeFROM dba_sys_privsWHERE grantee = 'USERNAME';

2. Check Object Privileges for a User

To check whether a user has specific object privileges (like SELECT, INSERT, UPDATE, DELETE on tables), you can query the DBA_TAB_PRIVS or USER_TAB_PRIVS view.

SELECT table_name, privilegeFROM dba_tab_privsWHERE grantee = 'USERNAME';

3. Check Role Privileges for a User

If a user has been granted roles, which can include the ability to CREATE TABLE, you can check the roles granted using the DBA_ROLE_PRIVS or USER_ROLE_PRIVS view.

SELECT granted_roleFROM dba_role_privsWHERE grantee = 'USERNAME';

4. Check if the User Has a Quota on a Tablespace

For creating tables, the user must also have a sufficient quota on the tablespace where the table will be created. You can check tablespace quotas using the following query:

SELECT tablespace_name, max_bytesFROM dba_ts_quotasWHERE username = 'USERNAME';

Example: Checking if a User Can Create Tables

You can combine the above queries to check if a user has the CREATE TABLE privilege and sufficient tablespace quota:

SELECT privilegeFROM dba_sys_privsWHERE grantee = 'USERNAME'AND privilege = 'CREATE TABLE';