Load a Partition with SQL*Loader

In this post we will see how we can insert records in specific partition using sql*loader.

Step 1 : Create a .dat file which contains records

[oracle@prod-18c u01]$ cat test_loader.dat 
1,JAGRUTI,AHM
2,ADITI,AHM
3,ROOPAM,AHM
[oracle@prod-18c u01]$

Step 2 : Create .ctl file

[oracle@prod-18c u01]$ cat test_loader.ctl 
LOAD DATA
APPEND
INTO TABLE TEST_LOADER PARTITION (L1)
FIELDS TERMINATED BY ","
( NO ,NAME ,CITY)
[oracle@prod-18c u01]

In above output we can see I am trying to insert records in L1 partition of TEST_LOADER table.

Step 3 : Prepare a sqlldr script with above two files.

sqlldr jagruti/oracle control=test_loader.ctl log=test_loader.log data=test_loader.dat

Let us run this command on OS prompt and see what happens.

[oracle@prod-18c u01]$ sqlldr jagruti/oracle control=test_loader.ctl log=test_loader.log data=test_loader.dat

SQL*Loader: Release 12.2.0.1.0 - Production on Thu Aug 1 23:50:19 2019

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

Path used:      Conventional
Commit point reached - logical record count 3

Table TEST_LOADER, partition L1:
  3 Rows successfully loaded.

Check the log file:
  test_loader.log
for more information about the load.
[oracle@prod-18c u01]$ 

Step 4 : Let us select from the table and see

SQL> SELECT * FROM TEST_LOADER PARTITION (L1);

	NO NAME 					      CITY
---------- -------------------------------------------------- --------------------------------------------------
	 1 JAGRUTI					      AHM
	 2 ADITI					      AHM
	 3 ROOPAM					      AHM

SQL> 

So using above steps we can insert records in specific partition.

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

Skant Gupta’s LinkedIn: www.linkedin.com/in/skantali/

Joel Perez’s LinkedIn: Joel Perez’s Profile

LinkedIn Group: Oracle Cloud DBAAS

Facebook Page: OracleHelp