SQL> create global temporary table temp01(id number) on commit delete rows;

Table created.

SQL> insert into temp01 values (1) ;

1 row created.

SQL> select * from temp01;

        ID

----------

         1

SQL> commit;

Commit complete.

SQL> select * from temp01;

no rows selected

SQL> select table_name ,temporary from user_tables;

TABLE_NAME                     T

------------------------------ -

TEST01                         N

TEMP01                         Y

SQL> create global temporary table temp02 (id number) on commit preserve rows;

Table created.

SQL> insert into temp02 values(1);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from temp02;

        ID

----------

         1

SQL> conn / as sysdba

Connected.

SQL> conn test/test

Connected.

SQL> select * from temp02;

no rows selected

SQL> alter table temp01 add constraint pk_temp01 primary key(id);

Table altered.

SQL> alter table temp02 add constraint pk_temp02 primary key(id);

Table altered.

SQL> desc temp01

 Name                                                              Null?    Type

 ----------------------------------------------------------------- -------- --------------------------------------------

 ID                                                                NOT NULL NUMBER

SQL> alter table temp02 drop constraint pk_temp02;

Table altered.

SQL> desc temp02

 Name                                                              Null?    Type

 ----------------------------------------------------------------- -------- --------------------------------------------

 ID                                                                         NUMBER

SQL> insert into temp02 values(1);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from temp02;

        ID

----------

         1

SQL> alter table temp02 add constraint pk_temp02 primary key(id);

alter table temp02 add constraint pk_temp02 primary key(id)

*

ERROR at line 1:

ORA-14450: attempt to access a transactional temp table already in use

SQL> select username from v$sort_usage;

USERNAME

------------------------------

TEST

SQL> select * from temp02;

        ID

----------

         1

SQL> insert into temp01 values(1);

1 row created.

SQL> select username,SQLADDR,SEGTYPE ,BLOCKS, SEGBLK#  from v$sort_usage;

USERNAME                       SQLADDR  SEGTYPE       BLOCKS    SEGBLK#

------------------------------ -------- --------- ---------- ----------

TEST                           29ABA460 DATA             128       1801

TEST                           29ABA460 DATA             128       1673

TEST                           29ABA460 INDEX            128       1545

临时表的不足之处：

1.不支持lob对象，这也许是设计者基于运行效率的考虑，但实际应用中确实需要此功能时就无法使用临时表了。

2.不支持主外键关系

特性和性能(与普通表和视图的比较) 

1.临时表只在当前连接内有效 

2.临时表不建立索引,所以如果数据量比较大或进行多次查询时,不推荐使用 

3.数据处理比较复杂的时候时表快,反之视图快点 

4.在仅仅查询数据的时候建议用游标: open cursor for 'sql clause';