1 2 3 4 Previous Next 55 Replies Latest reply on Apr 29, 2013 8:36 AM by John Stegeman Go to original post
      • 15. Re: Urgently - Oracle_loader, problem with external tables
        John Spencer
        "User oracle is owner of log file"

        Yes, that is the way that all of the Oracle file-based utilities work. Whenever you write a file from Oracle, it is one of the O/S processes that does the actual writing. These processes (i.e. all of the background processes as well as all of the processes spawned by connections to the database) run as the user that installed oracle. In your case that appears to be oracle. So, any files those processes create will be owned by oracle.

        If you correctly changed the permission on the directories, the oracle O/S user will be able to write those files.

        At the unix prompt from the /dwh/files directory post the output from ls -ld logs and ls -ld bad.

        BluShadow:

        I do the *nix thing every day, it is Windows I don't get :-)

        John
        • 16. Re: Urgently - Oracle_loader, problem with external tables
          deep222
          Hi.

          $ ls -ld log
          drwxrwxr-- 2 dwh dba 1024 Jul 27 09:07 log
          $ ls -ld bad
          drwxrwxr-- 2 dwh dba 96 Jul 27 09:05 bad


          So if I understand, if we change permissions to
          chmod o+wx log
          and
          chmod o+wx bad

          we will then have:

          $ ls -ld log
          drwxrwxrwx 2 dwh dba 1024 Jul 27 09:07 log
          $ ls -ld bad
          drwxrwxrwx 2 dwh dba 96 Jul 27 09:05 bad


          But then I still received a message when selecting external table:

          ORA-29913: error in executing ODCIEXTTABLEOPEN callout
          ORA-29400: data cartridge error
          KUP-04063: unable to open log file dwh_sources.log
          OS error No such file or directory
          ORA-06512: at "SYS.ORACLE_LOADER", line 19

          Do I need to restart database or maybe to do some commit in unix because I have
          drwxrwxrwx for other users to log and bad dir, but still is an error.

          Any help?

          Message was edited by:
          deep222
          • 17. Re: Urgently - Oracle_loader, problem with external tables
            John Spencer
            Does the owner of the external table have read and write permissions on the directory objects defined for the external table?

            As the owner of the of the external table, try:
            SELECT table_name, privilege, grantee
            FROM all_tab_privs
            WHERE table_name in ('BAD_FILES_DIR', 'DSC_FILES_DIR', 'LOG_FILES_DIR')
            You may also want to check all_directories to see if the directory object LOG_FILES_DIR still points to the directory you think it does.

            John
            • 18. Re: Urgently - Oracle_loader, problem with external tables
              deep222
              1. Does the owner of the external table have read and write permissions on the directory objects defined for the external table?

              Yes, all directories have R/W to user who owns external tables.

              2.
              select table_name, privilege, grantee
              from all_tab_privs
              where table_name in ('FILES_DIR', 'BAD_FILES_DIR','LOG_FILES_DIR','DSC_FILES_DIR')

              Then I get 6 rows selected

              All directories have read, write to user who owns external tables.

              3. You may also want to check all_directories to see if the directory object LOG_FILES_DIR still points to the directory you think it does.

              Yes, I did. I did check and this is true. All locations are correct.

              Which unix user is used when selecting from tables?
              Does Oracle always use oracle user from oinstall group?

              Deep

              Message was edited by:
              deep222
              • 19. Re: Urgently - Oracle_loader, problem with external tables
                John Spencer
                Oracle will always use the O/S user and group that is running the executables. Based on your earlier posting, that would appear to the oracle:oinstall.

                Who shows as the process owner when you do ps -ef |grep pmon at the unix prompt?

                What does ls -l $ORACLE_HOME/bin/oracle show as the file owner:group, and the permission flags?

                Was your database re-started between the time this last worked and when it stopped working?

                Are you sure about the permissions on the directory object. You say you got 6 rows from the query against all_tab_privs. Given that you gave 4 directory objects, I would expect to see 8 rows, one for read privilege and one for write privilege from each of the directory objects. I could also see 7 rows if the user only has read on the files_dir object. However, 6 rows implies to me that you are missing a write provilege somewhere.

                John
                • 20. Re: Urgently - Oracle_loader, problem with external tables
                  deep222
                  1. Who shows as the process owner when you do ps -ef |grep pmon at the unix prompt?

                  $ ps -ef |grep pmon
                  root 658 1 0 May 25 ? 3:57 ipmon -sD
                  oracle 3921 1 0 May 25 ? 23:23 asm_pmon_+ASM
                  oracle 23495 25074 0 11:07:56 pts/ta 0:00 grep pmon
                  oracle 534 1 0 Jun 1 ? 30:50 ora_pmon_DWH



                  2. What does ls -l $ORACLE_HOME/bin/oracle show as the file owner:group, and the permission flags?

                  $ ls -l $ORACLE_HOME/bin/oracle
                  -rwsr-s--x 1 oracle oinstall 259432664 Sep 28 2006 /ora_home/10.2.0/db/bin/oracle


                  3. Was your database re-started between the time this last worked and when it stopped working?

                  I don't know, ora DBA is on vacation.
                  Maybe the database was restarted, but if yes, could be this problem?


                  4. My mistake, I wrote 6 instead of 8.

                  It is 8 rows.

                  select table_name, privilege, grantee
                  from all_tab_privs
                  where table_name in ('FILES_DIR', 'BAD_FILES_DIR','LOG_FILES_DIR','DSC_FILES_DIR')

                  I'm giving up. ;-(
                  • 21. Re: Urgently - Oracle_loader, problem with external tables
                    SamB
                    Before you give up, I would try it without the logfile to determine if that is in fact the problem:
                    CREATE TABLE EXT_DWH_SOURCES
                    (
                    DWH_SOURCE_CODE VARCHAR2(10 BYTE),
                    DWH_SOURCE_NAME VARCHAR2(100 BYTE)
                    )
                    ORGANIZATION EXTERNAL
                    ( TYPE ORACLE_LOADER
                    DEFAULT DIRECTORY FILES_DIR
                    ACCESS PARAMETERS
                    ( RECORDS DELIMITED BY NEWLINE
                    CHARACTERSET EE8MSWIN1250
                    STRING SIZES ARE IN BYTES
                    BADFILE BAD_FILES_DIR:'dwh_sources.bad'
                    DISCARDFILE DSC_FILES_DIR:'dwh_sources.dsc'
                    NOLOGFILE
                    --LOGFILE LOG_FILES_DIR:'dwh_sources.log'
                    FIELDS
                    LRTRIM
                    MISSING FIELD VALUES ARE NULL
                    (
                    "DWH_SOURCE_CODE" POSITION (1:2) CHAR,
                    "DWH_SOURCE_NAME" POSITION (3:52) CHAR
                    )
                    )
                    LOCATION (FILES_DIR:'dwh_sources.txt')
                    )
                    REJECT LIMIT UNLIMITED
                    NOPARALLEL
                    NOMONITORING;
                    If the error goes away then I would check that the LOG_FILES_DIR is pointing to a valid directory.
                    • 22. Re: Urgently - Oracle_loader, problem with external tables
                      deep222
                      If I remove all 3 entries for log, dsc and bad files and put nologfile and then create external table now I get error for FILES_DIR:

                      ORA-29913: error in executing ODCIEXTTABLEOPEN callout
                      ORA-29400: data cartridge error
                      KUP-04040: file 'dwh_sources.txt in FILES_DIR not found
                      ORA-06512: at "SYS.ORACLE_LOADER", line 19

                      I repeat, all direcories are valid and have right way to a files.

                      And they are granted to user who owns external tables.
                      • 23. Re: Urgently - Oracle_loader, problem with external tables
                        SamB
                        Run this query as the user which is trying to access the external tables.

                        select * from all_directories;

                        See anything strange?
                        • 24. Re: Urgently - Oracle_loader, problem with external tables
                          deep222
                          From the user who owns external tables - stg_user


                          select * from all_directories


                          OWNER     DIRECTORY_NAME     DIRECTORY_PATH

                          SYS     LOG_FILES_DIR     /dwh/files/log
                          SYS     DSC_FILES_DIR     /dwh/files/dsc
                          SYS     BAD_FILES_DIR     /dwh/files/bad
                          SYS     FILES_DIR     /dwh/files

                          That's ok!! User stg_user have grants to all directories, read and write.

                          Why does sys own directories?

                          Message was edited by:
                          deep222
                          • 25. Re: Urgently - Oracle_loader, problem with external tables
                            SamB
                            SYS owns all directories in the database.

                            Ok so we now know that the problem has something to do with the file itself and not the directories.

                            Oracle can't see this file for some reason.

                            Message was edited by:
                            SamB
                            • 26. Re: Urgently - Oracle_loader, problem with external tables
                              deep222
                              Have you got any idea more?
                              • 27. Re: Urgently - Oracle_loader, problem with external tables
                                SamB
                                Try with a simple table and another file (if you have access to the OS).
                                CREATE TABLE test(test_column VARCHAR2(10 BYTE))
                                ORGANIZATION EXTERNAL( TYPE ORACLE_LOADER
                                DEFAULT DIRECTORY FILE_DIR
                                ACCESS PARAMETERS
                                ( RECORDS DELIMITED BY NEWLINE
                                )
                                LOCATION ('test.txt'))
                                REJECT LIMIT UNLIMITED
                                Message was edited by:
                                SamB
                                Use the same directory for now.
                                • 28. Re: Urgently - Oracle_loader, problem with external tables
                                  deep222
                                  I put test.txt thru FTP, also I have data inside it.

                                  CREATE TABLE test( TEST_COLUMN VARCHAR2(10 BYTE))
                                  ORGANIZATION EXTERNAL( TYPE ORACLE_LOADER
                                  DEFAULT
                                  DIRECTORY FILES_DIR
                                  ACCESS PARAMETERS ( RECORDS DELIMITED BY NEWLINE)
                                  LOCATION ('test.txt'))REJECT LIMIT UNLIMITED

                                  ORA-29913: error in executing ODCIEXTTABLEOPEN callout
                                  ORA-29400: data cartridge error
                                  KUP-04063: unable to open log file TEST_17730.log
                                  OS error No such file or directory
                                  ORA-06512: at "SYS.ORACLE_LOADER", line 19

                                  If i put NOLOGFILE, I have the same problem as before:

                                  ORA-29913: error in executing ODCIEXTTABLEOPEN callout
                                  ORA-29400: data cartridge error
                                  KUP-04040: file test.txt in FILES_DIR not found
                                  ORA-06512: at "SYS.ORACLE_LOADER", line 19

                                  The same problem.

                                  Message was edited by:
                                  deep222
                                  • 29. Re: Urgently - Oracle_loader, problem with external tables
                                    SamB
                                    Ok the problem seems to be accessing that directory. To confirm this, is it possible for you to create another directory and try with that?

                                    (Sorry I know you are going in circles here).

                                    Message was edited by:
                                    SamB