如何在Oracle 19中创建私人临时桌子？

发布于 2025-02-08 10:41:02 字数 939 浏览 2 评论 0原文

我正在运行Oracle数据库19C Enterprise Edition版本19.0.0.0.0-码头容器中的生产。

我使用创建会话创建了一个用户，并创建表系统特权。用户也有配额无限。

CREATE USER airflow IDENTIFIED BY pass;
GRANT CREATE SESSION TO airflow;
GRANT CREATE TABLE TO airflow;
ALTER USER airflow QUOTA UNLIMITED ON USERS;

使用该用户，我尝试创建一个带有以下查询的私人临时表：

CREATE PRIVATE TEMPORARY TABLE ora$ppt_temp1 (
    name varchar2(7),
    age int,
    employed int
    ) ON COMMIT PRESERVE DEFINITION;

我正在使用SQLalchemy 1.3.24访问Python 3.9.13上的数据库。我会收到以下错误：

sqlalchemy.exc.databaseerror ：（ cx_oracle.databaseerror）ora-00903：无效的表名称

我还从dbeaver运行dbeaver时，我还能获取ora-00903 。我已经检查了private_temp_table_prefix，并将其设置为ora $ ptt _的默认值。我已经阅读了Oracle 19C文档和几个堆栈溢出问题，但看不到我在这里缺少什么。

我怀疑我需要添加或修改以进行这项工作需要一些特权。

原文

I am running Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production in a Docker container.

I created a user with CREATE SESSION and CREATE TABLE system privileges. User also has QUOTA UNLIMITED.

CREATE USER airflow IDENTIFIED BY pass;
GRANT CREATE SESSION TO airflow;
GRANT CREATE TABLE TO airflow;
ALTER USER airflow QUOTA UNLIMITED ON USERS;

With that user I attempted to create a private temporary table with the following query:

CREATE PRIVATE TEMPORARY TABLE ora$ppt_temp1 (
    name varchar2(7),
    age int,
    employed int
    ) ON COMMIT PRESERVE DEFINITION;

I am accessing the database on Python 3.9.13 using SQLAlchemy 1.3.24.
I get the following error:

sqlalchemy.exc.DatabaseError: (cx_Oracle.DatabaseError) ORA-00903: invalid table name

I also get ORA-00903 when running the query from DBeaver. I have checked the private_temp_table_prefix and it is set to the default value of ORA$PTT_. I have read through the Oracle 19c documentation and several stack overflow questions and cannot see what I am missing here.

I suspect that there is some privilege I need to add or modify to make this work.

分享到QQ

分享到微博