seleksi field pada SQL Loader Oracle

wah bingung nih, harus membandingkan data 400 ribu kedalam data yang file size nya 10 giga, huaaaaaaaaaaaaaaaa.... ya sud di intip dulu isi file nya, eh ternyata file tersebut text yang mempunyai delimiter "(spasi)", sekarang masalahnya adalah saya tidak mau me-load file tersebut ke dalam database semuanya, karena gak penting gitu lho data2 yang lain :D... hmm setelah hasil intipan didapat ternyata data yang saya butuhkan ada di urutan ke 1 dan 7 dari file tersebut. Contoh :

"MI","Agate Falls","falls","Ontonagon","26","131","462851N","0890527W",
"46.48083","-89.09083","","","","","","","Trout Creek"

"MI","Agate Harbor","bay","Keweenaw","26","083","472815N","0880329W",
"47.47083","-88.05806","","","","","","","Delaware"

"MI","Agate Point","cape","Keweenaw","26","083","472820N","0880241W",
"47.47222","-88.04472","","","","","","","Delaware"

Note: contoh diatas pada file aslinya hanya ada 3 baris

Yang saya butuhkan dari data di atas untuk di insert ke dalam table saya adalah field pertama dan ke-7 lalu setelah saya buat table dalam database dengan field:
nama table: lokasi

kode varchar(2);
letak varchar(10);

Kemudian saya buat control file nya, yang isinya:

LOAD DATA
APPEND INTO TABLE test_table
(
kode CHAR TERMINATED BY "," ENCLOSED BY '"',
k1 FILLER TERMINATED BY "," ENCLOSED BY '"',
k2 FILLER TERMINATED BY "," ENCLOSED BY '"',
k3 FILLER TERMINATED BY "," ENCLOSED BY '"',
k4 FILLER TERMINATED BY "," ENCLOSED BY '"',
k5 FILLER TERMINATED BY "," ENCLOSED BY '"',
letak CHAR TERMINATED BY "," ENCLOSED BY '"'
)

control file tersebut saya save menjadi: control.ctl

nama file yang akan saya load adalah file_master.dat

lalu saya jalankan command berikut ini pada command prompt:

sqlldr user/password@databasename control=control.ctl data=file_master.dat

voila, saya mendapatkan data yang saya inginkan dalam table saya, tinggal saya bandingkan deh


keterangan FILLER adalah keterangan agar field tersebut tidak saya load ke dalam database, sedangkan ENCLOSED BY '"' adalah optional jika field2 yang ada dalam file yang akan anda load tidak di enclose/dibungkus oleh tanda ' " '.

sampai jumpa

Revive my "database" Again? ... huks

Pagi hari yang cerah, mendung namun ada matahari. Tiba - tiba dari kejauhan vivink teriak...EKOOOOOOOOOOOOOOO DATABASEKU MATIIIIIIIIIIIIIIIIIIIIII......
Pas diliat ternyata bisa log-on memang ke databasenya, tetapi pas di query file databasenya tidak terbaca oleh si database itu sendiri (ribet yak)....
sekedar info: Databasenya Oracle 9.2

di cek punya cek, saya logon ke server *.*.*.* make remote desktop connection, trus diliat database masih running, OK.... kemudian diliat di tablespacenya...OALAAAAAAAAAH ternyata tablespace nya vivink itu size nya = 0 sodara2.....heuheuheuheehuehu, padahal size aslinya 32 giga boooo... ya udah usut2 punya usut ternyata si oracle masih nyimpen archive terakhirnya....lalu mainkan jarimu onta....

here's the code brother:

SQL> select status from dba_tablespaces where tablespace_name='VCR_CM';
STATUS
---------
ONLINE

SQL> select file_id from dba_data_files where tablespace_name='VCR_CM';
FILE_ID
----------
11
SQL> select sequence# from v$log where status='CURRENT';

SEQUENCE#
----------
1662

SQL> select distinct group# from v$log;

GROUP#
----------
1
2
3

SQL> alter database datafile 11 offline;
Database altered.

SQL> select sequence#, archived from v$log;

SEQUENCE# ARC
---------- ---
1660 YES
1662 NO
1661 YES

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> select sequence#, status, archived from v$log;

SEQUENCE# STATUS ARC
---------- ---------------- ---
1666 INACTIVE YES
1665 INACTIVE YES
1667 CURRENT NO

SQL> alter database datafile 11 online;
alter database datafile 11 online
*
ERROR at line 1:
ORA-01113: file 11 needs media recovery
ORA-01110: data file 11: 'E:\ORACLE\ORADATA\TES\VCR_CM.ORA'


SQL> recover datafile 11;
ORA-00279: change 8379346308110 generated at 12/06/2007 22:21:08 needed for
thread 1
ORA-00289: suggestion : E:\ORACLE\ORADATA\TES\ARCHIVE\1_1661.DBF
ORA-00280: change 8379346308110 for thread 1 is in sequence #1661


Specify log: {=suggested | filename | AUTO | CANCEL}

ORA-00283: recovery session canceled due to errors
ORA-01579: write error occurred during recovery


ORA-01112: media recovery not started


SQL> recover datafile 11;
ORA-00279: change 8379346308321 generated at 12/06/2007 22:21:09 needed for
thread 1
ORA-00289: suggestion : E:\ORACLE\ORADATA\TES\ARCHIVE\1_1661.DBF
ORA-00280: change 8379346308321 for thread 1 is in sequence #1661


Specify log: {=suggested | filename | AUTO | CANCEL}
E:\ORACLE\ORADATA\TES\ARCHIVE\1_1660.DBF
ORA-00310: archived log contains sequence 1660; sequence 1661 required
ORA-00334: archived log: 'E:\ORACLE\ORADATA\TES\ARCHIVE\1_1660.DBF'


SQL> recover datafile 11;
ORA-00279: change 8379346308321 generated at 12/06/2007 22:21:09 needed for
thread 1
ORA-00289: suggestion : E:\ORACLE\ORADATA\TES\ARCHIVE\1_1661.DBF
ORA-00280: change 8379346308321 for thread 1 is in sequence #1661


Specify log: {=suggested | filename | AUTO | CANCEL}
E:\ORACLE\ORADATA\TES\ARCHIVE\1_1661.DBF
ORA-00310: archived log contains sequence 1660; sequence 1661 required
ORA-00334: archived log: 'E:\ORACLE\ORADATA\TES\ARCHIVE\1_1661.DBF'


SQL> recover datafile 11;
ORA-00279: change 8379346308321 generated at 12/06/2007 22:21:09 needed for
thread 1
ORA-00289: suggestion : E:\ORACLE\ORADATA\TES\ARCHIVE\1_1661.DBF
ORA-00280: change 8379346308321 for thread 1 is in sequence #1661


Specify log: {=suggested | filename | AUTO | CANCEL}
E:\ORACLE\ORADATA\TES\ARCHIVE\1_16611.DBF
Log applied.
Media recovery complete.

SQL> alter database datafile 11 online;


daku melakukan ini dengan kondisi database still running....
lalu, ya kalian bisa tebak vivink bisa berbahagia kembali......