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’)
);