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