Import/Export an oracle db/schema

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]