Oracle Test Container抛ORA-01918错误，如何创建指定数据库用户？

问题场景 #

我是Oracle Container新手，Spring Boot应用有两个数据源，在JUnit5集成测试中用Testcontainers创建两个Oracle容器实例。执行建表SQL脚本时抛出SQLSyntaxErrorException，错误信息为ORA-01918: user does not exist。

相关代码 #

容器初始化抽象类

@Slf4j
public class AbstractContanierBaseTest {

    static final OracleContainer ORACLE_OPIOWNER_CONTANINER;
    static final OracleContainer ORACLE_FIRMAOWNER_CONTANINER;

    static {
        ORACLE_OPIOWNER_CONTANINER = new OracleContainer("gvenzl/oracle-xe:21-slim");
        ORACLE_OPIOWNER_CONTANINER
                .withUsername("opiowner")
                .withPassword("password")
                .withDatabaseName("OPIOWNER")
                .withExposedPorts(1521)
                .withLogConsumer(new Slf4jLogConsumer(log))
                .withEnv("ORACLE_DATABASE", "OPIOWNER")
                .withEnv("ORACLE_PASSWORD", "password")
                .withEnv("APP_USER", "opiowner")
                .withEnv("APP_USER_PASSWORD", "password")
                .usingSid();
        ORACLE_OPIOWNER_CONTANINER.start();

        System.setProperty("spring.datasource.driver-class-name", ORACLE_OPIOWNER_CONTANINER.getDriverClassName());
        System.setProperty("spring.datasource.url", ORACLE_OPIOWNER_CONTANINER.getJdbcUrl());
        System.setProperty("spring.datasource.username", ORACLE_OPIOWNER_CONTANINER.getUsername());
        System.setProperty("spring.datasource.password", ORACLE_OPIOWNER_CONTANINER.getPassword());

        ORACLE_FIRMAOWNER_CONTANINER = new OracleContainer("gvenzl/oracle-xe:11-slim");
        ORACLE_FIRMAOWNER_CONTANINER
                .withUsername("firmaowner")
                .withPassword("password")
                .withDatabaseName("FIRMAOWNER")
                .withLogConsumer(new Slf4jLogConsumer(log))
                .withEnv("ORACLE_PASSWORD", "password")
                .withEnv("APP_USER", "firmaowner")
                .withEnv("APP_USER_PASSWORD", "password")
                .usingSid();
        ORACLE_FIRMAOWNER_CONTANINER.start();

        System.setProperty("spring.second-datasource.url", ORACLE_FIRMAOWNER_CONTANINER.getJdbcUrl());
        System.setProperty("spring.second-datasource.username", ORACLE_FIRMAOWNER_CONTANINER.getUsername());
        System.setProperty("spring.second-datasource.password", ORACLE_FIRMAOWNER_CONTANINER.getPassword());

    }
}

JUnit测试的SQL执行注解

@Sql(config = @SqlConfig(transactionManager = "opiownerTransactionManager"), scripts = {"classpath:scripts/ddl/Recall_create_table_opiConfluenzaDati.sql", "classpath:scripts/dml/Recall_opiConfluenzaDati_script.sql"})

建表SQL脚本

CREATE TABLE opiowner.OPI_CONFLUENZA_DATI
(
    "OPI_ANNO_EMISSIONE_TITOLO" NUMBER(4,0),
    "OPI_TIPOLOGIADISPOSIZIONE" VARCHAR2(11),
    "OPI_DATADISPOSIZIONE" DATE,
    "OPI_ID_DISPOSIZIONE" NUMBER(10,0) NOT NULL,
    "OPI_NUM_TITOLO" NUMBER(9,0),
    "OPI_PROG_QUOTA" NUMBER(15,0),
    "OPI_TIPO_COD_BENEF" VARCHAR2(2),
    "OPI_CODICE_BENEF" VARCHAR2(16),
    "OPI_TIPO_RECORD" VARCHAR2(11),
    "OPI_SPECIE_TITOLO" VARCHAR2(2),
    "OPI_TIPO_SPESA" VARCHAR2(5),
    "OPI_COD_ENTE" VARCHAR2(15),
    "OPI_IMPORTO" NUMBER(15,3),
    "OPI_NOME_FILE" VARCHAR2(100),
    "OPI_ORDINANTE" VARCHAR2(100)
);

ALTER TABLE opiowner.OPI_CONFLUENZA_DATI
ADD CONSTRAINT OPI_CONFLUENZA_DATI_PK PRIMARY KEY ("OPI_TIPOLOGIADISPOSIZIONE", "OPI_DATADISPOSIZIONE", "OPI_ID_DISPOSIZIONE");

问题原因 #

你使用的gvenzl/oracle-xe镜像有自己的环境变量初始化逻辑，同时混用了Testcontainers的withUsername/withPassword方法和镜像的APP_USER环境变量，导致用户没有被正确创建；另外，SQL脚本中指定opiowner.前缀，但连接用户可能没有对应权限或用户本身不存在。

解决方案 #

方案1：正确配置镜像环境变量创建用户 #

gvenzl/oracle-xe镜像通过环境变量自动创建用户和PDB，无需混用Testcontainers的用户配置方法，调整容器初始化代码：

ORACLE_OPIOWNER_CONTANINER = new OracleContainer("gvenzl/oracle-xe:21-slim")
        .withLogConsumer(new Slf4jLogConsumer(log))
        // 创建名为OPIOWNER的PDB
        .withEnv("ORACLE_DATABASE", "OPIOWNER")
        // 设置SYS/SYSTEM用户密码
        .withEnv("ORACLE_PASSWORD", "password")
        // 在OPIOWNER PDB中创建opiowner用户
        .withEnv("APP_USER", "opiowner")
        // 设置opiowner用户密码
        .withEnv("APP_USER_PASSWORD", "password")
        // 推荐用Service Name连接PDB，替换usingSid()
        .withServiceName("OPIOWNER");

ORACLE_OPIOWNER_CONTANINER.start();

// 直接使用容器提供的用户信息设置数据源
System.setProperty("spring.datasource.driver-class-name", ORACLE_OPIOWNER_CONTANINER.getDriverClassName());
System.setProperty("spring.datasource.url", ORACLE_OPIOWNER_CONTANINER.getJdbcUrl());
System.setProperty("spring.datasource.username", ORACLE_OPIOWNER_CONTANINER.getUsername());
System.setProperty("spring.datasource.password", ORACLE_OPIOWNER_CONTANINER.getPassword());

该配置会让镜像自动在指定PDB中创建opiowner用户，且Testcontainers会生成正确的JDBC URL指向该PDB。

方案2：手动执行SQL创建用户（兜底方案） #

如果镜像环境变量不生效，可在容器启动后，用SYS用户手动创建并授权opiowner：

ORACLE_OPIOWNER_CONTANINER.start();

// 构建SYS用户的连接URL
String sysJdbcUrl = ORACLE_OPIOWNER_CONTANINER.getJdbcUrl().replace(ORACLE_OPIOWNER_CONTANINER.getUsername(), "SYS as SYSDBA");
try (Connection conn = DriverManager.getConnection(sysJdbcUrl, "SYS", "password")) {
    try (Statement stmt = conn.createStatement()) {
        // 创建用户
        stmt.execute("CREATE USER opiowner IDENTIFIED BY password");
        // 授予必要权限
        stmt.execute("GRANT CONNECT, RESOURCE, DBA TO opiowner");
        // 授予表空间无限配额
        stmt.execute("ALTER USER opiowner QUOTA UNLIMITED ON USERS");
    }
} catch (SQLException e) {
    log.error("创建opiowner用户失败", e);
    throw new RuntimeException(e);
}

// 后续设置数据源属性...

方案3：简化SQL脚本（避免指定schema前缀） #

如果你的数据源连接用户就是opiowner，SQL脚本无需指定opiowner.前缀，默认会在当前用户的schema下创建表：

CREATE TABLE OPI_CONFLUENZA_DATI
(
    -- 字段定义不变...
);

ALTER TABLE OPI_CONFLUENZA_DATI
ADD CONSTRAINT OPI_CONFLUENZA_DATI_PK PRIMARY KEY ("OPI_TIPOLOGIADISPOSIZIONE", "OPI_DATADISPOSIZIONE", "OPI_ID_DISPOSIZIONE");

内容的提问来源于stack exchange，提问作者Saverio Mirko Viola