title: SQL*Loader-926: OCI error while executing delete/truncate ORA-01031: insufficient privileges
body: <p>I have 12 <code>.csv files</code> which should be loaded in a <strong>staging table</strong> and after it loaded the data to the <strong>staging table</strong> it should load the data <strong>grouped by parameters</strong> into another <strong>table</strong>. It throws an error by <strong>creating the table</strong>. It is missing privileges.</p>
  
  <p>GABER_RIGHTS.SQL:</p>
  
  <pre><code>GRANT SELECT, DELETE, INSERT, UPDATE ON FOOTBALLCLUB.FACT_GABER_FOOTBALLCLUB                    TO GABER;
  GRANT SELECT, DELETE, INSERT, UPDATE ON FOOTBALLCLUB.FACT_GABER_FOOTBALLCLUB_STA        TO GABER;
  GRANT SELECT, DELETE, INSERT, UPDATE ON FOOTBALLCLUB.DIM_PLAYER                TO GABER;
  GRANT SELECT, DELETE, INSERT, UPDATE ON FOOTBALLCLUB.DIM_TEAM                   TO GABER;
  GRANT SELECT, DELETE, INSERT, UPDATE ON FOOTBALLCLUB.DIM_TRAINER                  TO GABER;
  exit;
  </code></pre>
  
  <p>gaber_footballclub.sh:</p>
  
  <pre><code>#!/bin/bash
  
  sqlplus system/oracle @FOOTBALLCLUB_GABER_CREATE_USER.sql
  sqlplus gaber/oracle @FOOTBALLCLUB_GABER_CREATE_TABLES.plsql
  sqlplus gaber/oracle @FOOTBALLCLUB_GABER_CREATE_TABLES.sql
  sqlplus system/oracle @GABER_RIGHTS.sql
  
  for FILE_NAME in `ls ../data/FACT_FOOTBALLCLUB_GABER*.csv`
  do
     export BASE_NAME=`basename $FILE_NAME`
     export JUST_NAME=`echo $BASE_NAME | sed -e 's/\..*//'`
     echo $FILE_NAME
     echo $BASE_NAME
     echo $JUST_NAME
  
  
  sed -e 's/"//g' ../data/$FILE_NAME | dos2unix &gt;../data/FACT_FOOTBALLCLUB_GABER.csv 
  sqlldr gaber/oracle data=../data/FACT_FOOTBALLCLUB_GABER.csv control=gaber_footballclub_staging.ldr log=../log/$JUST_NAME.log bad=../log/$JUST_NAME.bad errors=20
  
     sqlplus gaber/oracle &lt;&lt;!
        INSERT INTO FOOTBALLCLUB.FACT_GABER_FOOTBALLCLUB
        SELECT 
     ID,
     Sum(NUMBER_OF_PLAYERS) as NUMBER_OF_PLAYERS,
     Sum(GOALS_SHOT) as GOALS_SHOT,
     Sum(GOALS_GOT) as GOALS_GOT,
     Sum(YELLOW_CARDS) as YELLOW_CARDS,
     Sum(RED_CARDS) as RED_CARDS,
     Sum(MINUTES_PLAYED) as MINUTES_PLAYED,
     DATE_PLAYED,
     TEAM_ID,
     TRAINER_ID
        FROM   FOOTBALLCLUB.FACT_GABER_FOOTBALLCLUB_STA
        GROUP BY ID, DATE_PLAYED, TEAM_ID, TRAINER_ID;
        exit;
  !
  done
  </code></pre>
  
  <p>gaber_footballclub_staging.ldr:</p>
  
  <pre><code>LOAD DATA
  REPLACE
  INTO TABLE FOOTBALLCLUB.FACT_GABER_FOOTBALLCLUB_STA
  FIELDS TERMINATED BY ','
  (
     ID,
     NUMBER_OF_PLAYERS,
     GOALS_SHOT,
     GOALS_GOT,
     YELLOW_CARDS,
     RED_CARDS,
     MINUTES_PLAYED,
     DATE_PLAYED  DATE "YYYY-MM-DD",
     TEAM_ID,
     TRAINER_ID
  )
  </code></pre>
  
  <p>FOOTBALLCLUB_GABER_CREATE_USER.sql:</p>
  
  <pre><code>DROP TABLESPACE TBS_GABER_DWH INCLUDING CONTENTS AND DATAFILES;
  DROP USER FOOTBALLCLUB CASCADE;
  DROP USER GABER CASCADE;
  
  
  CREATE SMALLFILE TABLESPACE TBS_GABER_DWH DATAFILE
  '/home/oracle/app/oracle/oradata/orcl/gaber_dwh01.dbf' SIZE 200M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED,
  '/home/oracle/app/oracle/oradata/orcl/gaber_dwh02.dbf' SIZE 200M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED
  LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
  
  CREATE USER GABER PROFILE DEFAULT IDENTIFIED BY "oracle"
  DEFAULT TABLESPACE TBS_GABER_DWH
  QUOTA UNLIMITED ON TBS_GABER_DWH
  TEMPORARY TABLESPACE TEMP ACCOUNT UNLOCK;
  GRANT SELECT ANY DICTIONARY TO GABER;
  GRANT UNLIMITED TABLESPACE TO GABER;
  GRANT CONNECT TO GABER;
  GRANT RESOURCE TO GABER;
  GRANT CREATE TABLE TO GABER;
  GRANT CREATE ANY TABLE TO GABER;
  GRANT CREATE PROCEDURE TO GABER;
  GRANT CREATE ANY PROCEDURE TO GABER;
  GRANT EXECUTE ANY PROCEDURE TO GABER;
  GRANT CREATE SESSION TO GABER;
  GRANT SELECT ANY TABLE TO GABER;
  
  
  
  CREATE USER FOOTBALLCLUB PROFILE DEFAULT IDENTIFIED BY "oracle"
  DEFAULT TABLESPACE TBS_GABER_DWH
  TEMPORARY TABLESPACE TEMP
  QUOTA UNLIMITED ON TBS_GABER_DWH
  ACCOUNT LOCK;
  GRANT CREATE TABLE TO FOOTBALLCLUB;
  GRANT UNLIMITED TABLESPACE TO FOOTBALLCLUB;
  
  
  exit;
  </code></pre>
  
  <p>FOOTBALLCLUB_GABER_CREATE_TABLES.sql:</p>
  
  <pre><code>call FOOTBALLCLUB.PROC_GABER_CREATE_TABLES('FOOTBALLCLUB');
  exit;
  </code></pre>
  
  <p>FOOTBALLCLUB_GABER_CREATE_TABLES.plsql:</p>
  
  <pre><code>CREATE OR REPLACE PROCEDURE FOOTBALLCLUB.PROC_GABER_CREATE_TABLES (SCHEMA_NAME IN VARCHAR2) IS
    v_tabcnt number;
  BEGIN
  
  
    SELECT count(*) into v_tabcnt
    FROM   ALL_TABLES
    WHERE  OWNER = SCHEMA_NAME and
           TABLE_NAME = 'DIM_TEAM';
  
    if v_tabcnt = 0 then
        EXECUTE IMMEDIATE
              'CREATE TABLE ' || SCHEMA_NAME || '.DIM_TEAM
              (
                  TEAM_ID  INTEGER      NOT NULL PRIMARY KEY,
                  TEAM_NAME VARCHAR2(30)     NOT NULL
              )';
    end if;
  
  
  
  
    SELECT count(*) into v_tabcnt
    FROM   ALL_TABLES
    WHERE  OWNER = SCHEMA_NAME and
           TABLE_NAME = 'DIM_PLAYER';
  
    if v_tabcnt = 0 then
        EXECUTE IMMEDIATE
              'CREATE TABLE ' || SCHEMA_NAME || '.DIM_PLAYER
              (
                  PLAYER_ID     INTEGER      NOT NULL PRIMARY KEY,
                  PLAYER_NAME   VARCHAR2(30) NOT NULL,
                  PLAYER_NUMBER INTEGER NOT NULL,
                  PLAYER_BIRTHDATE DATE NOT NULL,
                  TEAM_ID INTEGER NOT NULL,
  
                  CONSTRAINT fk_team_id
                     FOREIGN KEY (TEAM_ID)
                     REFERENCES ' || SCHEMA_NAME || '.DIM_TEAM (TEAM_ID)
              )';
    end if;
  
  
    SELECT count(*) into v_tabcnt
    FROM   ALL_TABLES
    WHERE  OWNER = SCHEMA_NAME and
           TABLE_NAME = 'DIM_TRAINER';
  
    if v_tabcnt = 0 then
        EXECUTE IMMEDIATE
              'CREATE TABLE ' || SCHEMA_NAME || '.DIM_TRAINER
              (
                  TRAINER_ID  INTEGER      NOT NULL PRIMARY KEY,
                  TRAINER_NAME VARCHAR2(30) NOT NULL,
                  TRAINER_BIRTHDATE DATE NOT NULL
              )';
    end if;
  
  
    SELECT count(*) into v_tabcnt
    FROM   ALL_TABLES
    WHERE  OWNER = SCHEMA_NAME and
           TABLE_NAME = 'FACT_GABER_FOOTBALLCLUB_STA';
  
    if v_tabcnt = 0 then
        EXECUTE IMMEDIATE
              'CREATE TABLE ' || SCHEMA_NAME || '.FACT_GABER_FOOTBALLCLUB_STA
              (
                  ID     INTEGER    NOT NULL PRIMARY KEY,
                  NUMBER_OF_PLAYERS     INTEGER NOT NULL,
                  GOALS_SHOT  INTEGER NOT NULL,
                  GOALS_GOT     INTEGER NOT NULL,
                  YELLOW_CARDS    INTEGER NOT NULL,
                  RED_CARDS         INTEGER NOT NULL,
                  MINUTES_PLAYED        DECIMAL(12,2) NOT NULL,
                  DATE_PLAYED       DATE NOT NULL,
                  TEAM_ID            INTEGER NOT NULL,
                  TRAINER_ID      INTEGER NOT NULL,
  
                  CONSTRAINT fk_fact_team_id_st
                     FOREIGN KEY (TEAM_ID)
                     REFERENCES ' || SCHEMA_NAME || '.DIM_TEAM (TEAM_ID),
                  CONSTRAINT fk_trainer_id_st
                     FOREIGN KEY (TRAINER_ID)
                     REFERENCES ' || SCHEMA_NAME || '.DIM_TRAINER (TRAINER_ID)
              )';
    end if;
  
      SELECT count(*) into v_tabcnt
    FROM   ALL_TABLES
    WHERE  OWNER = SCHEMA_NAME and
           TABLE_NAME = 'FACT_GABER_FOOTBALLCLUB';
  
    if v_tabcnt = 0 then
        EXECUTE IMMEDIATE
              'CREATE TABLE ' || SCHEMA_NAME || '.FACT_GABER_FOOTBALLCLUB
              (
                  ID     INTEGER    NOT NULL PRIMARY KEY,
                  NUMBER_OF_PLAYERS     INTEGER NOT NULL,
                  GOALS_SHOT  INTEGER NOT NULL,
                  GOALS_GOT     INTEGER NOT NULL,
                  YELLOW_CARDS    INTEGER NOT NULL,
                  RED_CARDS         INTEGER NOT NULL,
                  MINUTES_PLAYED        DECIMAL(12,2) NOT NULL,
                  DATE_PLAYED       DATE NOT NULL,
                  TEAM_ID            INTEGER NOT NULL,
                  TRAINER_ID      INTEGER NOT NULL,
                  CONSTRAINT fk_fact_team_id  
                     FOREIGN KEY (TEAM_ID)
                     REFERENCES ' || SCHEMA_NAME || '.DIM_TEAM (TEAM_ID),
                  CONSTRAINT fk_trainer_id
                     FOREIGN KEY (TRAINER_ID)
                     REFERENCES ' || SCHEMA_NAME || '.DIM_TRAINER (TRAINER_ID)           
              )';
    end if;
  END;
  /
  exit;
  </code></pre>
  
  <p>gaber_footballclub_staging.log:
      SQL*Loader: Release 11.2.0.2.0 - Production on Thu Feb 13 15:05:23 2020</p>
  
  <pre><code>Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
  
  Control File:   gaber_footballclub_staging.ldr
  Data File:      ../data/FACT_FOOTBALLCLUB_GABER.csv
    Bad File:     FACT_FOOTBALLCLUB_GABER.bad
    Discard File:  none specified
  
   (Allow all discards)
  
  Number to load: ALL
  Number to skip: 0
  Errors allowed: 50
  Bind array:     64 rows, maximum of 256000 bytes
  Continuation:    none specified
  Path used:      Conventional
  
  Table FOOTBALLCLUB.FACT_GABER_FOOTBALLCLUB_STA, loaded from every logical record.
  Insert option in effect for this table: REPLACE
  
     Column Name                  Position   Len  Term Encl Datatype
  ------------------------------ ---------- ----- ---- ---- ---------------------
  ID                                  FIRST     *   ;       CHARACTER            
  NUMBER_OF_PLAYERS                    NEXT     *   ;       CHARACTER            
  GOALS_SHOT                           NEXT     *   ;       CHARACTER            
  GOALS_GOT                            NEXT     *   ;       CHARACTER            
  YELLOW_CARDS                         NEXT     *   ;       CHARACTER            
  RED_CARDS                            NEXT     *   ;       CHARACTER            
  MINUTES_PLAYED                       NEXT     *   ;       CHARACTER            
  DATE_PLAYED                          NEXT     *   ;       DATE YYYY-MM-DD      
  TEAM_ID                              NEXT     *   ;       CHARACTER            
  TRAINER_ID                           NEXT     *   ;       CHARACTER            
  
  SQL*Loader-926: OCI error while executing delete/truncate (due to REPLACE/TRUNCATE keyword) for table FOOTBALLCLUB.FACT_GABER_FOOTBALLCLUB_STA
  ORA-01031: insufficient privileges
  </code></pre>