Results 1 to 3 of 3
  1. #1
    Join Date
    May 2002
    Posts
    62

    Question Unanswered: SQL Loader (sqlldr) problem in Oracle 10g

    Hi,

    We recently upgraded to Oracle 10.2.0.2 from Oracle 9.2.

    Our OS is: Unix AIX

    After the upgrade we are facing a strange problem with our sql loader. We load several flat files everyday one after the other into our flat tables using sql loader. After the upgrade, the sql loader hangs completely on a file (at random) and sits there for ever. No error in the logs or anywhere else. No code changes were made after the upgrade.

    Here is the syntax we are using from the Unix script to invoke the sql loader.

    sqlldr PARFILE=<file with userid=user/password@dbname> data=data_file.dat control=control_file.ctl log=log_file.log bad=bad_file.bad discard=discard_file.dsc errors=999 direct=true

    Same syntax is used repeatedly from a Unix script for all the files and all the tables. But it works fine one day for all the files/tables and fails on other day for a file (random, could be any).

    No errors found anywhere. Unix command 'ps -ef' says sqlldr is executing. When I kill using 'kill -9' command, the sqlldr continues with next file without any problem. I tested loading the same file on our test system (10g, Unix AIX) and it works just fine.

    Did anyone come across the same problem with Oracle 10g ? Any inputs are greatly appreciated.

    Thanks
    -Bheem

  2. #2
    Join Date
    Nov 2006
    Location
    Indianapolis
    Posts
    90
    Does the same file that fails to load, loads OK if run a 2nd time?

    I would look at what it's doing in the database, see if it's even connected...

  3. #3
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by bheemsen
    But it works fine one day for all the files/tables and fails on other day for a file (random, could be any).
    Do you have a "TRUNCATE TABLE" in your control file? If so this this could be related to locks beeing held on the table. If any other process is accessing the table, then SQL*Loader cannot truncate the table.
    Next time this happens I would check Oracle sessions and locks rather than OS processes.

Posting Permissions

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