How to setup Oracle free db dockercontainer with a schema?

Question

My goal is to setup an Oracle test database, containing a schema with two tables. Accessible via DataGrip (JDBC) connection.

Starting from the Oracle container registry: Oracle Container Free Image

I can start an image with the following command:

docker run -d -p 1521:1521 -e ORACLE_PWD=mypw --name my-test-db container-registry.oracle.com/database/free:latest

This one is accessible with two configuration from DataGrip: jdbc:oracle:thin:@localhost:1521:FREE with username: system and pw: mypw The db1 is accessible with an other configuration: jdbc:oracle:thin:@localhost:1521/FREEPDB1 with username: pdbadmin and pw: mypw notice the / instead of the :. Datagrip autogenerated : instead of /

Now from here on I want to create two tables using an init.sql script which are available within their own schema.

The init.sql script can be mounted by exiting the docker command as follows:

docker run -d -p 1521:1521 -e ORACLE_PWD=pw -v C:\Users\MyUser\Documents\Code\test-db-startup:/opt/oracle/scripts/startup --name my-test-db container-registry.oracle.com/database/free:latest

The init.sql is located at C:\Users\MyUser\Documents\Code\test-db-startup and contains two simple tables:

-- Creating the Invoice Table
CREATE TABLE INVOICE (
    EXTERNAL_ID NUMBER(10) NOT NULL,
    DUEDATE DATE,
    PRIMARY KEY (EXTERNAL_ID)
);

-- Creating the Line Table
CREATE TABLE LINE (
    EXTERNAL_ID NUMBER(10) NOT NULL,
    LINENO NUMBER(10) NOT NULL,
    PRICE NUMBER(18, 6),
    PRIMARY KEY (EXTERNAL_ID, LINENO)
);

According to the logs the tables are created, where do I find them in which schema?

How can I adjust the init script so it organizes the tables within a new schema for example "TESTDATA" which is accessible from Datagrip and listed as a schema?

The https://docs.oracle.com/javadb/10.8.3.0/ref/rrefsqlj31580.html CREATE SCHEMA command looked promising, but somehow I cannot deal with the authorization part, it always claims it's missing.

In PostgreSQL, I would have created a new database, what is the best practice of doing something like this in Oracle?

Answer 1

So your init.sql script (init.sql is located at C:\Users\MyUser\Documents\Code\test-db-startup) is missing a few things. According to the docs here, the Oracle DB will run your setup scripts using sys as sysdba and most likely in the CDB instead of PDB. Therefore, in your init.sql script, you want to do the following:

1. Set the session to your PDB

   alter session set container=FREEPDB1;
   

2. Create your schema by creating a user, this lets you control where your tables would go instead of some other schema

   create user myuser no authentication;
   grant connect, resource to myuser;
   grant unlimited tablespace to myuser; -- fine-tune this later
   

3. Create your tables with the schema name specified, for e.g.

   -- Creating the Invoice Table in myuser schema
   CREATE TABLE myuser.INVOICE (
      EXTERNAL_ID NUMBER(10) NOT NULL,
      DUEDATE DATE,
      PRIMARY KEY (EXTERNAL_ID)
   );
   

---

As for your question where your tables were created, it would be in the CDB and SYS schema. You can check this by:

-- using sqlcl
connect sys@localhost:1521 as sysdba;

select table_name, owner from dba_tables where upper(TABLE_NAME) in ('INVOICE', 'LINE');

-- result
SQL> select table_name, owner from dba_tables where upper(TABLE_NAME) in ('INVOICE', 'LINE');

TABLE_NAME    OWNER     
_____________ _________ 
INVOICE       SYS    
LINE          SYS  

Therefore, you want to alter your session, create a schema and create your tables inside the schema. If successful,

-- using sqlcl
connect system@localhost:1521/FREEPDB1;

-- result
SQL> select table_name, owner from dba_tables where upper(TABLE_NAME) in ('INVOICE', 'LINE');

TABLE_NAME    OWNER     
_____________ _________ 
INVOICE       MYUSER    
LINE          MYUSER  

You can then change the myuser password or grant access to another user;