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?