Oracle export commands
Oracle Export utility provides a number of options to help you export your data for different scenarios.
Here are some examples of the most common options that Developers/DBAs use.
Export based on a query:
You can use thess options to export selected rows from a table. For example below I am exporting all records from orders table where id < 300 .
exp scott/tiger file=test1.dmp query=\”where id \< 300\” tables=orders statistics=none
Note above that I am using escape character “\”. This will be needed when you run your export in Unix/Linux environment.
Continue reading
SQL*Loader with example
SQL*LOADER WITH EXAMPLE
========================
SQL*Loader is an Oracle supplied utility used to load data in Oracle tables easily with significantly
high performance. This utility is installed by default when you install Oracle Server software. It is
also installed when you install Administrative version of Oracle Client.
It is commonly used to load data from plain flat files (text files/plain data files) into database tables.
In it’s simplest form it requires a Control File, a Data File, the SQL*Loader executable and the Target
Table(s).
Control File: As the name suggests this file controls the loading of data in an Oracle table. Data type,
data length, data file separator (called field terminator), target table etc are specified in this file.
Data File: This is a plain text file separated by usually comma (“,”) or any other Oracle understandable
characters.
SQL*Loader: This is the sqlldr.exe file supplied by Oracle.
Target Tables(s): Data can be loaded into a single table or multiple tables.
Here is a demonstration of how SQL*Loader is used.
Suppose I have an Oracle table of structure
create table table1
(empno number,
fname varchar2(20),
lname varchar2(20),
age number
);
I want to load data from a text file named mydatafile.txt which has the following data
1000,David,Selby,40
1001,Thomas,Train,31
1002,Nick,Junior,22
1003,Samantha,Dale,24
1004,Roman,Catfield,39
1005,Punnet,Gupta
1006,Dummy, Suname name does not exists, 10
To load the content of file mydatafile.txt into table table1 I’ll need to have a control file
which will look somewhat like this:
Recent Comments