Oracle

Oracle SQL Loader DB link-21

Advertisements

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://stackoverflow.com/questions/2960594/how-do-i-create-a-database-link-where-remote-and-local-are-the-same-server

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;

 

I’m currently working as a SQL server DBA in one of the top MNC. I’m passionate about SQL Server And I’m specialized in Administration and Performance tuning. I’m an active member of SQL server Central and MSDN forum. I also write articles in SQL server Central. For more Click here

Leave a Reply

Your email address will not be published. Required fields are marked *

66 + = 69