Hello ,
I am getting the error “ORA-14451: unsupported feature with temporary table” when I am trying to create a private temp table. I tested it in Oracle 19.3.0.0.0.
My use case : I am logged in as a user RCU_USER (sqldeveloper) and is creating PTT using the following query
CREATE PRIVATE TEMPORARY TABLE DEMO_USER.ORA$PTT_ET_CORP_ALERT_FORMATTED_MSG_TEMP3 ON COMMIT PRESERVE DEFINITION AS SELECT COD_ALERT_NO, COD_ALERT_UID, COD_ALERT_MIS_UID FROM DEMO_USER.ET_CORP_ALERT_FORMATTED_MSG WHERE 1=0;
My base table DDL
CREATE TABLE DEMO_USER.ET_CORP_ALERT_FORMATTED_MSG
( COD_ALERT_NO NUMBER NOT NULL ENABLE,
COD_ALERT_UID VARCHAR2(120) NOT NULL ENABLE,
COD_ALERT_MIS_UID VARCHAR2(120),
COD_ALERT_CONSOL_UID VARCHAR2(120),
COD_ALERTLOG_UID VARCHAR2(120) NOT NULL ENABLE,
COD_ALERT_TEMPL_ID VARCHAR2(5) NOT NULL ENABLE,
COD_ALERT_SEQ_NO VARCHAR2(120),
COD_ACCT_NO CHAR(16),
COD_CUST_ID VARCHAR2(20),
TXT_EMAIL_ID VARCHAR2(2000),
TXT_MOBILE_NO VARCHAR2(2000),
COD_ALERT_ID VARCHAR2(10) NOT NULL ENABLE,
DAT_PROCESS DATE NOT NULL ENABLE,
DAT_TXN DATE NOT NULL ENABLE,
FLG_CONSOLIDATED_ALERT CHAR(1),
TXT_ALERT_DESC VARCHAR2(50),
TXT_ALERT_META_DATA VARCHAR2(256),
TXT_ALERT_MSG_DATA VARCHAR2(2000),
TXT_ALERT_FORMATTED_MSG CLOB,
TXT_ATTCH_HDR_DATA VARCHAR2(1000),
TXT_ATCH_META_DATA VARCHAR2(2000),
TXT_ATCH_FORMATTED_MSG CLOB,
FLG_TXT_ATTCH VARCHAR2(2),
COD_ATTCH_FORMAT VARCHAR2(5),
TXT_ATTCH_EXTN VARCHAR2(5),
TXT_ATTCH_DELIM VARCHAR2(3),
COD_ALERT_STATUS CHAR(1),
TXT_ERR_MSG VARCHAR2(256),
CTR_RETRY NUMBER,
COD_EOD_MIS_STATUS CHAR(1),
CTR_MIS_RETRY NUMBER,
NAM_ATTCH_FILE VARCHAR2(100),
DAT_ALERT_RESP DATE,
COD_ENTITY_VPD NUMBER(5,0) DEFAULT NVL(sys_context('CLIENTCONTEXT','entity_co
de'),11) NOT NULL ENABLE,
TXT_ALERT_MSG_HDR VARCHAR2(1000)
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 64 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE RCU_USER
LOB (TXT_ALERT_FORMATTED_MSG) STORE AS SECUREFILE (
TABLESPACE RCU_USER ENABLE STORAGE IN ROW CHUNK 8192
NOCACHE LOGGING NOCOMPRESS KEEP_DUPLICATES
STORAGE(INITIAL 106496 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT))
LOB (TXT_ATCH_FORMATTED_MSG) STORE AS SECUREFILE (
TABLESPACE RCU_USER ENABLE STORAGE IN ROW CHUNK 8192
NOCACHE LOGGING NOCOMPRESS KEEP_DUPLICATES
STORAGE(INITIAL 106496 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT))
I can run the select on the table successfully
SELECT COD_ALERT_NO, COD_ALERT_UID, COD_ALERT_MIS_UID FROM DEMO_USER.ET_CORP_ALERT_FORMATTED_MSG;
Please provide your feedback.