Server Utilities :: Import Performance Using Sql Loader
Apr 6, 2010
I have a text file of 175G with the delimeter as '|*'. I want to import it using sql *loader. I know that sql *loader can load data parallely. I can employ two schemes for importing it using sql *loader
a) with the text file being imported all at once using option parallel = true
b) with the single text file being broken down into files of smaller size(say 5G each or more) and then imported in parallel using parallel = true.
I can import it in somewhat powerful server with 128G of ram, 16 intel CPus and abundant hard disk space.Now my question is which option would be better in performance (in terms of time) it takes to import data?I donot have sufficient time to test both of these approaches.
The problem here is , When you try to import to a table which has same columns . I skipped the first line when loading . The issue here is the second field is getting split in to the two columns . for e.g. :- Default goes to Child and Remaining goes to the Alias.
infarct there is a tab at the end of the each line. How to set the Sql loader settings correctly so that I can populated the end column in CHILD column only.!!!!
I have installed Oracle server and SQL Server on separate machines which cause me a time delay of 21 seconds for each execution. Why executions delay? I have set RPC out (true).
Note: My main concern is either if the query is correct/incorrect it executes for 21 seconds._
Another case when I have both servers on the same machine it executes in milliseconds. I have tried Following methods in SQL SERVER.
*1, Using OPENQUERY:* SELECT * From OPENQUERY(Linked Server Name,’Select * from OracleTableName ‘)
*2, Using Exec:* DECLARE @sql NVARCHAR(MAX); SET @sql =(’Select * from OracleTableName ‘); EXEC (@sql) AT Linked Server Name ;
How to reduce the time delay caused for the execution?
I will have to proceed with Oracle 9 database refresh from production server to integration server. 5 biggest schemas must be exported and imported. They constitute 97% space used in a database. This is very big database so I would like to be sure that everything will go smoothly. That is why i want to ask you some questions.
Have you got any clues for me before I start with exp/imp? From my side i will tell you that I will have to exp/imp schema by schema because there is small space both on production and integration disk for a dump. First thing I thought are dependencies between schemas that are exported and that which are not, and also between schemas that are exported/imported one by one.
This is procedure that I plan:
For every schema that is to be refreshed { 1. Export schema with ROWS=N CONSTRAINTS=Y 2. EXPORT schema with ROWS=y CONSTRAINTS=N 3. Import schema from step one 4. Disable all the foreign key constraints using ALTER TABLE DISABLE CONSTRAINT. 5. Import schema with rows } ALTER TABLE ENABLE CONSTRAINT
With above procedure i think that I will avoid problems with dependencies between schemas exported/imported one by one. But my concern is if there are any dependencies between those schemas and schemas that are not exported. Is there an way to check it before refresh ?
How do i map student_newId field to student_id field in STUDENT DB table so that new id should be inserted in student_id column. How do i specify the mapping in control file. I dont want to create a new column in student table. In control file i will specify the below, Is this a best approach?. Do we have any othe way?
I installed oracle 10G complete so I can have everything. But now I cannot run sql loader. I check my oracle devsuitehome directory and I cannot find sqlldr.exe
I need to install sql loader separately? I can't find sql loader installer on web.
- 1 - M or P: indicates which table to insert: MASTER or PARENT; - 2 - M or A or B: indicates MASTER, PARENT_A, PARENT_B; - 3:18 - DATA.
Based on the values above, what I need to do is:
1. Load a line to MASTER_TABLE; 2. Load a line to PARENT_TABLE_A pointing to its relative line in MASTER_TABLE; 3. Load a line to PARENT_TABLE_B pointing to its relative line in MASTER_TABLE; 4. In the original file line, there is nothing I can use to join a MASTER line with a PARENT line.
The result would be: MASTER_ID PARENT_DATA 1 PARENT_TABLE_A1 1 PARENT_TABLE_B1 2 PARENT_TABLE_A2 2 PARENT_TABLE_B2
I tried to use both: SEQUENCE and Sequence.NextVall (CurrVal) but they only work when using ROWS=1 and the file I need to load has millions of rows, so I need direct path loading.Also, I read about External Table, but it does not suit my needs because the Application server is not the same as Database server, which is needed by external tables.
in this case is better load the data to a temporary table and then insert to the other tables, I found almost the same question in the topic pointed by the link below: URL....
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".
My data file contains records with RECORD_TYPE '01', '02', '03' and '04' in position (30:31) I use sql loader to load this data into a table. I need to load ONLY rows with RECORD_TYPE ='04'. I have to output all the other rows into a file, so I decided to use DISCARD file. Now, those with RECORD_TYPE ='4' have to be loaded into different columns depending on the value in position (267:268).
So, my ctl file should look something like:
WHEN (30:31) = '04' into table MYDATA WHEN (267:268) != 'O ' into table MYDATA WHEN (267:268) = 'O '
and whatever is not '04' goes to discard file.
I tried to use
into table MYDATA WHEN (30:31) = '04' and (267:268) != 'O ' into table MYDATA WHEN (30:31) = '04' and (267:268) = 'O '
but I don't get the right result in terms of the discard file.
Is there any way to put together all these conditions?
The SQL loader somehow is loading only first record. The data file is a csv and the end of line character is a new line. Some text fields have multiple new lines.
Here is my control file
load data infile '/home/devo/c0397105/RuleImport/testLoad/dummyLoad.csv' Truncate into table DUMMY_LOAD_TABLE fields terminated by "," optionally enclosed by '"' ( ID "to_number(:ID)",REQUESTED_GROUP,PURPOSE,COMMENTS) [code]........
We don't have Retail resource type as Dependent System now; the rule will be changed later when Dependent Systems can accept resource types other than application"
I am using sqlloader for loading the data into database by using csv file.My csv file is delimited by comma in that i am having a column which is having the , and line feeds targeted to load into a long data type.for example as below
descri,dfdfdfd,dfdfdf, sdfsdf, dfsdfd,
i want to move this column data into a single table column.But due to because of delimited "," it is splitting into number of columns
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)
I have a small problem when I am trying to load data into a table using SQL Loader. The data I am trying to load should be a number, but it is in the format '999,999,999 USD'. When I try to load the data, I am getting an invalid number error, due to the USD (I have already accounted for the thousands seperators). My question is, how can I load the data as a number with USD in the format?
LOAD DATA APPEND INTO TABLE IPGITLREDATA WHEN ITL_REC_TYPE = 'D' FIELDS TERMINATED BY ',' TRAILING NULLCOLS (
[code].....
The data file might have a value of " D " instead of "D" for ITL_REC_TYPE and ITL_REC_TYPE is in the WHEN clause. How can I check for the trimmed value of ITL_REC_TYPE in the WHEN clause ?
I want to populate totale number of record in the file. Usually i get 10000 records per file and i load them using sql loader.I want to also insert the number of records in file while loading the data in table.
How can i achive it.
structure of control file is
load data BADFILE '/backup/temp/rajesh/RIO/BadFiles/FILENAME' append into table ERS_RIO_SRC TRAILING NULLCOLS ( INSTALLATION_ID CHAR
I have loaded 14324590 rows into target tables using sql*loader.I used below consideration during the load process.
1) direct=true,parllel=true 2) unrecoverable 3) disable all indexes and triggers.
But, sql loader takes 21 minutes to load 14324590 rows in database? tuning sql loader process? we cannot change data file because it has given by client.
from Control file, data is getting popupalated in TEST_USER and TEST_TITLE similarly if remove
INTO TABLE TEST_TITLE( TX_SID POSITION(1:3) CHAR, ID_TITLE "get_title_id(:TITLE_NAME)" ,
[code]...
from Control file, TEST_USER and TEST_ROLE is getting populated.
Here RD_ROLE_MASTER script
CREATE TABLE RD_ROLE_MASTER ( "ID_ROLE" NUMBER(38,0) NOT NULL ENABLE, "TX_ROLE_NAME" VARCHAR2(20 BYTE) NOT NULL ENABLE
[code]...
Here is RD_TITLE_MASTER script CREATE TABLE RD_TITLE_MASTER( "ID_TITLE" NUMBER(38,0) NOT NULL ENABLE, "TX_TITLE_NAME" VARCHAR2(25 BYTE) NOT NULL ENABLE);
Insert into RD_TITLE_MASTER (ID_TITLE,TX_TITLE_NAME) values (7,'RED_LOB_ESCALATION_L1'); what is the problem?
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
My requirement is to load the data from feed file into two tables based on the value of a column in feed file.
say column in feed file is "Activity_Value" . If the Activity_value is 10, the data from feed file should be loaded into Table A and If the Activity Value is 20 the data from feed file should be loaded into Table B.
we have to load some serious amount of data into an oracle database. We have extracted data out of another database and the number format for negative values is e.g. 50.00- / Positive values doesn't have a plus in e.g. its 50.00.
Is there a way to format the 50.00- to like -50.00 during the load with SQL loader? I was thinking of a regular expression?
I have a file having 10k of rows and I need to use *sql loader to insert the data into table. Below are the information.
SQL> desc EMPLOYEE Name Type EMP_ID NUMBER(10) -- PrimaryKey EMP_NAME VARCHAR2(30) DEPT_ID NUMBER(10) -- ForeignKey from DEPARTMENT
SQL> desc DEPARTMENT Name Type DEPT_ID NUMBER(10) DEPT_NAME VARCHAR2(30)
myFile.txt ------------ 1,Edward,Account 2,Andrew,Finance 3,Sam, IT
CONTROL FILE (SQLLOADER) ------------ load data infile myFile.txt append into table EMPLOYEE FIELDS TERMINATED BY ',' (EMP_ID, EMP_NAME, DEPT_ID ) <--- ?? What should do in here
what should i do in this line because the value that i want is DEPT_ID but the file is giving the DEPART_NAME. If there any sql statement can be used in control file?
My oracle is sitting on UNIX, i have a sql loader scripts which load the data in oracle at every 10 min and bad files is written into a directory. since the file names are same it overwrite the badfiles in case of error record. i can devise a code to write the bad file with different name. I want to write error record into oracle table, is this possible and how can i achieve ?
I'm having a problem with SQL loader and the control file. I want to load a delimited file. The script will eventually be automated where the file name is passed in to the script, it's not a static name.
It's a simple SQL loader Unix script that I have created as follows
