Prerequisites:
You
should have the following software installed on your machine.
-
Oracle Database Server
- import / export utility
Assumption:
This
sample we have 2 Databases
First
is "viper" installed on "Sun" machine?
Second
is "fuju" installed on "pc" (win2k professional)
Both
Databases use the same "TH8TISASCII" character set
We
would like to export all data of "Nicole" schema (user)
from "viper" database to "fuju" database.
Attendees:
-
Junior DBA
- Developer
Purpose:
Describe
basic steps to use imp/exp utility for transferring data between
different database servers.
Step-by-Step
Example:
Export
1.
Checking current Database character set
2.
Checking current client environment
3.
Exporting data from specific schema
Transfer
the dump file
4.
Transferring your dump file to other system or store that file
in secure media
Import
5.
You must have an existing schema (user) before importing the data
6.
Checking import database character set
7.
Checking current client environment
8.
Importing data to specify schema
9.
Checking your data
Export
1.
Checking current Database character set
| $
sqlplus nicole/tiger@viper
SQL> select * from nls_database_parameters where parameter
= 'NLS_CHARACTERSET';
PARAMETER
VALUE
--------------------------- -----------
NLS_CHARACTERSET TH8TISASCII
SQL> |
2.
Checking current client environment
| $
echo $NLS_LANG
THAI_THAILAND.TH8TISASCII |
If
the NLS_LANG environment is wrong, you must set it to correct
value first.
(NLS
setting for oracle environment "http://www.exzilla.net/docs/nls/oracleNLS01.php")
3.
Exporting data from specific schema
| $
exp nicole/tiger@viper file=nicoleData.dmp
...
Export
done in TH8TISASCII character set and TH8TISASCII NCHAR
character set
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user NICOLE
...
.
exporting statistics
Export terminated successfully without warnings.
$ |
Don't
forget to check character that we've done
Export
done in TH8TISASCII character set and TH8TISASCII NCHAR character
set
The
messages should like above. If you found "done in other character
set" your database may have problems.
Transfer
the dump file
4.
Transferring your dump file to other system or store that file
in secure media
You
can use many ways to transfer the dump file to other system such
as copy, ftp or write to tape or cd-rom .
Import
5.
You must have an existing schema (user) before importing the data
5.1
Checking all users in current database that you would like to
import the data.
| SQL>
select * from all_users;
USERNAME
USER_ID CREATED
------------------------------
---------- ---------
SYS
0 05-FEB-02
SYSTEM
5 05-FEB-02
OUTLN
11 05-FEB-02
…
NICOLE
20 05-FEB-02
HR 21
21-FEB-02
OE
22 21-FEB-02
…
SQL> |
5.2
If you don't have desired user, you must create that user first.
Sample
command for creating user
| sql>
connect system/manager@fuju
sql> create user nicole identified by tiger default tablespace
uses temporary tablespace temp;
sql> grant connect,resource to nicole; |
6.
Checking import database character set
| D:\>
sqlplus nicole/tiger@viper
SQL> select * from nls_database_parameters where
parameter = 'NLS_CHARACTERSET';
PARAMETER
VALUE
--------------------------- -----------
NLS_CHARACTERSET TH8TISASCII
SQL> |
7.
Checking current client environment
In
the regedit program in window environment should be like this
regedit
-> HKEY_LOCAl_MACHINE\SOFTWARE\ORACLE ->
NLS_LANG=THAI_THAILAND.TH8TISASCII
If
the value of NLS_LANG is not correct, you must set NLS_LANG to
correct value first.
(NLS
setting for oracle environment “ http://www.exzilla.net/docs/nls/oracleNLS01.php
“)
8.
Importing data to specify schema
| D:\>
imp nicole/tiger@fuju file=nicoleData.dmp
...
import done in TH8TISASCII character set and TH8TISASCII
NCHAR character set
. importing NICOLE's objects into NICOLE
Import terminated successfully without warnings.
D:\> |
Don't
forget to check character that we've done.
Import
done in TH8TISASCII character set and TH8TISASCII NCHAR character
set
The
messages should like above. If you found "done in other character
set" your database may have problems. Please check all previous
steps again.
9.
Checking your data
Just
connect to fuju database and select your data
| D:\>
sqlplus nicole/tiger@fuju
…
SQL> select * from tab;
SQL> select * from employee; |
Finish
:)
Note:
N/A
Complete
sample code:
N/A
References:
1.
Oracle9i Database Utilities Release 1 (9.0.1) Part
Number A90192-01
More
Information:
1.http://otn.oracle.com
- All Oracle Technology software and documents
2.NLS
setting for oracle environment "http://www.exzilla.net/docs/nls/oracleNLS01.php"
Keywords:
IMPORT
EXPORT IMP EXP ORACLE LINUX TH8TISASCII NCHAR NLS_LANG NLS
|