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>