in Oracle

ORA-01950: no privileges on tablespace ‘USERS’ – Oracle

Problem

Import the objects from one user scott to another user tom, returns error ORA-01950: no privileges on tablespace 'USERS'.

Procedure

Export all the objects from user scott,

[tom@ocp backup]$ exp scott/oracle file=scott
[tom@ocp backup]$ ll
-rw-rw-r--. 1 tom tom 24576 Aug 31 22:55 scott.dmp

Error occurred when Importing the objects from one user scott to another user tom,ORA-01950: no privileges on tablespace 'USERS', the message was as below,

[tom@ocp backup]$ imp system/oracle file=scott.dmp fromuser=scott touser=tom

Import: Release 11.2.0.1.0 - Production on Sat Aug 31 23:07:38 2019

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


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

Export file created by EXPORT:V11.02.00 via conventional path

Warning: the objects were exported by SCOTT, not by you

import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses ZHS16GBK character set (possible charset conversion)
. importing SCOTT's objects into TOM
. . importing table                         "DEPT"
IMP-00058: ORACLE error 1950 encountered
ORA-01950: no privileges on tablespace 'USERS'
. . importing table                          "EMP"
IMP-00058: ORACLE error 1950 encountered
ORA-01950: no privileges on tablespace 'USERS'
. . importing table                     "SALGRADE"
IMP-00058: ORACLE error 1950 encountered
ORA-01950: no privileges on tablespace 'USERS'
About to enable constraints...
Import terminated successfully with warnings.
[tom@ocp backup]$


The object scheme was imported, however the table content cannot be imported, it was empty:

SQL> conn tom/oracle;
Connected.
SQL> select object_name,object_type from user_objects;

OBJECT_NAME      OBJECT_TYP
-------------------- ----------
JOB1             JOB
DEPT             TABLE
PK_DEPT          INDEX
EMP          TABLE
PK_EMP           INDEX
SALGRADE         TABLE

6 rows selected.

SQL> select * from emp;

no rows selected

SQL>

Now delete all the objects which had been imported into user tom,

SQL> drop table dept CASCADE CONSTRAINTS;

Table dropped.

SQL>

SQL> drop table emp CASCADE CONSTRAINTS;

Table dropped.
SQL> drop table salgrade CASCADE CONSTRAINTS;

Table dropped.

SQL>
SQL> select object_name,object_type from user_objects;

OBJECT_NAME      OBJECT_TYP
-------------------- ----------
JOB1             JOB

SQL>

If grant user tom the unlimited tablespace, the error ORA-01950: no privileges on tablespace 'USERS' was resolved.

Solution

SQL> GRANT UNLIMITED TABLESPACE TO tom;

Grant succeeded.

SQL>
SQL> select user from dual;

USER
------------------------------
SYS

SQL>

Verify the result

Do it again:

[tom@ocp backup]$ imp system/oracle file=scott.dmp fromuser=scott touser=tom

Import: Release 11.2.0.1.0 - Production on Sat Aug 31 23:36:19 2019

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


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

Export file created by EXPORT:V11.02.00 via conventional path

Warning: the objects were exported by SCOTT, not by you

import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses ZHS16GBK character set (possible charset conversion)
. importing SCOTT's objects into TOM
. . importing table                         "DEPT"          4 rows imported
. . importing table                          "EMP"         14 rows imported
. . importing table                     "SALGRADE"          5 rows imported
About to enable constraints...
Import terminated successfully without warnings.

Check the result in Oracle:

SQL> select object_name,object_type from user_objects;

OBJECT_NAME      OBJECT_TYP
-------------------- ----------
DEPT             TABLE
PK_DEPT          INDEX
EMP          TABLE
PK_EMP           INDEX
SALGRADE         TABLE
JOB1             JOB

6 rows selected.

SQL> select * from dept;

    DEPTNO DNAME      LOC
---------- -------------- -------------
    10 ACCOUNTING     NEW YORK
    20 RESEARCH   DALLAS
    30 SALES      CHICAGO
    40 OPERATIONS     BOSTON

SQL>

Reference

Oracle no privileges on tablespace USERS

Write a Comment

Comment