说明

私有临时表是18c中新引入的特性，但是在实验时无法正常创建私有临时表，报错http://www.cndba.cn/Expect-le/article/2949

SQL> CREATE PRIVATE TEMPORARY TABLE ora$ptt_my_temp_table (
  id           NUMBER,
  description  VARCHAR2(20)
)
ON COMMIT DROP DEFINITION;  2    3    4    5
CREATE PRIVATE TEMPORARY TABLE ora$ptt_my_temp_table (
*
ERROR at line 1:
ORA-14451: unsupported feature with temporary table

环境：http://www.cndba.cn/Expect-le/article/2949

SQL> show parameter compati

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
compatible                           string      18.0.0
noncdb_compatible                    boolean     FALSE
plsql_v2_compatibility               boolean     FALSE
SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
BANNER_FULL
--------------------------------------------------------------------------------
BANNER_LEGACY
--------------------------------------------------------------------------------
    CON_ID
----------
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
         0

BANNER
--------------------------------------------------------------------------------
BANNER_FULL
--------------------------------------------------------------------------------
BANNER_LEGACY
--------------------------------------------------------------------------------
    CON_ID
----------

但是在Oracle LIVE SQL上可以正常执行
https://livesql.oracle.com

原因

经过不断测试终于找到了原因，就是不要使用sys用户来创建.

• 1.使用非sys用户

[oracle@18c-new admin]$ sqlplus lei/oracle@leipdb
SQL*Plus: Release 18.0.0.0.0 - Production on Tue Aug 14 14:04:17 2018
Version 18.3.0.0.0
Copyright (c) 1982, 2018, Oracle.  All rights reserved.
Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0

SQL> show pdbs
SP2-0382: The SHOW PDBS command is not available
SQL> show con_name

CON_NAME
------------------------------
LEIPDB
SQL> create private temporary table ora$ptt_a
(
 n number
) on commit preserve definition;  2    3    4

Table created.

• 2.使用system用户

  http://www.cndba.cn/Expect-le/article/2949
  http://www.cndba.cn/Expect-le/article/2949

    SQL> conn system/oracle
    Connected.
    SQL> CREATE PRIVATE TEMPORARY TABLE ORA$PTT_sales_ptt_transaction
        (time_id      DATE,
         amount_sold  NUMBER(10,2))
       ON COMMIT DROP DEFINITION;  2    3    4
  
    Table created.
  

• 3.使用SYS用户

    SQL> conn sys/oracle as sysdba
    Connected.
    SQL> CREATE PRIVATE TEMPORARY TABLE ORA$PTT_sales_ptt_transaction
        (time_id      DATE,
         amount_sold  NUMBER(10,2))
       ON COMMIT DROP DEFINITION;  2    3    4
    CREATE PRIVATE TEMPORARY TABLE ORA$PTT_sales_ptt_transaction
    *
    ERROR at line 1:
    ORA-14451: unsupported feature with temporary table
  

• 4.使用具有sysdba权限的用户http://www.cndba.cn/Expect-le/article/2949

    SQL> grant sysdba to lei;
  
    Grant succeeded.
  
    SQL> conn lei/oracle@leipdb
    Connected.
    SQL> CREATE PRIVATE TEMPORARY TABLE ORA$PTT_test
        (time_id      DATE,
         amount_sold  NUMBER(10,2))
       ON COMMIT DROP DEFINITION;  2    3    4
  
    Table created.
  

  总结

  可以看到只有SYS用户无法正常创建私有临时表。

  http://www.cndba.cn/Expect-le/article/2949

版权声明：本文为博主原创文章，未经博主允许不得转载。

ORA-14451: unsupported feature with temporary table