Results 1 to 11 of 11
  1. #1
    Join Date
    Apr 2004
    Posts
    31

    Unanswered: full import with users

    hi there...

    Yesterday I tried to import a complete database from a dmp-file... it worked fine, but i had to create a new user. Before I created that user the import-output complaind about the fact that the user did not exist... and so I created one and it worked out fine...

    however...

    now I have another dmp-file to import. The same thing happened here... but the difference is that there are many more users in this one... so I cant create the users because i don't know their names and what roles they posses...

    There must be away of having the import also create the users and the roles?

    also... I need to drop a database that did'nt get completly built... how do I do that? is it simply to delete the specific directory in the admin and oradata directories?

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713
    Extract the DDL from the export file:
    Code:
    imp <uid>/<pw> show=y file=expdat.dmp log=TheSchema.txt
    With text editor or other, separate the create role/users statements.
    Create the roles, users.
    Import with ignore=Y.
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  3. #3
    Join Date
    Apr 2004
    Posts
    31
    ok...

    so, correct me if I'm wrong here... the SHOW=Y will create and open a textfile? and in that textfile i should cut out the users and roles? SHoluld I not use the FULL=Y parameter?

    i don't completly understand...

    sorry for beeing a pain in the a**

  4. #4
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713
    SHOW=Y will extract the schema's from the dump file, unfortunatelly non formmated. also use option FULL=Y
    Also if you are on a Unix box you can use the 'strings' command to do the same.

    To create a text file with this data use the LOG=MyFile.txt option.

    Code:
    imp <uid>/<pw> file=expdat.dmp show=y full=y log=myschema.txt
    Then with a text editor you can copy the CREATE USER , CREATE ROLE statements.
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  5. #5
    Join Date
    Jul 2003
    Posts
    2,296
    what about importing using the interactive method??

    once you specify 'no' for a full import it should prompt you for what 'users' to import. I wonder if you can get it to list the users.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  6. #6
    Join Date
    Apr 2004
    Posts
    31
    interesting idea duckie... I might try that...

    LK_Brwn_DBA > won't that start the import (that will fail)? the problem seem to be that there aren't any create user or create role in the dmp-file. It immidiatly tries to grant roles or such to a user that doesn't exist(nor does it create it).

    still needssome info on that last question on my first post...
    thanks for the inputs.

  7. #7
    Join Date
    Jun 2004
    Posts
    796
    Provided Answers: 1
    To answer your last question on your first post, this is a quote from the Oracle Administrator's Guide (http://www.lsbu.ac.uk/oracle/oracle9...te.htm#1018217):


    "To drop a database, you must remove its datafiles, redo log files, and all other associated files (control files, initialization parameter files, archived log files)."
    90% of users' problems can be resolved by punching them - the other 10% by switching off their PCs.

  8. #8
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Talking

    Acctually you are correct, the CREATE USER statements do not exist in the export file, but you do have something like this:
    . importing SCOTT's objects into OPS$ORACLE...

    Create the Users, assign default/temp tablespaces and grant connect, resource.

    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  9. #9
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    The create user commands are only made when a full database export is done. If you export 20 users, the import will expect the users to be there when the import is done.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  10. #10
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Thumbs up

    Quote Originally Posted by beilstwh
    The create user commands are only made when a full database export is done. If you export 20 users, the import will expect the users to be there when the import is done.
    Right again!

    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  11. #11
    Join Date
    Apr 2004
    Posts
    31
    ok so what you guys are saying is this... create the users/roles first, then do the import. correct?

    here are the errors/warnings from the log of my last import try:

    imp-00041 Warning object created with compilation warnings
    (maybe I shouldn't bother to much abut that one... since it's not really an arror and therefor not my problem )

    ...

    imp-00017 following statement failed:
    "GRANT EXECUTE ON "SP_whatever" TO "secret""
    ora-01917 user or role "secret" does not exist
    (so if I should pre-create this one? but is it a role or a user?)

    then i got a suspisious one:
    PLS-00103 symbol "BRAINY" occured when one of the following were expected:
    .(),*@% (etc...)
    "BRAINY" is one of the users I created before I performed the import.

    another error is:
    IMP-00008 unknown statement in export-file
    (I get a bunch of thoose, maybe a result of previous errors?)

    last error is:
    ORA-00942 table or view does not exist.
    (also result of prevoius errors)

    Any ideas would be real helpful...

    thanks...

    (by the way.... the error messages were translated from swedish so they might not be exact)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •