DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: SQL*LOADER:Commit point reached - logical record count 64

  1. #1
    Join Date
    Jun 2007
    Posts
    17

    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:

    LOAD DATA
    INFILE '/oracle/sample.csv'
    INTO TABLE loader
    FIELDS TERMINATED BY ','
    ( confid, member, statdays )

    WHERE:
    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

    WHERE:
    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

    Kindly advise,
    Thanks in advance,
    Arpan.

    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?

  2. #2
    Join Date
    Jun 2007
    Posts
    17
    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 :~)

  3. #3
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    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
    or,
    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.

  4. #4
    Join Date
    Jun 2007
    Posts
    17
    Thank you very much,
    Follow your commands I shall.
    May the force be with you master PAVB.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width