3 Replies Latest reply: Jan 11, 2013 9:36 AM by Dean Gagne-Oracle RSS

    How to import specific user and related table with data

    user12263161
      Hi All,

      I am trying to import export dump of 10g to 11gR2. I am getting the below error;

      ORA-39083: Object type TABLESPACE failed to create with error:*
      ORA-02494: invalid or missing maximum file size in MAXSIZE clause*
      Failing sql is:*
      CREATE TEMPORARY TABLESPACE "TEMP1" TEMPFILE '/test/data/temp01.dbf' SIZE 12582912000 AUTOEXTEND ON NEXT 8192 MAXSIZE 12000M,'/test/data/temp03.dbf' SIZE 12884901888 AUTOEXTEND ON NEXT 8192 MAXSIZE 12288M,'/test/data/temp08.dbf' SIZE 34359721984 AUTOEXTEND ON NEXT 52428800 MAXSIZE 32767M,*

      and since the above failed ;

      ORA-39083: Object type USER failed to create with error:
      ORA-00959: tablespace 'TEMP1' does not exist
      Failing sql is:
      CREATE USER "EUR" IDENTIFIED BY VALUES 'C8B09B2144327BBF' DEFAULT TABLESPACE "DISCOVERER" TEMPORARY TABLESPACE "TEMP1"


      I dont know why 10g export has such kind of errors. Now i want to import the above user and tables/objects and data owned by him.

      How should i proceed?

      Thanks for the Help.

      Edited by: user12263161 on Jan 10, 2013 8:37 PM
        • 1. Re: How to import specific user and related table with data
          Paul M.
          *'/test/data/temp08.dbf' SIZE 34359721984 AUTOEXTEND ON NEXT 52428800 MAXSIZE 32767M*
          32767M is less than 34359721984, and MAXSIZE can't be less than SIZE.

          You may try to pre-create that tablespace, changing 32767M with UNLIMITED.
          • 2. Re: How to import specific user and related table with data
            Richard Harrison .
            Hi,
            This may be a bug, I'd manually create the tablespaces and then exlcude their creation from the impdp command using exclude= (or just ignore the error when it tries to create them when they are already there).

            Regards,
            Harry
            • 3. Re: How to import specific user and related table with data
              Dean Gagne-Oracle
              I'm not sure why there was bad ddl created. I wonder if there is something wrong with the view that is fetching this information from the source database. The problem can be fixed by just creating that tablepace manually. You have the ddl, and if you have a situation where you don't get it all, you can run the import command with:

              sqlfile=<some_file_name.sql>

              You can also use the:

              include=tablespace

              to limit what gets written to your sqlfile. Once you get this file, it will contain the list of ddl that would have been executed if the import was run. You can edit the file to get the ddl that you want. If you need to change the ddl, you can do that as well.

              I should have added: Once the tablespace is imported,you can run the same import and then add:

              exclude=tablespace

              or if you are running just a schema or set of schemas, then you don't need to add the exclude since tablespaces are not imported as part of a schema import.

              Hope this help.

              Dean

              Edited by: Dean Gagne on Jan 11, 2013 10:35 AM