SQL Loader
It’s a utility. It will load the external data table into oracle. Like excel, Csv & notepad.
Keyword: sqlldr
Syntax: $ sqlldr un/pw parameter 1,2, etc ..
Parameters:
Control= <controlfile name>
Inline = <input file name>
Badfile= <badfile name> – it will have an unloaded data, incase of any failure and mismatch
Discardfile=< discordfile name> – It will store the condition of the data. Like eno should be 3 digit
Log =<logfile name> – log
Ex: We have an employee table
ENo: 101,102,103ABC & 05 etc… SO the tow digit will not upload, since we have a condition and character will not upload and it will go to badfile.
Sql> conn Muthu/Muthu
Sql> create table emp(eno number, ename varchar2 (20), salary number);
Open a notepad and write a script an save as /opt/sqlload.ctl
Load data
Infile ‘/opt/record.dat’ append
Into table emp fields terminated by ‘,’ (eno,ename,salary)
Append – is optional
For notepad save as ‘.dat’ and excel, csv save as ‘.csv’
$ sqlldr Muthu/Muthu control=’/opt/sqlload.ctl’ log =/opt/sqlload.log
Sql> select * from emp;
9i – To append data, wee ned to specify this keyword, otherwise it will overwrite.
10g & 11g – Default is append.
DBlink
CREATE [PUBLIC] DATABASE LINK yourLinkName CONNECT TO theSchema IDENTIFIED BY thePW USING ‘YourDBAlias’;
CREATE DATABASE LINK test1 CONNECT TO U_RMAN1 IDENTIFIED BY rman USING ‘TEST2’;
select * from tbl_DBlink@test1;
DROP DATABASE LINK TEST1;
http://www.orafaq.com/wiki/Database_link
CREATE DATABASE LINK TESTDB CONNECT TO U_RESTORE IDENTIFIED BY restore USING ‘TESTDB’;
select * from tbl_DBlink_1@testdb;
DROP DATABASE LINK TESTDB;
