sqlloader I

30 Jun

sqlloader digunakan untuk 2 hal :

1. memindahkan dari database selain oracle ke oracle

2. memindahkan database dari kantor cabang ke kantor pusat, dimana kantor cabang ke kantor pusat tidak terhubung secara online karena keterbatasan bandwidth  internet.

Pada contoh dibawah ini, saya lengkapi script untuk memindahkan database oracle [database 1 / kantor cabang / sid : orcl1 / langkah 1 sd 5] ke format text file, kemudian saya lakukan sqlloader ke server database lainnya [database 2 / kantor pusat / sid : orcl2 / langkah 6 sd 9]

##########################################################################
# sqlloader I
##########################################################################
1. login sqlplus di oracle server I [orcl1]
[oracle@orcl1 ~]$ sqlplus scott/tiger@orcl1

2. periksa struktur
SQL> desc dept;
Name Null? Type
—————————————– ——– —————————-
DEPTNO NOT NULL NUMBER(2)
DNAME VARCHAR2(14)
LOC VARCHAR2(13)

3. membuat script sql untuk export ke text file
SQL> host
[oracle@orcl1 ~]$ vi above_file.sql
set linesize 120;
set feedback off;
set HEADING off;
set pagesize 0;
SELECT
DEPTNO || ‘,’ || DNAME || ‘,’ || LOC
FROM
dept;
[oracle@orcl1 ~]$ exit

4. data di table dept ditambahkan
SQL> select * from dept;
DEPTNO DNAME LOC
———- ————– ————-
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

SQL> insert into dept values (41,’IT’,’SURABAYA’);
SQL> insert into dept values (42,’MKT’,’SURABAYA’);
SQL> commit;

SQL> select * from dept;
DEPTNO DNAME LOC
———- ————– ————-
41 IT SURABAYA
42 MKT SURABAYA
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

5. lakukan export ke text file
SQL> exit
[oracle@orcl1 ~]$ cat above_file.sql | sqlplus -s scott/tiger@orcl1 > dept_290609.txt
[oracle@orcl1 ~]$ cat dept_290609.txt
41,IT,SURABAYA
42,MKT,SURABAYA
10,ACCOUNTING,NEW YORK
20,RESEARCH,DALLAS
30,SALES,CHICAGO
40,OPERATIONS,BOSTON

6. untuk melakukan sqlloader ke server ke 2 [orcl2], perlu dibuat file controlnya
[oracle@orcl1 ~]$ vi dept.ctl
load data
INFILE dept_290609.txt
APPEND
into table DEPT
fields terminated by “,”
TRAILING NULLCOLS
(DEPTNO,DNAME,LOC)

7. periksa keadaan tabel sebelum di sqlloader
[oracle@orcl1 ~]$ sqlplus scott/tiger@orcl2
SQL> select * from dept;
DEPTNO DNAME LOC
———- ————– ————-
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL> exit

8. menjalankan sqlloader
[oracle@orcl1 ~]$ sqlldr userid=scott/tiger@orcl2 control=dept.ctl log=dept.log

9. periksa keadaan tabel sesudah di sqlloader
[oracle@orcl1 ~]$ sqlplus scott/tiger@orcl2
SQL> select * from dept;
DEPTNO DNAME LOC
———- ————– ————-
41 IT SURABAYA
42 MKT SURABAYA
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL> exit

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: