Import/Export an oracle db/schema

• 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)
   ?

   1 2 3 4 5 6 7

   <span style="text-decoration: underline;">Linux</span>: $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   <span style="text-decoration: underline;">Windows</span>: SET NLS_LANG=AMERICAN_AMERICA.ELI8MSWIN1253

2. Startup the DB (if needed)
   ?

   1	sqlplus /nolog

   SQL prompt:

   ?

   1 2 3

   connect user/pass as sysdba startup exit

3. Startup the listener (if needed)
   ?

   1 2	cd to oracle app product path ($ORACLE_HOME/bin) ./lsnrctl start

4. Startup the em console (web interface, if needed)
   ?

   1	./emctl start

5. Export data
   1. entire database:
      ?

      1	exp dbuser/password FULL=y FILE=exportDB.dmp LOG=exportDB.log

   2. schema only:
      ?

      1	exp dbuser/password FIlE=scott.dmp OWNER=scott

6. Importdata
   1. if needed, create the user for the DB:
      ?

      1 2 3 4 5 6 7 8 9

      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;

   2. login with new user and try an sql statement:
      ?

      1	select sysdate from dual;

   3. entire database:
      ?

      1	imp dbuser/password FULL=y FILE=exportDB.dmp LOG=importDB.log

   4. schema only:
      ?

      1	imp dbuser/password FIlE=scott.dmp

   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:
   ?

   1	exp dbuser/password FILE=scott.dmp TABLES=(emp,dept)

2. Import tables [dept] and [emp] from “scott” user:
   ?

   1	imp dbuser/password FIlE=importDB.dmp FROMUSER=scott TABLES=(dept,emp)