Oracle Error ORA-01950

• 错误现象
• 解决步骤

 

一、错误现象

 

使用SQL*Loader向scott用户的bonus表导入数据时,提示插入了数据

$ sqlldr scott/tiger control=ldr_case.ctl

SQL*Loader: Release 11.2.0.1.0 - Production on Mon Sep 30 16:06:18 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Commit point reached - logical record count 4

 

查看bonus表是否插入成功

$ sqlplus scott/tiger

SQL*Plus: Release 11.2.0.1.0 Production on Mon Sep 30 16:08:22 2013

Copyright (c) 1982, 2009, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> SELECT * FROM BONUS;

no rows selected

 

于是查看log文件,见到如下报错

Record 1: Rejected - Error on table BONUS.
ORA-01950: no privileges on tablespace 'USERS'

Record 2: Rejected - Error on table BONUS.
ORA-01950: no privileges on tablespace 'USERS'

Record 3: Rejected - Error on table BONUS.
ORA-01950: no privileges on tablespace 'USERS'

Record 4: Rejected - Error on table BONUS.
ORA-01950: no privileges on tablespace 'USERS'

 

 

二、解决步骤

 

使用GRANT RESOURCE TO "user";

$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Mon Sep 30 16:27:36 2013

Copyright (c) 1982, 2009, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> grant resource to scott;

Grant succeeded.

 

 

再次导入后查看bonus表

$ sqlldr scott/tiger control=ldr_case.ctl

SQL*Loader: Release 11.2.0.1.0 - Production on Mon Sep 30 16:28:06 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Commit point reached - logical record count 4

$ sqlplus scott/tiger

SQL*Plus: Release 11.2.0.1.0 Production on Mon Sep 30 16:28:10 2013

Copyright (c) 1982, 2009, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select * from bonus;

ENAME      JOB              SAL       COMM
---------- --------- ---------- ----------
SMITH      CLEAK           3904
ALLEN      SALESMAN        2891
WARD       SALESMAN        3128
KING       PRESIDENT       2523