Import a Oracle DMP file into a Fresh install of oracle

  • A client sent us an Oracle database we need to test against. We don't use Oracle or have any in-house Oracle expertise.

    We need to setup the database so we can connect to it and debug a problem.

    I did a fresh install of Oracle 9 (the version the client is running) and the management tools.

    For the life of me, I cannot get it to import the data. It should not be this complicated. I must be getting something wrong.

    I've tried:

    imp 'sys/password AS SYSDBA' file=c:\database.dmp full=yes log=implog.txt
    

    and got:

    Connected to: Personal Oracle9i Release 9.2.0.1.0 - Production
    With the Partitioning, OLAP and Oracle Data Mining options
    JServer Release 9.2.0.1.0 - Production
    
    Export file created by EXPORT:V09.02.00 via conventional path
    
    Warning: the objects were exported by SYSTEM, not by you
    
    import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
    . importing SYSTEM's objects into SYS
    . importing ABCDE's objects into ABCDE
    IMP-00003: ORACLE error 1435 encountered
    ORA-01435: user does not exist
    Import terminated successfully with warnings.
    

    But nothing shows up in the manager as far as tables in any schema and I'm at my wits end.

    Thanks, exact problem here. Got an oracle dump and we have to import. Extremely counterintuitive compared to, well, virtually any other database system

  • You'll need to create a user (or Schema) first

    C:\>sqlplus system/password
    
    SQL> create user CLIENT_TEST identified by client_test_password;
    SQL> grant connect, unlimited tablespace, resource to CLIENT_TEST;
    SQL> exit
    

    Then you can use the fromuser= and touser= IMP switches to import the data into the new user/schema:

    C:\>imp system/password FROMUSER=ABCDE TOUSER=client_test file=c:\database.dmp full=yes
    

    Hope that helps!

    ORA-65096: invalid common user or role name

  • It is not common to create tables as SYS or SYSTEM in Oracle, as those two accounts are administrators.

    Error means that your client created tables as user ABCDE.

    You should create that user before import, then you should import data as that user,

    This should eliminate the error because exported file contains permissions and other informations related to user ABCDE that does not exist in database.

    Check this question for additional hints: How to determine the Schemas inside an Oracle Data Pump Export file.

  • You first need to create user "ABCDE"

    Something like

    In SQL*PLUS:

    create user ABCDE identified by password;
    grant connect, resource to ABCDE;
    

    There's a squillion options on "create user" but this would use the defaults.

  • Did the client provide the details of how they generated the dump file? In particular, are you certain that this is a full export rather than a schema-level export, a tablespace-level export, or a table-level export?

  • Rather than 'sys/password AS SYSDBA', try 'system/password'.

    Oracle's tool requires the same user to import as created the export even though sys is the "all powerful" user.

  • Warning was given regarding that the data that you are importing was actually exported by SYS user as .dmo file. Try this..

    C:\oracle\ora92\bin>imp sys/[email protected] file=c:\database.dmp full=yes
    

    dbname is the database name that you have provided

  • c:\\ imp database name/[email protected] file=xyz,dmp grants=n ignore=y 
      fromuser=database name touser=database name
    

    You should clarify your answer by explaining how this works, what the parameters are, *anything* really beyond just a command-line.

License under CC-BY-SA with attribution


Content dated before 6/26/2020 9:53 AM