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:
- 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/binWindows:
SET NLS_LANG=AMERICAN_AMERICA.ELI8MSWIN1253[/bash] - Startup the DB (if needed)
[bash]sqlplus /nolog[/bash]
SQL prompt:[sql] connect user/pass as sysdba
startup
exit[/sql] - Startup the listener (if needed)
[bash]cd to oracle app product path ($ORACLE_HOME/bin)
./lsnrctl start[/bash] - Startup the em console (web interface, if needed)
[bash]./emctl start[/bash] - Export data
- entire database:
[bash]exp dbuser/password FULL=y FILE=exportDB.dmp LOG=exportDB.log[/bash] - schema only:
[bash]exp dbuser/password FIlE=scott.dmp OWNER=scott[/bash]
- entire database:
- Importdata
- 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] - login with new user and try an sql statement:
[sql]select sysdate from dual;[/sql] - entire database:
[bash]imp dbuser/password FULL=y FILE=exportDB.dmp LOG=importDB.log[/bash] - 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
- if needed, create the user for the DB:
import/export tables only:
- Export tables [emp] and [dept] from “scott” user:
[bash]exp dbuser/password FILE=scott.dmp TABLES=(emp,dept)[/bash] - Import tables [dept] and [emp] from “scott” user:
[bash]imp dbuser/password FIlE=importDB.dmp FROMUSER=scott TABLES=(dept,emp)[/bash]
Comments