Server Utilities :: Multithreading Not Working With Direct Path Load
May 30, 2013
Output in the sqlldr log:-
------------------------------------------------------------------------------
Path used: Direct
Insert option in effect for this table: APPEND
Trigger DEV."R_TM_BK_BORROWER" was disabled before the load.
DEV."R_TM_BK_BORROWER" was re-enabled.
The following index(es) on table "YO"."TM_BK_BORROWER" were processed:
index DEV.I_NK_TM_BK_BORR_1 loaded successfully with 1554238 keys
index DEV.I_NK_TM_BK_BORR_2 loaded successfully with 1554238 keys
index DEV.I_NK_TM_BK_BORR_3 loaded successfully with 1554238 keys
index DEV.I_NK_TM_BK_BORR_31 loaded successfully with 1554238 keys
Bind array size not used in direct path.
Column array rows : 5000
Stream buffer bytes: 256000
Read buffer bytes: 1048576
Total logical records skipped: 1
Total logical records read: 1554241
Total logical records rejected: 48
Total logical records discarded: 2
Total stream buffers loaded by SQL*Loader main thread: 7695
Total stream buffers loaded by SQL*Loader load thread: 0
------------------------------------------------------------------------------
So, I still see in the sqlldr log that the stream buffers are laoded by main thread and load thread is still not being used. SQL*Loader load thread did not offload the SQL*Loader main thread. If the load thread takes care of the current stream buffers, then it allows the main thread to build the next stream buffer while the load thread loads the current stream on the server. We have a 24 CPU server.
I am using the following parameters set to true in the sqlldr:- parallel=true , multithreading=true , skip_index_maintenance=true in the sqlldr
View 5 Replies
ADVERTISEMENT
Apr 19, 2010
I have a small confusion in direct path loading. Will direct path load ever use memory (SGA)? If yes, why it is not generating redo? If no, can we write into a block at file system level directly.
View 1 Replies
View Related
Apr 25, 2012
I have a table which is being load by sqlloader, when i load the table without direct path set to TRUE IT Works well , but when DIRECT path set to TRUE ,it comes out with the following error
SQL*Loader-702: Internal error - Unknown column for OCI_ATTR_COL_COUNT
SQL*Loader-2026: the load was aborted because SQL Loader cannot continue.
control file looks like below.
load data
BADFILE '/backup/temp/rajesh/RIO/BadFiles/FILENAME'
append into table TEMP_rio_RESP_TIME_LND
TRAILING NULLCOLS
(
INSTALLATION_ID CHAR
[code]....
data set is
V5_RIO_5NCC|78967|172.16.0.166|RioLoginSrc.asp|0.296|12/04/2012 15:27:25.703|12/04/2012 15:27:26.000|V5_RIO_5NCC||||||||||
V5_RIO_5NCC|78968|172.16.0.167|TextDialogueCentre.asp|0.015|12/04/2012 15:27:27.983|12/04/2012 15:27:28.000|V5_RIO_5NCC||||||||||
V5_RIO_5NCC|78969|172.16.0.167|RioLoginSrc.asp|57.843|12/04/2012 15:27:14.157|12/04/2012 15:28:12.000|V5_RIO_5NCC||||||||||
View 9 Replies
View Related
Apr 28, 2011
I am trying to import the database and i see the following error:
IMP-00051: Direct path exported dump file contains illegal column length imp abruptly stops. my source and destination database is as follows:
Source: 9.2.0.8
Destinatin: 11g-r2.
I used exp with the following options:
direct=y
buffer=899989898
recordlength=64000
File=1.dmp,2.dmp,3.dmp,4.dmp,5.dmp,6.dmp,7.dmp,8.dmp,9.dmp,10.dmp,11.dmp,12.dmp,
13.dmp,14.dmp,15.dmp,16.dmp,17.dmp,18.dmp,19.dmp,20.dmp,21.dmp,22.dmp,23.dmp,24.
dmp,25.dmp,26.dmp,27.dmp,28.dmp,29.dmp,30.dmp
grants=y
compress=y
FILESIZE=25g
owner=BRM
Using imp with the following option:
indexes=n
buffer=99989898
recordlength=64000
File=1.dmp,2.dmp,3.dmp,4.dmp,5.dmp,6.dmp,7.dmp,8.dmp,9.dmp,10.dmp,11.dmp,12.dmp,
13.dmp,14.dmp,15.dmp,16.dmp,17.dmp,18.dmp,19.dmp,
20.dmp,21.dmp,22.dmp,23.dmp,24.dmp,25.dmp,26.dmp,27.dmp,28.dmp,29.dmp,30.dmp
analyze=n
grants=y
fromuser=brm
touser=brm
statistics=none
why my imp failing?
View 4 Replies
View Related
Jan 22, 2013
I am using oracle 10g version 10.2.0.1.0.
Can I use user-defined functions in my sql*loader control file and load in DIRECT path mode?
View 1 Replies
View Related
Feb 25, 2011
What is the Default Path of Log File after Import Dump in Oracle 10g.
View 1 Replies
View Related
Mar 10, 2010
I want to load geometry into a table using sql*loader. My datafile contains geometry defined as WKT URL...., a standard for geometry and also Oracle has a function called sdo_util.from_wktgeometry.If I'm using a separate 'insert into' statement using this function in sql*plus, there's no problem. But if I'm using the same function in my control-file for sql*loader import, I get a sql*loader-418 error: "bad datafile for column geometrie".
Why and how I can import WKT using sql*loader?
-- data file
id;geometrie
1;POINT(120123.123 485345.789)
2;LINESTRING(123456.01 482543.21, 125764.76 483444.11)
3;POLYGON((121121.22 484394.22, 122887.444 484721.48, 122911.098 486382.45, 121005.21 486592.01, 121121.22 484394.22),(121922.56 485333.23, 122010.22 485854.83, 121922.56 485333.23))
4;MULTIPOINT((120586 483958.33),(120635 483726.11))
5;MULTILINESTRING((117948 480284, 118215 481236),(118475 481604, 120462 482822))
6;MULTIPOLYGON(((123678 481948, 124654 485215, 123678 481948),(127321 488321, 124907 483921, 127321 488321)))
[code]....
View 15 Replies
View Related
Aug 8, 2012
I would like to load a text file into an oracle table.
View 10 Replies
View Related
Mar 10, 2013
How to get ill-formatted data into Oracle table? I'm trying to load a large amount of data that is not arranged into neat columns and doesn't have proper record delimiters.
I'd like to use sql loader but I don't think that will work with unstructured data. I'm reading that perhaps using an external table would be the best way to do it. It's sample census data and I've attached a single record to look at.
View 4 Replies
View Related
Apr 6, 2010
I Have one csv file.i want to load to a table trough sql*loader.but in table 3 column is there.but in the csv file some record hav one semicolumn in last filed like this
1234;"hogit";78887;89
4567;"rtef";12565;89
how can we load...
View 6 Replies
View Related
Dec 14, 2011
I want to load data from LST file. The data format and control file is given below. It is loading the 1st line only. it is not loading the other lines. pls let me know what needs to be added in the control file to load this data?
Table Scan: |14-DEC-11 09:54 |xest | 16| 0|SYSTEM |ws_email|declare v_lst_suc da|14-DEC-11 08:32:39| 716444|XEST_USER
XEST_USER.X| | | | | |er.exe |te; v_nxt_sch date; | | |
EST_PING_RCV| | | | | | |cur_time varchar2(30| | |
D: 28609 out| | | | | | |); begin --select| | |
of 28609 Bl| | | | | | | last_date, next_dat| | |
ocks done | | | | | | |e into v_lst_suc, v_| | |
[code]....
View 10 Replies
View Related
Jun 8, 2011
i want to load jpg into my oracle database 10g throw sqlloader
i did folloing steps
step 1)
Create the table as follows
CREATE TABLE image_table (file_id NUMBER(5),
file_name VARCHAR2(30),file_data BLOB);
step 2 )
Create control file as follows
LOAD DATA
INFILE *
INTO TABLE image_table
REPLACE
FIELDS TERMINATED BY ','
(
[code].....
step 3)
Then i have run this command
F:oracleproduct10.2.0db_1in>sqlldr control=F:practicecontrol.ctl
Username:system
Password
so i got this error
SQL*Loader: Release 10.2.0.5.0 - Production on Wed Jun 8 13:47:27 2011
Copyright (c) 1982, 2007, Oracle. All rights reserved.
SQL*Loader-404: Column FILE_ID present more than once in IMAGE_TABLE's INTO TABL
E block.
View 4 Replies
View Related
Aug 3, 2011
I have data file emp.dat in that i have 10000 records. My requirement is i want to skip last 100 records when i am loading it into EMP table using SQL *LODER.
View 6 Replies
View Related
Nov 25, 2011
In a flat file 50000 records are there.In that some of the records having special charcters.From that special character record the remaing records are not loading to oracle tables. to load the remaining records,after the record which is having special characters.The data is loading using sql*loader
View 3 Replies
View Related
Nov 1, 2011
I Have Flat file with 20columns of data,My intention is to load into two tables(i.e TABLE1 WITH 10 columns,TABLE2 With remaining 10columns)
View 5 Replies
View Related
Feb 7, 2013
I'm trying to load data into a table using SQL Loader but getting a failure error below.
Log File
========
SQL*Loader: Release 11.2.0.2.0 - Production on Wed Feb 6 23:54:25 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Control File: /opt/Infor/Outbound_Marketing/7.2.2/EM/metadata/trans.ldr
Data File: /opt/Infor/Outbound_Marketing/7.2.2/EM/logs/trans.log
Bad File: trans.bad
Discard File: none specified
[code]....
View 13 Replies
View Related
Aug 27, 2010
I got an csv file which got 52 sheets. Is there a way from sqlloder to load all these 52 sheets data. I in general seperate all these sheets into different CSV files and load. So, is there a better way to load.
View 6 Replies
View Related
Jan 20, 2012
I want to load data into more tables from many files ,based on first column value,which is FILLER field.i am trying to test this scenario with two oracle tables with similar definition. and load one record on each table using WHEN/POSITION keywords. for this , i added first column as reference column in the data which i have in ctl file itself.
1st table loaded with 1st record. But, 2nd record not loading.if i missed anything with WHEN/POSITION keyword ?
This is the error in log file for 2nd table(WD1):
Record 2: Rejected - Error on table WD1, column TAB.
ORA-01841: (full) year must be between -4713 and +9999, and not be 0
Table WD1:
0 Rows successfully loaded.
1 Row not loaded due to data errors.
1 Row not loaded because all WHEN clauses were failed.
0 Rows not loaded because all fields were null.
[code]....
View 9 Replies
View Related
Apr 5, 2010
load/unload csv file using sql command
View 1 Replies
View Related
May 17, 2012
load data in multiple table using sql loader. I have IN predicate which i don't know is allowed in the sql loader or not
my control file and is as below
LOAD DATA
INFILE 'c: empdemo05.dat'
BADFILE 'c: empad05.bad'
DISCARDFILE 'c: empdisc05.dsc'
REPLACE
[code]....
i am getting below error when executing above error
SQL*Loader-350: Syntax error at line 5.
Expecting "(", found keyword when.
WHEN DEPTNO IN ('
View 4 Replies
View Related
Sep 26, 2012
i have tried it , but it load the data in text file , but i want to load this data in excel sheet in such a way that each column should be in different-2 cell of excel sheet.
SQL> spool on
SQL> spool 'd:data.text'
SQL> select * from scott.emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 300 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
14 rows selected.
SQL> spool off;
SQL>
View 4 Replies
View Related
Apr 8, 2010
I am using perl script to dynamically generate the control file.If I have data in the control file as well as in the datafile, how would i write the control file in that case. Is the below one correct?
load data
INFILE '*'
INFILE '/export/home/test/test.csv'
INSERT INTO TABLE EMP fields
terminated by "," optionally enclosed by '"'
trailing nullcols
( empno, empname, sal, deptno )
[code]....
Is there any way that if my control file contains half of the data and my data file contains the other half of the data can i club this data into a logical record in the control file to populate the DB?
My exact 2nd requirement is, my DB contains 5 cols and for 1 col the data is common(countryName) which i have to pass to the control file dynamically and the .csv file contains the data for the other four cols. How could i combine these in the ctrl file and populate the DB?
so if the DB contains CountryName, empid, ename, sal and dept..I will get the CountryName to the ctrl file and csv contains the data for empid, ename, sal and dept. How would i combine these data into a logical record and populate the DB?
View 12 Replies
View Related
Apr 17, 2013
I have table named purchage with 2 columns (order_no number,order_date date) in my database. I want to load the data from a file into that table. The below is the file format
100,4/3/2013 1:18:18 AM
101,4/3/2013 1:18:18 AM
102,4/3/2013 1:18:18 AM
103,4/3/2013 1:18:18 AM
104,4/3/2013 1:18:18 AM
105,4/3/2013 1:18:18 AM
106,4/3/2013 1:18:18 AM
how to load the date filed along with the time stamp.
View 2 Replies
View Related
Oct 18, 2012
My loader start and say commit reach logical records 8 as there are 8 records but donot load them and write them into badfile. there is no logs file generation happing so unable to trace.
table
CREATE TABLE ERS_NRT_SRC
(
POLL_ID NUMBER(10) NULL,
TIME_OF_POLL DATE NULL,
SERVICE_DESC VARCHAR2(50 BYTE) NULL,
LOCATION_AGENT VARCHAR2(30 BYTE) NULL,
TRANSACTION_NAME VARCHAR2(50 BYTE) NULL,
[code]...
View 6 Replies
View Related
Oct 26, 2012
I am having query regarding sql loader. my data file is comm(,) seperated and I want to load the whole file in oracle table 'bill_temp' except 1st column data of data file.
e.g.
File name: bill_file.dat
fields seperated by comma ','
values are like
emp_id,emp_name,emp_sal,join_date
oracle table bill_temp having the below column:
emp_name,emp_sal,join_date
Here I want load the emp_name,emp_sal and join_date into oracle table bill_temp.
emp_id should not get loaded into table.
Is there any way to skip the loading of particular column data from data file into table?
View 12 Replies
View Related
Sep 18, 2010
I had a requirement of loading flatfile into staging table using SQL Loader, One of the columns in the the Flat file is having values FALSE or TRUE and my requirement is that I load 0 for FALSE and 1 for TRUE which can be achieved by simple DECODE function...I did use decode and tried to load several times but did not work.
INFILE 'sql_4ODS.txt'
BADFILE 'SQL_4ODS.badtxt'
APPEND
INTO TABLE members
FIELDS TERMINATED BY "|"
[code]...
I did try putting a trim as well as SUBSTR but did not work....the cloumn just doent get any values in the output (just null or say free space)
View 5 Replies
View Related
Jul 17, 2010
My requirement is to to truncate the table and load it with the data present in file. In the control file, I used the "TRUNCATE" command as well.In case, if the file has some invalid data and sqlldr fails, my existing data will be lost. Is there any option in which the sqlldr does not TRUNCATE the table in case of a failure.
View 6 Replies
View Related
Jan 25, 2012
sqlldr works fine. but, i have primary keys defined in the table. So the SQL loader throws error that
SQL*Loader-951: Error calling once/load initialization
ORA-26002: Table WD has index defined upon it.
How should i change this load type to "normal" in sqlldr ? I think sqlldr made for bulk load purpose only.
View 3 Replies
View Related
Mar 26, 2012
I want to load single column data into multiple columns.
View 3 Replies
View Related
Jul 22, 2011
I have 780(12*65) csv files generated from 65 databases.Now I have to load this 780 csv files into 12 tables created in my database for some monitoring and reporting purpose.to call the sql loader I am plannig to create 780 lines like below.
sqlldr abc@tns/pwd control='E:htmlctlhtml_broken_jobs_rpt.ctl' log='E:htmlreportloghtml_broken_jobs_rpt.log'
sqlldr abc@tns/pwd control='E:htmlctlhtml_db_size_rpt.ctl' log='E:htmlreportloghtml_db_size_rpt.log'
sqlldr abc@tns/pwd control='E:htmlctlhtml_fragmentation_rpt.ctl' log='E:htmlreportloghtml_fragmentation_rpt.log'
sqlldr abc@tns/pwd control='E:htmlctlhtml_index_stats_rpt.ctl' log='E:htmlreportloghtml_index_stats_rpt.log'
sqlldr abc@tns/pwd control='E:htmlctlhtml_invalid_object_rpt.ctl' log='E:htmlreportloghtml_invalid_object_rpt.log'
sqlldr abc@tns/pwd control='E:htmlctlhtml_long_running_queries_rpt.ctl' log='E:htmlreportloghtml_long_running_queries_rpt.log'
we know creating 780 control files is the difficult task.So I have created only 12 control files. is there any mechanism to pass a varible (planning to declare it in the sqlldr line) to the infile clause like below in sql loader?
infile "E:htmlreportoutput&a_html_broken_jobs_rpt.csv"
here a is the variable name. it will change every 12 csv files once.
or
is there anyother way to achive this?
View 8 Replies
View Related
