SQL*LOADER:Commit point reached - logical record count 64
Hello everyone, I am using ORACLE10g on Red Hat Linux and I am having trouble using SQL*Loader to insert a .CSV file into a table i created. I have listed the details below and would appreciate all help.
My control file (loader1.ctl) looks like this:
INTO TABLE loader
FIELDS TERMINATED BY ','
( confid, member, statdays )
SAMPLE.CSV is the file that I want to insert into the table.
LOADER is the table I want to insert sample.csv into
LOADER has three columns all of which are 'NUMBER'
In order to launch SQL*Loader I use the following command:
$ sqlldr scott/tiger control=loader1.ctl
User Scott was created earlier today for this purpose only and was granted DBA privileges
ERROR (latest ):
[oracle@linux3 ~]$ sqlldr scott/tiger control=loader1.ctl
SQL*Loader: Release 10.1.0.4.0 - Production on Fri Jul 6 21:51:42 2007
Copyright (c) 1982, 2004, Oracle. All rights reserved.
Commit point reached - logical record count 64
At this point if I check the table 'loader' with a select count (*) it shows that the table has 0 lines btu if I try to shtudown the database oracle says:
ORA-01097: cannot shutdown while in a transaction - commit or rollback first
I figured that maybe there was something wrong with the '*.csv' file and so I tried a couple of different files with the same results (are they supossed to be opened with the calc. A few times when I ran the loader immediately after receiving this error i was told that I can not INSERT in to a non-empty table, (count (*) still shows the table to be empty) I still changed the control file from "INTO loader" to "APPEND INTO loader" but with the same error about logical count.
I suspect that there might be some error regarding the user, maybe I should not use 'scott' or maybe 'scott' needs special permissions to do his job.
ALSO, if there any timing issues about updating anyting (as with the pfile) I am not aware of them. I have three open terminals- in root, oracle, and SQL and make changes in parallel.
I thought that I could do it on my own but I guess I was wrong
Thanks in advance,
PS: During the day I felt that I shoud grant 'sysdba' to 'scott' and bungled up somthin with hte password file so everytime I mount the database (successfully) I get this:
ERROR at line 1:
ORA-01990: error opening password file '/oracle/OracleHomes/db10g/dbs/orapw'
ORA-27037: unable to obtain file status
Additional information: 5
I doubt that this has anything to do with the problem does it?
I guess I fiexed it :~) -- well kinda,
My biggest mistake was that I wanted to use Scott. I created another table as system and then ran SQL*Loader as system and it works fine. While I am VERY happy I figured it out on my own, I am sure there is a lot more to it.
I will really appreciate all your feedback and also- how do I fix the problem with password file?
I wonder if this is how you guys learned ORACLE (make one mistake while fixing another in an infinite loop :~)
1- SQLLoader Issue:
You don't need to do it as system to get it done.
2- Password File Issue:
Relax, I'm not going to ask what you did trying to grant privs.
To fix it you can either set
REMOTE_LOGIN_PASSWORDFILE = NONE
which will ignore password file -assuming you don't need it
recreate the password file using
orapwd file=path\PWD.ora password=oracle
Remember to set the path to the one in the error message i.e.: /oracle/OracleHomes/db10g/dbs/orapw
Last edited by PAVB; 07-07-2007 at 06:11 PM.
Pablo (Paul) Berzukov
Author of Understanding Database Administration
available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
Thank you very much,
Follow your commands I shall.
May the force be with you master PAVB.
Click Here to Expand Forum to Full Width