关于Oracle 18c 创建私有临时表报错ORA-14451: unsupported feature with temporary table

说明

私有临时表是18c中新引入的特性，但是在实验时无法正常创建私有临时表，报错

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

环境：

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

关于私有临时表，请查看：https://blog.csdn.net/qianglei6077/article/details/87074562

原因

经过不断测试终于找到了原因，就是不要使用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用户

	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权限的用户

  	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用户无法正常创建私有临时表。