How to Import and Create Users in Oracle

Oracle When you do an import sometimes you will find you will need to also create a user for this new set of data. Today I find myself in that situation as I imported data from Bell Canada and set up a new user for that data.

  1. Create a new user for the data.

    This example creates a user named "foo" with the password "foo".  Foo is a typical user that can create sessions, synonyms, procedures and tables.

    CREATE USER FOO IDENTIFIED BY FOO
      DEFAULT TABLESPACE users
      TEMPORARY TABLESPACE temp
      QUOTA UNLIMITED ON users;
    
    GRANT CREATE SESSION to FOO;
    GRANT CREATE SYNONYM TO FOO;
    GRANT CREATE PROCEDURE TO FOO;
    GRANT CREATE TABLE TO FOO;
    GRANT CREATE VIEW TO FOO;

    For more information please read Oracle’s CREATE USER documentation.

    If you want this user to be a DBA you can grant that to her too

    GRANT DBA TO FOO;
  2. Import the dump using the new user.

    This example imports the dump from the file "dump.dmp". This dump was created using the user "foo" and is being imported to the user "foo". The dump will be logged in the file "dump.log".

    imp system/system@example file=dump.dmp log=dump.log fromuser=foo touser=foo

    For more information please read Oracle’s Import Export FAQ.

  3. To redo recreate user and then import again.

    If you need to redo an import the easiest thing to do is to drop the user, recreate her and then do the import again. When you drop the user specify CASCADE to drop all the objects in the user’s schema.

    DROP USER FOO CASCADE;

    For more information please read Oracle’s DROP USER documentation.

Leave a Reply

Your email address will not be published. Required fields are marked *