Oracle全局临时表和私有临时表

jiawang

Oracle 全局临时表以下简称GTT（Global Temporary Table），私有临时表以下简称PTT（Private Temporary Table）。

GTT和PTT的主要区别如下：
    GTT更早。GTT是8i就有的特性，PTT是18c才有的特性。
    GTT和PTT的数据都是会话私有的，而且会话结束后数据就没有了，但GTT的定义是全局的。
    事务提交时，GTT可以选择是否保留数据，PTT可以选择是否保留定义。
    GTT的表定义是所有会话共享的；PTT的表定义是会话私有的。


临时表的语法帮助参见官方文档。
https://docs.oracle.com/en/datab ... f/CREATE-TABLE.html


GLOBAL TEMPORARY

    Specify GLOBAL TEMPORARY to create a temporary table, whose definition is visible to all sessions with appropriate privileges. The data in a temporary table is visible only to the session that inserts the data into the table.
    When you first create a temporary table, its metadata is stored in the data dictionary, but no space is allocated for table data. Space is allocated for the table segment at the time of the first DML operation on the table. The temporary table definition persists in the same way as the definitions of regular tables, but the table segment and any data the table contains are either session-specific or transaction-specific data. You specify whether the table segment and data are session- or transaction-specific with the ON COMMIT clause.
    You can perform DDL operations (such as ALTER TABLE, DROP TABLE, CREATE INDEX) on a temporary table only when no session is bound to it. A session becomes bound to a temporary table with an INSERT operation on the table. A session becomes unbound to a temporary table with a TRUNCATE statement or at session termination, or, for a transaction-specific temporary table, by issuing a COMMIT or ROLLBACK statement.

PRIVATE TEMPORARY

    Specify PRIVATE TEMPORARY to create a private temporary table.
    A private temporary table differs from a temporary table in that its definition and data are visible only within the session that created it. Use the ON COMMIT clause to define the scope of a private temporary table: either transaction or session. The ON COMMIT clause used with the keywords DROP DEFINITION creates a transaction-specific table whose data and definition are dropped when the transaction commits. This is the default behavior. The ON COMMIT clause used with keywords PRESERVE DEFINITION creates a session-specific table whose definition is preserved when the transaction commits. See here for usage details of theON COMMIT clause.
    Three DDL statements are supported for private temporary tables: CREATE, DROP, and TRUNCATE.

非SYS用户才可创建临时表