[20181112]Private Temporary Tables Oracle Database 18C.txt

[20181112]Private Temporary Tables Oracle Database 18C.txt

--//18C出现了一种新的临时表，称为私有临时表。它们是在事务或会话结束时删除的临时数据库对象。私有临时表存储在内存中，每个

--//临时表只对创建它的会话可见。

--//语法如下：

CREATE PRIVATE TEMPORARY TABLE .... ON COMMIT DROP DEFINITION ;

or

CREATE PRIVATE TEMPORARY TABLE .... ON COMMIT PRESERVE DEFINITION;

DROP DEFINITION     : This creates a private temporary table that is transaction specific. All data in the table is

                      lost, and the table is dropped at the end of transaction.

PRESERVE DEFINITION : This creates a private temporary table that is session specific. All data in the table is lost,

                      and the table is dropped at the end of the session that created the table.

--//Private temporary table name 定义使用前缀按照参数private_temp_table_prefix

--//简单通过例子说明：

1.环境：

SQL> select banner_full from v$version;

BANNER_FULL

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

Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production

Version 18.3.0.0.0

SQL> set linesize 2000

SQL> show parameter private

NAME                      TYPE    VALUE

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

private_temp_table_prefix string  ORA$PTT_

--//session 1:

CREATE PRIVATE TEMPORARY TABLE temp_test

( 

  id number;

  create_date date

) 

ON COMMIT PRESERVE DEFINITION;

*

ERROR at line 1:

ORA-00903: invalid table name

--//前缀必须使用private_temp_table_prefix。

CREATE PRIVATE TEMPORARY TABLE ORA$PTT_temp_test

( 

  id number,

  create_date date

) 

ON COMMIT DROP DEFINITION;

Table created.

SQL> SELECT sid, serial#, owner, table_name, duration  FROM user_private_temp_tables;

       SID    SERIAL# OWNER                          TABLE_NAME                     DURATION

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

       392      63118 SYSTEM                         ORA$PTT_TEMP_TEST              TRANSACTION

2.继续测试：

--//别的会话也可以定义相同的表名：

--//session 2:

SQL> SELECT sid, serial#, owner, table_name, duration FROM user_private_temp_tables;

no rows selected

--//别的会话看不到对方建立的私有临时表.

CREATE PRIVATE TEMPORARY TABLE ORA$PTT_temp_test

( 

  id number,

  c_date date

) 

ON COMMIT DROP DEFINITION;

Table created.

--//注:字段c_date与前面不同.实际上表结构完全不同也可以的.

SQL> SELECT sid, serial#, owner, table_name, duration FROM user_private_temp_tables;

       SID    SERIAL# OWNER                          TABLE_NAME                     DURATION

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

       196       7874 SYSTEM                         ORA$PTT_TEMP_TEST              TRANSACTION

--//视图user_private_temp_tables仅仅看到自己定义的.

SQL> SELECT sid, serial#, owner, table_name, duration   FROM dba_private_temp_tables;

       SID    SERIAL# OWNER                          TABLE_NAME                     DURATION

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

       196       7874 SYSTEM                         ORA$PTT_TEMP_TEST              TRANSACTION

       392      63118 SYSTEM                         ORA$PTT_TEMP_TEST              TRANSACTION

--//按照定义ON COMMIT DROP DEFINITION的特点，应该是commit后删除该表。

--//session 1:

SQL> insert into ORA$PTT_temp_test values (1,sysdate);

1 row created.

SQL> select count(*) from ORA$PTT_temp_test;

  COUNT(*)

----------

         1

SQL> commit ;

Commit complete.

SQL> select count(*) from ORA$PTT_temp_test;

select count(*) from ORA$PTT_temp_test

                     *

ERROR at line 1:

ORA-00942: table or view does not exist

--//可以发现提交后,表定义消失.

3.测试 ON COMMIT PRESERVE DEFINITION:

--//按照定义就是提交后保持表定义，直到退出会话才消失。

--//session 1:

CREATE PRIVATE TEMPORARY TABLE ORA$PTT_temp_test

( 

  id number,

  create_date date

) 

ON COMMIT PRESERVE DEFINITION;

Table created.

SQL> insert into ORA$PTT_temp_test values (1,sysdate);

1 row created.

SQL> commit ;

Commit complete.

SQL> select count(*) from ORA$PTT_temp_test;

  COUNT(*)

----------

         1

--//提交后,表定义还在.继续dml操作:

SQL>  insert into ORA$PTT_temp_test values (2,sysdate);

1 row created.

SQL> commit ;

Commit complete.

SQL> select * from ORA$PTT_temp_test ;

        ID CREATE_DATE

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

         1 12-NOV-18

         2 12-NOV-18

--//session 2:

SQL> SELECT sid, serial#, owner, table_name, duration   FROM dba_private_temp_tables;

       SID    SERIAL# OWNER                          TABLE_NAME                     DURATION

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

       196       7874 SYSTEM                         ORA$PTT_TEMP_TEST              TRANSACTION

       392      63118 SYSTEM                         ORA$PTT_TEMP_TEST              SESSION

--//可以发现ON COMMIT PRESERVE DEFINITION;的表DURATION=SESSION.

--//        ON COMMIT DROP DEFINITION;的表DURATION=TRANSACTION.

--//全部退出后,查询:

SQL>  SELECT sid, serial#, owner, table_name, duration   FROM dba_private_temp_tables;

no rows selected

--//这样私有临时表全部看不见了.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/267265/viewspace-2219559/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/267265/viewspace-2219559/