How do I create a new Oracle schema?

Question

I’m pretty familiar with Relational DBMS in general, but not with Oracle. I have Oracle 18c XE running inside Docker (on MacOS). I connect to it using a third party client (SQLPro Studio on MacOS). Now I want to get started.

If this were MySQL I could issue CREATE DATABASE whatever; USE whatever; and proceed with the tables. The same with Microsoft SQL Server, and something similar with PostgreSQL. I could also create an additional schema to keep better organised.

I gather that Oracle XE only gives you a single database, and I would want to create a suitable schema. At this point I’m at sea. I gather that I would need to create a new user associated with the schema.

I also know that different RDBMS have different ideas about what a database or a schema is, so I’m not sure whether this is the correct definition or solution.

I’m having no success creating either a new user or a new schema in my client.

I have tried:

CREATE USER fred IDENTIFIED BY 'h3art0fg0ld`;

but I get the message:

invalid common user or role name

I also read that I should use something like c##fred, which did work, but (a) I don’t really know what that means and (b) the statement GRANT connect, resource, create sessions TO c##printsdb; gives me the message “missing or invalid privilege”, so I’m no further ahead.

Being in a Docker container, I don’t know whether I have access to SQL*Plus, which some online tutorials presume.

How do I create and use a schema, or whatever is required to contain a collection of tables?

Answer 1

I gather that Oracle XE only gives you a single database

Correct - as long as you "speak" Oracle-ese.

The Container Database is the only "real" Oracle Database instance that you have.

The "Pluggable Databases" within it are essentially "Tablespaces on Steroids", but with much better security that now guarantees the isolation of each one from every other.

For most normal operations, you should ignore the Container Database completely.
Just use the/a Pluggable Database inside it.

How do I create and use a schema, or whatever is required to contain a collection of tables?

CREATE USER fred IDENTIFIED BY '********';

Connect to the Pluggable Database and your statement will work exactly as you expect it to.