5 Replies Latest reply on Aug 22, 2013 2:00 PM by Larry Wilkinson

    ORA-01950: no privileges on tablespace during import

    maddox
      I am trying to import using data pump (OEM) from a 10g database to an 11g database using import files. I keep receiving an error (ORA-01950: no privileges on tablespace). My user has already been granted unlimited tablespace and create any table. Also, I was able to use the same user id and manually create a table in the tablespace. Does anyone have an idea of what I could be missing?
        • 1. Re: ORA-01950: no privileges on tablespace during import
          sb92075
          maddox wrote:
          I am trying to import using data pump (OEM) from a 10g database to an 11g database using import files. I keep receiving an error (ORA-01950: no privileges on tablespace). My user has already been granted unlimited tablespace and create any table. Also, I was able to use the same user id and manually create a table in the tablespace. Does anyone have an idea of what I could be missing?
          I believe Oracle better reports reality than you..
          01950, 00000, "no privileges on tablespace '%s'"
          // *Cause:  User does not have privileges to allocate an extent in the
          //          specified tablespace.
          // *Action: Grant the user the appropriate system privileges or grant the user
          //          space resource on the tablespace.
          • 2. Re: ORA-01950: no privileges on tablespace during import
            Fran
            Required Privileges

            Privilages necessary for Imports are listed Below:

            To use import, you need the CREATE SESSION privilege. The CONNECT role will have this privilege established during database creation.
            If user XYZ has created a export, then any user with IMP_FULL_DATABASE role granted to him (usually a DBA role will be granted this privilege at database creation) can import the export file.
            If user XYZ happens to be a DBA (user granted DBA role), then users only with IMP_FULL_DATABASE (dba role) can import this file.
            If user XYZ has created a export file, then the same userid (XYZ) on a different database can import this file.
            A user with IMP_FULL_DATABASE privilege, can only do a full database import.
            To be able to import privileges that a user has granted to others, the user initiating the import must either own the object or have object privilege with the WITH GRANT OPTION.

            i get this from:
            http://www.orafaq.com/wiki/Import#Required_Privileges
            1 person found this helpful
            • 3. Re: ORA-01950: no privileges on tablespace during import
              maddox
              I already had the create session on the userid and added the imp_full_database but got the same result. Is it possible that OEM is running the load as another user? The log has the correct userid as the owner of the job. Both the export and import are run with the same userid.

              The article also points that the tablespace cannot be set to read. I verified mine are set to read/write.
              • 4. Re: ORA-01950: no privileges on tablespace during import
                maddox
                Figured it out. The user permissions must be granted to the user for the schema not the owner of the job. I granted them unlimited tablespace and the tables imported correctly. Tables are created using the permissions of the schema not the import job.
                • 5. Re: ORA-01950: no privileges on tablespace during import
                  Larry Wilkinson

                  Thanks very much - this also solved the problem for me.