Prerequisites:

  • Import:
    • Oracle DB setup with default DB
    • the username that has access to the import DB
    • Remember to set the character set if importing Unicode
  • Import/Export:
    • ip-address, SID, user/pass (sysDBA)

To export/import a DB using telnet/linux console:

  1. Set the environment variables (should exist)
    [bash]Linux:
    $ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1/bin
    $ORACLE_SID = myoracl
    $PATH = /u01/app/oracle/product/10.2.0/db_1/bin

    Windows:
    SET NLS_LANG=AMERICAN_AMERICA.ELI8MSWIN1253[/bash]

  2. Startup the DB (if needed)
    [bash]sqlplus /nolog[/bash]
    SQL prompt:[sql] connect user/pass as sysdba
    startup
    exit[/sql]
  3. Startup the listener (if needed)
    [bash]cd to oracle app product path ($ORACLE_HOME/bin)
    ./lsnrctl start[/bash]
  4. Startup the em console (web interface, if needed)
    [bash]./emctl start[/bash]
  5. Export data
    1. entire database:
      [bash]exp dbuser/password FULL=y FILE=exportDB.dmp LOG=exportDB.log[/bash]
    2. schema only:
      [bash]exp dbuser/password FIlE=scott.dmp OWNER=scott[/bash]
  6. Importdata
    1. if needed, create the user for the DB:
      [sql]CREATE USER username IDENTIFIED BY “username”
      DEFAULT TABLESPACE “USERS”
      TEMPORARY TABLESPACE “TEMP”
      PROFILE DEFAULT
      QUOTA UNLIMITED ON “USERS”;
      GRANT “CONNECT” TO username;
      GRANT “DBA” TO username;
      GRANT CREATE session to username;
      ALTER USER username DEFAULT ROLE NONE;[/sql]
    2. login with new user and try an sql statement:
      [sql]select sysdate from dual;[/sql]
    3. entire database:
      [bash]imp dbuser/password FULL=y FILE=exportDB.dmp LOG=importDB.log[/bash]
    4. schema only:
      [bash]imp dbuser/password FIlE=scott.dmp[/bash]

    Notes:
    – If you get an error that the export was not done by the user doing the import, you need to do it with the user who actually exported the .dmp file.
    – If you get an error trying to import and it stops, you may need to add: dbuser/password@db

import/export tables only:

  1. Export tables [emp] and [dept] from “scott” user:
    [bash]exp dbuser/password FILE=scott.dmp TABLES=(emp,dept)[/bash]
  2. Import tables [dept] and [emp] from “scott” user:
    [bash]imp dbuser/password FIlE=importDB.dmp FROMUSER=scott TABLES=(dept,emp)[/bash]