ORA-28116: insufficient privileges to do direct path access ORACLE 报错 故障修复 远程处理

文档解释

ORA-28116: insufficient privileges to do direct path access

Cause: Users with insufficient privileges attempting to do direct path access of tables with fine grain access control policies.

Action: Ask the database administrator to do the operation. Note that users can work with security administrator to temporarily drop/disable the policies at time of export, import, or load, but this has security implication, and thus access of the database must be controlled carefully.

ORA-28116: insufficient privileges to do direct path access，是Oracle数据库在执行direct path加载数据时报出的错误，提示用户对对象没有足够的权限。

官方解释

常见案例

1. 在SEQUENCE权限不够的情况下，用户使用direct path加载会报错：ORA-28116: insufficient privileges to do direct path access

2. Append会反复的报错：ORA-28116: insufficient privileges to do direct path access

一般处理方法及步骤

1. 为用户授权SELECT ANY TABLE 和 INSERT ANY TABLE权限：

GRANT SELECT ANY TABLE TO 用户;

GRANT INSERT ANY TABLE TO 用户;

2. 授权SEQUENCE对象：

GRANT SELECT, ALTER ON SEQUENCE 序列 TO 用户;

3. 使用EXTERNAL TABLE功能代替direct path加载：

在定义表空间时，设置表空间的”Default Temporary Tablespace”为一个有足够空间的临时表空间。

在创建external table时，使用”REJECT LIMIT x”参数，x为允许reject行数：

CREATE TABLE EXT_EMP

(EMPNO VARCHAR2(4), ENAME VARCHAR2(10), SAL NUMBER, COMM NUMBER)

ORGANIZATION EXTERNAL

(TYPE ORACLE_LOADER

DEFAULT DIRECTORY DIR_LOC

ACCESS PARAMETERS

(RECORDS DELIMITED BY NEWLINE

FIELDS (EMPNO,ENAME,SAL,COMM)

REJECT ROWS WITH ALL NULL FIELDS

REJECT LIMIT 10

)

LOCATION (’emp.dat’)

);