sql - SQL * Loader-926 : OCI error while executing delete/truncate ORA-01031: insufficient privileges

我有12个.csv files，应将其加载到临时表中，然后将数据加载到临时表中，然后应将按参数分组的数据加载到另一个表中。通过创建表会引发错误。它缺少特权。

GABER_RIGHTS.SQL:

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;

gaber_footballclub.sh:

#!/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 >../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 <<!
      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

gaber_footballclub_staging.ldr:

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
)

FOOTBALLCLUB_GABER_CREATE_USER.sql:

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;

FOOTBALLCLUB_GABER_CREATE_TABLES.sql:

call FOOTBALLCLUB.PROC_GABER_CREATE_TABLES('FOOTBALLCLUB');
exit;

FOOTBALLCLUB_GABER_CREATE_TABLES.plsql:

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;

gaber_footballclub_staging.log:
SQL * Loader:版本11.2.0.2.0-2020年2月13日星期四15:05:23生产

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

最佳答案

我已经通过删除 plsql脚本中的constraints和primary keys解决了我的问题。此外，我在一个 csv表中使用了一个 0 而不是 O 。

唯一更改的文档是
FOOTBALLCLUB_GABER_CREATE_TABLES.plsql:

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,
                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,
                PLAYER_NAME   VARCHAR2(30) NOT NULL,
                PLAYER_NUMBER INTEGER NOT NULL,
                PLAYER_BIRTHDATE DATE NOT NULL,
                TEAM_ID INTEGER NOT NULL
            )';
  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,
                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,
                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
            )';
  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,
                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
            )';
  end if;
END;
/
exit;