global temporary table

From:http://www.oracle-base.com/articles/8i/TemporaryTables.php
[color=black]Applications often use some form of temporary data store for processes that are to complicated to complete in a single pass. Often, these temporary stores are defined as database tables or PL/SQL tables. In Oracle 8i, the maintenance and management of temporary tables can be delegated to the server by using Global Temporary Tables.[/color][color=blue][b]
=>应用者经常需要用临时数据组成的一些表，store一些用单一的方式很难达到的过程。通常，这些临时性stores被定义为数据库表或pl/sql 表。在oracle 8i,临时表的维护和管理通过用全局临时表来委派给server.[/b][/color][color=black]
Creation of Global Temporary Tables
The data in a global temporary table is private, such that data inserted by a session can only be accessed by that session. The session-specific rows in a global temporary table can be preserved for the whole session, or just for the current transaction. The ON COMMIT DELETE ROWS clause indicates that the data should be deleted at the end of the transaction.[/color][color=blue][b]
=>创建全局临时表
在全局临时表中的数据是私有的，在一个session中插入的数据只能在同一个session中被获取。在全局临时表中指定session里的行被保存在整个session或者是当前的transaction中。on commit delete rows语句说明在session中的数据应该在transaction结束时被delete.例如：
[/b][/color]

CREATE GLOBAL TEMPORARY TABLE my_temp_table (
  column1  NUMBER,
  column2  NUMBER
) ON COMMIT DELETE ROWS;

In contrast, the ON COMMIT PRESERVE ROWS clause indicates that rows should be preserved until the end of the session.
[color=blue][b]=>相反，on commit preserve rows 语句指出直至session结束data应该保存在内存中。[/b][/color]

CREATE GLOBAL TEMPORARY TABLE my_temp_table (
  column1  NUMBER,
  column2  NUMBER
) ON COMMIT PRESERVE ROWS;

Miscellaneous Features
If the TRUNCATE statement is issued against a temporary table, only the session specific data is trucated. There is no affect on the data of other sessions.
[color=blue][b]=>如果truncate一个临时表时，只是对当前session里的数据trucate,而对其它session里的数据没有任何影响。[/b][/color]
Data in temporary tables is automatically delete at the end of the database session, even if it ends abnormally.
[color=blue][b]=>即使session以不正常结束,临时表中的数据也会在session结束时候被自动删除。[/b][/color]
Indexes can be created on temporary tables. The content of the index and the scope of the index is that same as the database session.
Views can be created against temporary tables and combinations of temporary and permanent tables.
[color=blue][b]=>index能被创建在临时表上，索引的内容和范围与session的一样。view能被创建在临时表，以及临时表与永久表的结合表上。(这里所谓结合表，应该是联合查询取得的数据表)[/b][/color]
DML locks are not acquired on the data of the temporary tables. The LOCK statement has no effect on a temporary table because each session has its own private data.
[color=blue][b]=>DML锁对于临时表的数据来说没有影响，因为每个Session都有它私有的数据。[/b][/color]
Temporary tables can have triggers associated with them.
Export and Import utilities can be used to transfer the table definitions, but no data rows are processed.
[color=blue][b]=>临时表有trigger与之关联。导入，导出功能也可以放在表定义的转换，但是没有数据。[/b][/color]

Specify GLOBAL TEMPORARY to indicate that the table is temporary and that its definition is visible to all sessions. The data in a temporary table is visible only to the session that inserts the data into the table.
特定全局临时表---对于所有的Session都可见，并且临时表中的数据只对插入数据到表中的Session可视。
Restrictions:
[color=blue][b]=>局限性:[/b][/color]
1.Temporary tables cannot be partitioned, index-organized, or clustered.
[color=blue][b]=>临时表不能被分区，索引化和群集。[/b][/color]
2.You cannot specify any referential integrity (foreign key) constraints on temporary tables.
[color=blue][b]=>不能在临时表上指定任何完整性约束。[/b][/color]
3.Temporary tables cannot contain columns of nested table or varray type.
[color=blue][b]=>临时表中不能包含嵌套表或变量数组之类的列。[/b][/color]
4.You cannot specify the following clauses of the LOB_storage_clause: TABLESPACE, storage_clause, LOGGING or NOLOGGING, MONITORING or NOMONITORING, or LOB_index_clause.
[color=blue][b]=>不能指定如下clause:TABLESPACE, storage_clause, LOGGING or NOLOGGING, MONITORING or NOMONITORING, or LOB_index_clause.[/b][/color]
Parallel DML and parallel queries are not supported for temporary tables. (Parallel hints are ignored. Specification of the parallel_clause returns an error.)
[color=blue][b]=>相应的DML和queries也不支持临时表。[/b][/color]
5.You cannot specify the segment_attributes_clause, nested_table_storage_clause, or parallel_clause.
[color=blue][b]=>同时在临时表上指定segment_attributes_clause, nested_table_storage_clause, or parallel_clause。[/b][/color]6.Distributed transactions are not supported for temporary tables.
[color=blue][b]=>临时表也不支持分布式事务。
以下是一个Global Temporary Table的实例:[/b][/color]

create global temporary table T_FORM4_POL_CHG_TMP
(
  POLICY_ID     NUMBER(10) not null,
  CHANGE_ID     NUMBER(10),
  SERVICE_ID    NUMBER(10),
  FINISH_TIME   DATE,
  CASE_ID       NUMBER(10),
  WITHDRAW_TIME DATE,
  CHANGE_STATUS VARCHAR2(2)
);

临时表的介绍：
Data in a temporary table is private to the session. Each session can only see and modify its own data.
[color=blue][b]=>在临时表中的数据只是对当前session可见，每个session仅能访问和修改属于它的数据。[/b][/color]
DML statements on temporary tables do not generate redo logs for the data changes. However, undo logs for the data and redo logs for the undo logs are generated. Data from the temporary table is automatically dropped in the case of session termination, either when the user logs off or when the session terminates abnormally such as during a session or instance crash.
[color=blue][b]=>对临时表的DML操作，不会对数据变化生成重做日志。然而，可以生成对数据的撤销日志和对撤销日志的重做日志。临时表中的数据可以在session中断时被自动drop，如当用户退出或者Session异常中断.[/b][/color]
You can create indexes for temporary tables using the CREATE INDEX statement. Indexes created on temporary tables are also temporary, and the data in the index has the same session or transaction scope as the data in the temporary table.

You can create views that access both temporary and permanent tables. You can also create triggers on temporary tables.
[color=blue][b]=>你能在临时表上创建索引，在临时表上的索引也是临时的，在索引上的数据同临时表中的数据一样，在相同的session或事务中。同时，也能在临时表上创建视图和触发器。[/b][/color]

Segment Allocation 段的分配
Temporary tables use temporary segments. Unlike permanent tables, temporary tables and their indexes do not automatically allocate a segment when they are created. Instead, segments are allocated when the first INSERT (or CREATE TABLE AS SELECT) is performed. This means that if a SELECT, UPDATE, or DELETE is performed before the first INSERT, then the table appears to be empty.
[color=blue][b]=>临时表用临时段。与永久表不同，临时表和属于它的索引在被创建时候，是无法自动分配段空间。取而代之，在第一次插入数据的时候，段空间才会被分配。换句话说，在Select,Update,delete执行前，必须做insert操作，因为那时数据表都为空。[/b][/color]
You can perform DDL statements (ALTER TABLE, DROP TABLE, CREATE INDEX, and so on) on a temporary table only when no session is currently bound to it. A session gets bound to a temporary table when an INSERT is performed on it. The session gets unbound by a TRUNCATE, at session termination, or by doing a COMMIT or ABORT for a transaction-specific temporary table.
[color=blue][b]=>仅当临时表不与临时表关联时候，才可以对它之行DDL操作（ALTER TABLE,DROP TABLE,CREATE INDEX等操作）。[/b][/color]
Temporary segments are deallocated at the end of the transaction for transaction-specific temporary tables and at the end of the session for session-specific temporary tables.
[color=blue][b]=>在Session结束或者Transaction结束时候临时段才会被收回。[/b][/color]