about GLOBAL TEMPORARY TABLES

1. 1.       创建SQL语句

CREATE GLOBAL TEMPORARY TABLE tablename (columns) [ ON COMMIT PRESERVE | DELETE ROWS ]

SQL> create global temporary table emp_temp(eno number) on commit delete rows;

-- transaction level duration，事务级别，此为默认选项

SQL> create global temporary table emp_temp(eno number) on commit preserve rows;

-- session level duration，会话级别

1. 2.       隔离性：

数据只在会话或者事务级别可见。不同用户可以使用同一个临时表，但是看到的都是各自的数据。

1. 3.       表上可以创建索引、视图、触发器等对象。
2. 4.       索引只有在临时表是empty时可创建。
3. 5.       临时表不产生数据的redo，但是会生成undo的redo。
4. 6.       临时表目前只支持GLOBAL的，所以创建语句为create global temporary table XXX。
5. 7.       使用truncate只对当前会话有效。
6. 8.       不能export/import表上的数据，只能导入导出表定义。
7. 9.       临时段在第一次insert或CATS时产生。

缺点：

1. 1.       表定义不能自动drop。
2. 2.       临时表目前只支持GLOBAL。
3. 3.       只有无会话时才能DDL，否则可能报错：

ORA-14452: attempt to create, alter or drop an index on temporary table already in use

1. 4.       临时表上默认不收集统计信息，如果需要收集统计信息，首先要确保临时表属性为ON COMMIT PRESERVE ROWS。

收集统计信息命令：analyze table table_name compute statistics;

============================================================================================================

OCP考题

Which three statements are true about GLOBAL TEMPORARY TABLES?

A) A GLOBAL TEMPORARY TABLE cannot have PUBLIC SYNONYM.

B) A GLOBAL TEMPORARY TABLE can have multiple indexes

C) A GLOBAL TEMPORARY TABLE can be referenced in the defining query of a view.

D) Data Manipulation Language (DML) on GLOBAL TEMPORARY TABLES generates no REDO.

E) A GLOBAL TEMPORARY TABLE can have only one index.

F) A trigger can be created on a GLOBAL TEMPORARY TABLE

Correct Answer: BCF

解析：D会产生redo，可以通过查看统计信息，打开set autotrace on,然后对临时表执行dml操作，会有redo size

全局临时表： GLOBAL TEMPORARY TABLES

解析：全局临时表跟普通表一样，可以创建索引、视图、触发器等等，dml 操作同样也会产生 redo。临时表创建的目的就是为了测试，如果很多操作不支持，那就失去了测试的意义。

Which three statements are true about GLOBAL TEMPORARY TABLES?

A) A DELETE command on GLOBAL TEMPORARY TABLE cannot be rolled back

B) GLOBAL TEMPORARY TABLE rows inserted by a session are available to any other session whose user has been granted selected on the table

C) Any GLOBAL TEMPORARY TABLE rows existing at session termination will be deleted

D) A GLOBAL TEMPORARY TABLE's definition is available to multiple sessions;

E) GLOBAL TEMPORARY TABLE space allocation occurs at session start.

F) A TRUNCATE command issues in a session causes all rows in a GLOBAL TEMPORARY TABLE for the issuing session to be deleted.

Correct Answer: CDF

Which three statements are true about GLOBAL TEMPORARY TABLES?

A) GLOBAL TEMPORARY TABLE rows inserted by a session are available to any other session whose user has been granted select on the table.

B) A TRUNCATE command issued in a session causes all rows In a GLOBAL TEMPORARY TABLE for the issuing session to be deleted.

C) A DELETE command on a GLOBAL TEMPORARY TABLE cannot be rolled back.

D) A GLOBAL TEMPORARY TABLE's definition is available to multiple sessions.

E) Any GLOBAL TEMPORARY TABLE rows existing at session termination will be deleted.

F) GLOBAL TEMPORARY TABLE space allocation occurs at session start.

Correct Answer:BDF