This discussion is archived
3 Replies Latest reply: Jan 11, 2013 7:36 AM by Dean Gagne RSS

How to import specific user and related table with data

792055 Newbie
Currently Being Moderated
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. Oracle ACE
    Currently Being Moderated
    *'/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 . Expert
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points