Import(imp) in 11gR2
SHAN2009Jan 19 2011 — edited Jan 25 2011Hi All,
I am using Oracle Database 11g R2 (11.2.0.1.0) on Window 2008 Server, I am facing a problem during importing(imp) the dump file from 11g R1 (11.1.0.6.0) into 11gr2 (I am getting error message as ORA-01950: no privileges on tablespace 'USERS'). I have taken a schema based export(exp) dump file from 11gr1 (in that schema, some of the object created under the USERS tablespace). I used the below scripts for creating a new schema in 11gr2 database,
CREATE SMALLFILE TABLESPACE "TEST2011" DATAFILE 'D:\APP\ADMINISTRATOR\ORADATA\TEST2011\TEST201101.DBF' SIZE 524288K REUSE AUTOEXTEND ON NEXT 25600K MAXSIZE 32767M LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO DEFAULT NOCOMPRESS;
CREATE USER TEST2011 PROFILE DEFAULT IDENTIFIED BY TEST2011 DEFAULT TABLESPACE TEST2011 TEMPORARY TABLESPACE TEMP ACCOUNT UNLOCK;
GRANT CREATE ANY SYNONYM TO TEST2011;
GRANT CREATE ANY VIEW TO TEST2011;
GRANT CREATE ANY TABLE TO TEST2011;
GRANT CONNECT TO TEST2011;
GRANT RESOURCE TO TEST2011;
ALTER USER TEST2011 QUOTA UNLIMITED ON TEST2011;
REVOKE UNLIMITED TABLESPACE FROM TEST2011;
If I have not revoke the unlimited tablespace the objects are getting created in the Users Tablespace if I revoke the unlimited tablespace then I am getting the error message. I need all the objects to be created under the test2011 tablespace alone not in any other Tablespaces. Please tell me how to resolve this problem…
Thank you
Shan