vee’s notes

this is about everything

SQL*Loader Oracle September 11, 2009

Filed under: oracle — veeolina @ 9:58 am
Tags: , ,

SQL Loader adalah tool Oracle yang dapat digunakan untuk me-load data dari flat file atau text file ke dalam database.

Misalnya terdapat file employee.csv yang isinya seperti berikut:

 7369,Smith, Clerck,800
 7499,Allen,Salesman,1600
 7521,Ward,Salesman,1250
 7566,Jones,Manager,2975

Selain itu juga terdapat file birthdate.txt yang isinya:

 19861103
 19550624
 19770723
 19481119

Data pada file employee.txt akan di-load ke tabel employee:

create table employee(
 empno    number, 
 ename    varchar2(30), 
 job    varchar2(20), 
 sal    number
);

Sedangkan data pada file birthdate.txt akan di-load ke table birthdate:

create table birthdate (
 tahun    char(4), 
 bulan    char(2), 
 hari    char(2)
) 

Berikut langkah-langkah untuk me-load data dari text file ke database Oracle:
1. Buat control file yang berisi parameter untuk SQL*Loader
Berikut contoh control file untuk employee.csv:

 load data 
 replace into table employee 
 fields terminated by ";"
 (empno, ename, job, sal)

Data pada file employee.csv adalah contoh delimeted data (variable length). Sedangkan data pada file birthdate.txt adalah contoh positional data (fixed length). Control filenya dapat berbentuk seperti di bawah ini:

 load data 
 infile '/home/oracle/data/birthdate.txt' 
 append 
 into table birthdate 
 (   
   tahun    position(1:4),   
   bulan    position(5:6),   
   hari     position(7:8)
 )

2. Load data ke tabel employee dengan menggunakan perintah sqlldr:

 sqlldr USERID=oracle/oracle CONTROL=employee.ctl, DATA=employee.csv, LOG=employee.log, BAD=employee.bad
 sqlldr USERID=oracle/oracle CONTROL=birthdate.ctl, DATA=birthdate.txt, LOG=birthdate.log, BAD=birthdate.bad

sqlldr memiliki beberapa parameter, di antaranya seperti yang terlihat pada perintah di atas. Untuk mengetahui parameter apa saja yang tersedia, eksekusi perintah “sqlldr” tanpa argumen apapun. Parameter yang sering digunakan adalah:
* USERID: user dan password database Oracle
* CONTROL: control file yang digunakan untuk me-load data
* DATA: data (file) yang akan di-load
* LOG: file yang digunakan menyimpan log dari proses load
* BAD: file yang digunakan untuk menyimpan data yang tidak diproses

Data yang akan di-load ke Oracle juga dapat berada di dalam control file (in-line data). Caranya dengan menspesifikasikan nama data file-nya dengan “*” dan menambahkan keyword “begindata” untuk memulai data section. Contohnya sebagai berikut:

 load data 
 infile * 
 replace 
 into table birthdate 
 (   
   tahun    position(1:4),   
   bulan    position(5:6), 
   hari    position(7:8) 
 ) 
 begindata 
 19861103 
 19550624 
 19770723 
 19481119

SQL Loader juga memberikan kemudahan-kemudahan, di antaranya:
* Memungkinkan untuk memodifikasi data yang akan di-load ke database  terlebi dahulu

 load data 
 infile * 
 into table modifikasi_data 
 ( 
   empno        position(1:4),       
   deptcode    position(5:7)"upper(:deptcode)",   
   start_date    position(8:15)"to_date(:start_date,'YYYYMMDD')" 
 ) 
 begindata 
 0001HRD20070808 
 0002FIN20071223  
 0003FIN20080103

* Load data dari beberapa input file sekaligus

 load data 
 infile    file1.dat 
 infile    file2.dat 
 infile    file3.dat 
 append 
 into table employee 
 fields terminated by ";"
 (empno, ename, job, sal)

* Load data ke beberapa table sekaligus

load data 
infile * 
into table purchase when code like 'pur%' 
fields terminated by "," 
(   
  code    char(3),   
  expense number 
) 
into table sale when code='sal%' 
fields terminated by "," 
(   
  code    char(3),   
  income number 
) 
begindata 
sal0001,1000000 
pur0001,200000 
sal0002,500000 
sal0003,200000

* Load hanya data-data tertentu saja

 load data  
 infile *
 append
 into table my_table when (01)='D'
 ( 
   date    position(2:9),
   code    position(10:15)  
 ) 
 begindata
 D20090708AD56HJ
 D20090808DF89KH
 H20090808500000
 D20090908JK57OP

* Skip header dari data
Hal ini dapat dilakukan dengan menspesifikasikan “SKIP=n”. n menunjukkan jumlah row yang tidak akan di load ke database.

 sqlldr USERID=oracle/oracle CONTROL=employee.ctl, DATA=employee.txt, LOG=employee.log, BAD=employee.bad, skip=1

* Skip column tertentu agar tidak di-load ke database
Dapat dilakukan dengan menggunakan keyword “FILLER” atau “BOUNDFILLER”. BOUNDFILLER digunakan jika nilai suatu column tidak akan di-load ke database tapi untuk selanjutnya nilainya dibutuhkan.
Contoh1:

 load data 
 truncate
 into table table1
 fields terminated by ','
 (
   field1,   field2 FILLER,   field3
 )

Contoh2:

load data
infile *
truncate into table table2
fields terminated by ","  trailing nullcols 
(   
   column1,   
   field2 BOUNDFILLER,
   field3 BOUNDFILLER,
   field4 BOUNDFILLER,
   field5 BOUNDFILLER,
   column2     ":field2 || :field3",
   column3     ":field4 + :field5"
 )
 

External Table Oracle September 7, 2009

Filed under: oracle — veeolina @ 3:25 pm
Tags: , ,

External table adalah tabel yang datanya ada di luar database, biasanya berupa flat file atau text file.
External table biasanya digunakan sebagai alternatif pengganti SQL*Loader untuk me-load data dari flat file ke database.

Misalnya terdapat dua file employees.csv dan birthdate.txt yang akan di-load ke database.
File employee.csv terdiri dari 4 records yang field-fieldnya dipisahkan oleh tanda baca koma:

7369,Smith, Clerck,800
7499,Allen,Salesman,1600
7521,Ward,Salesman,1250
7566,Jones,Manager,2975

File birthdate.txt terdiri dari 4 records yang panjang field-fieldnya sudah fixed:

19861103
19550624
19770723
19481119

Berikut langkah-langkah untuk membuat external table dari kedua file tersebut:
1. Buat direktori yang mengarah ke direktori di mana file tersebut disimpan

create or replace directory external_dir as '/home/oracle/data';

2. Beri privilege ke user agar bisa membaca dan menulis ke direktori tersebut

grant read, write on directory external_dir to oracle;

3. Buat external table

Untuk file employees.csv yang field-field nya dipisahkan oleh koma, external table-nya adalah:

create table ext_employee (
 empno    number,
 ename    varchar2(30),
 job      varchar2(20),
 sal      number        
)
organization external (
 type    oracle_loader
 default directory external_dir
 access parameters (
   records delimited  by newline
   badfile external_dir:'employee.bad'
   logfile external_dir:'employee.log'
   fields  terminated by ','
   missing field values are null
   (
     empno, ename, job, sal        
   )
 )
 location ('employee.csv')
)
reject limit unlimited;

Sedangkan untuk file birthdate.txt yang panjang setiap field nya tetap, external table nya adalah sebagai berikut:


create table ext_birthdate (
 tahun    char(4), 
 bulan    char(2), 
 hari    char(2)
)
organization external ( 
 type    oracle_loader 
 default directory external_dir  
 access parameters (   
   records delimited by newline
   badfile external_dir:'birthdate.bad'
   logfile external_dir:'birthdate.log'   
   fields(     
     tahun    position(1:4) char(4), 
     bulan    position(5:6) char(2), 
     hari    position(7:8) char(2)   
   ) 
 )
 location ('birthdate.txt')
)
reject limit unlimited;

Masing-masing external table tersebut dapat di query:

select * from ext_employee;
select * from ext_birthdate;

Selanjutnya external table yang telah dibuat dapat digunakan untuk me-load data ke database.
Misalnya, data pada file employee.csv akan di-load ke table employee:

insert into employee select * from ext_employee;

Sedangkan, data pada file birtdate.txt akan di load ke table birthdate:

insert into birthdate select * from ext_birthdate;
 

Sekilas tentang Oracle May 7, 2009

Filed under: oracle — veeolina @ 1:37 pm

1. Sebuah oracle database server terdiri dari sebuah oracle instance dan sebuah oracle database.
2. Sebuah oracle instance terdiri dari background process dan allocated memory (SGA dan PGA)
3. Background process terdiri dari:

* Database Writer Process (DBWn)
* Log Writer Process (LGWR)
* Checkpoint Process (CKPT)
* System Monitor Process (SMON)
* Process Monitor Process (PMON)
* Recoverer Process (RECO)
* Archiver Processes (ARCn)

4. Sebuah oracle database terdiri dari:  datafile, control file, redo log file, archive log file, parameter file.
5. Untuk dapat mengakses oracle database, diperlukan proses yang disebut oracle listener

 

install postgresql di fedora 10 May 5, 2009

Filed under: postgreSQL — veeolina @ 1:18 pm
Tags: , ,

Berikut langkah-langkah untuk menginstall PostgreSQL di fedora 10:

1. Install package postgresql dan postgresql-server sebagai user root:

yum install postgresql postgresql-server

2. Buat direktori untuk menyimpan database cluster dari PostgreSQL, misalnya direktori /opt/pgsql/data. Kemudian jadikan user ‘postgres’ sebagai pemilik dari direktori tersebut (dalam hal ini user ‘postgres’ adalah default user untuk PostgreSQL).

mkdir /opt/pgsql/data

chown postgres /opt/pgsql/data

3. Selanjutnya buat database cluster dengan perintah initdb.

Database Cluster adalah kumpulan database yang dikelola dengan single server instance. Pembuatan suatu database cluster terdiri dari pembuatan direktori di mana database ‘hidup’, shared catalog tables (tabel yang menjadi milik seluruh database dalam cluster), dan template1 dan postgres database. Semua yang ter-install dalam template1 akan di-copy untuk setiap pembuatan database baru. Sedangkan, postgres database adalah default database yang bisa digunakan oleh users, utilities, dan third party applications.

Pembuatan database cluster dapat dilakukan dengan menjalankan perintah berikut:

initdb -D /opt/pgsql/data

-D /opt/pgsql/data menunjukkan bahwa /opt/pgsql/data adalah direktori di mana database cluster akan disimpan.

Untuk mulai menggunakan PostgreSQL, PostgreSQl server harus dinyalakan terlebih dahulu dengan menjalankan perintah:

pg_ctl -D /opt/pgsql/data -l logfile start

Sedangkan untuk mematikan server dapat dilakukan dengan menjalankan perintah:

pg_ctl -D /opt/pgsql/data -l logfile stop

Selanjutnya, kita dapat membuat database baru, misalnya database dbdata, dengan menjalankan perintah createdb:

createdb dbdata

Untuk mulai mengakses database tersebut jalankan perintah:

psql dbdata

Have fun with PostgreSQL ^o^

 

Column Oriented vs Row Oriented DBMS April 28, 2009

Filed under: database — veeolina @ 4:48 pm
Tags: , ,

Suatu DBMS (Database Management System) terdiri dari row dan column. Jumlah column sudah ditentukan pada waktu pembuatan database sedangkan jumlah row akan terus bertambah seiring dengan penggunaan database.

Terdapat dua jenis DBMS berdasarkan cara penyimpanan datanya pada storage, yaitu column oriented DBMS dan row oriented DBMS. Column oriented database menyimpan data berdasarkan column dan row oriented DBMS menyimpan berdasarkan row. Untuk lebih jelasnya dapat dilihat pada contoh di bawah ini:

Misalnya terdapat tabel Employee yang terdiri dari 4 column, yaitu column EmpNo, EName, Salary, dan DeptNo. Berikut visualisasi dari tabel tersebut:

EmpNo  Ename  Salary  DeptNo

1      Smith    800      20

2      Allen    1600     30

3      Ward     1250     20

Row oriented DBMS akan menyimpan setiap atribut dari suatu row secara berurutan. Jadi data dari tabel di atas akan disimpan dalam bentuk:

1, Smith, 800, 20; 2, Allen, 1600, 30; 3, Ward, 1250, 20;

Sedangkan column oriented akan menyimpan data dari tabel di atas dalam bentuk:

1, 2, 3; Smith, Allen, Ward; 800, 1600, 1250; 20, 30, 20;

Column oriented dan row oriented DBMS memiliki kelebihan masing-masing. Column oriented cocok digunakan untuk komputasi pada row yang banyak tapi hanya untuk column-column tertentu. Selain itu, jenis DBMS ini juga cocok untuk penulisan nilai suatu column yang berlaku untuk semua row. Hal ini akan membuat proses penulisan menjadi lebih efisien karena tidak mengganggu column yang lain.

Sedangkan row oriented DBMS cocok digunakan jika dalam suatu komputasi melibatkan bayak column dari suatu row secara bersamaan dan jumlah row relatif sedikit. Row oriented juga cocok digunakan untuk penulisan data suatu row di mana semua column terisi secara bersamaan.

 

DELETE vs TRUNCATE April 23, 2009

Filed under: oracle — veeolina @ 5:35 pm
Tags: , ,

DELETE sama TRUNCATE apa bedanya sih? Perasaan fungsinya sama, hapus-hapus juga..

Eits..tunggu dulu..tnyata klo salah pilih, akibatnya bisa fatal lho..

Kok bisa gitu??

Command DELETE bisa dipakai untuk menghapus semua row yg ada dalam suatu tabel. Jika pada command DELETE ditambahkan clause WHERE maka yang dihapus hanya row tertentu saja yang memenuhi kondisi WHERE tersebut. Sedangkan command TRUNCATE digunakan untuk menghapus semua row yang ada pada suatu tabel. Command ini tidak bisa diikuti oleh clause WHERE.

DELETE adalah DML (Data Manipulation Language) command. Jadi setiap operasi DELETE selesai dilakukan, command COMMIT harus dipanggil untuk membuat perubahan menjadi permanen. Selain itu setelah operasi DELETE, operasi ROLLBACK juga dapat dilakukan. Untuk dapat ROLLBACK maka semua data yang dihapus harus di-copy terlebih dahulu ke tablesapce  UNDO/ROLLBACK. Hal inilah yang menyebabkan operasi DELETE lebih lambat daripada operasi TRUNCATE. Pada operasi TRUNCATE semua data langsung dihapus dari storage tanpa harus di-copy terlebih dahulu. TRUNCATE adalah DDL (Data Defenition Language) command yang tidak bisa di undo (ROLLBACK). Sebagai DDL command, TRUNCATE tidak bisa memanggil TRIGGER sedangkan DELETE dapat memanggil TRIGGER yang berhubungan dengan tabel yang dihapus.

Jadi pastikan untuk memilih command yang tepat ya..biar ga kehilangan data, operasi yang dijalankan benar, ga lemot, dan hasilnya sesuai dg yang diinginkan 🙂

 

Solusi ORA-01157 dan ORA-01110 April 15, 2009

Filed under: oracle — veeolina @ 5:44 pm
Tags: , ,

Aku pernah ngalamin masalah tiba-tiba oracle nya ga bisa di startup. Waktu mencoba untuk startup muncul pesan error:

SQL> startup
ORACLE instance started.

Total System Global Area 700448768 bytes
Fixed Size 1221372 bytes
Variable Size 88083716 bytes
Database Buffers 608174080 bytes
Redo Buffers 2969600 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 9 - see DBWR trace file
ORA-01110: data file 9: '/opt/oracle/oradata/DATA/datafile_a.dbf'

Ada masalah apa ya?
Ternyata ORA-01157 muncul karena background process tidak menemukan salah satu datafile. Hal ini bisa terjadi karena file nya rusak atau hilang.

Nah..untuk menyelesaikan masalah ini aku ngejalanin langkah-langkah berikut:
1. matikan oracle instance

SQL>shutdown immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.

2. nyalakan oracle instance tanpa open database

SQL>startup mount
ORACLE instance started.
Total System Global Area 236404368 bytes
Fixed Size 724624 bytes
Variable Size 201326592 bytes
Database Buffers 33554432 bytes
Redo Buffers 798720 bytes
Database mounted.

3. drop datafile yang rusak/hilang

SQL>alter database datafile '/opt/oracle/oradata/DATA/datafile_a.dbf' offline drop;
Database altered.

4. coba untuk open database

SQL>alter database open;
Database altered.

5. matikan oracle instance

SQL>shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

6. nyalakan oracle

SQL>startup
ORACLE instance started.
Total System Global Area 236404368 bytes
Fixed Size 724624 bytes
Variable Size 201326592 bytes
Database Buffers 33554432 bytes
Redo Buffers 798720 bytes
Database mounted.
Database opened.
SQL>

Drop datafile pada dasarnya dapat dilakukan, karena untuk startup, oracle tidak memerlukan datafile.

case solved ^^