How to Create a Schema in Oracle: Step-by-Step for Beginners

Creating a schema in Oracle is a fundamental task for DBAs and developers. However, beginners often run into errors, especially when using Oracle 12c or newer multitenant architecture (PDBs). In this blog, I’ll walk you through the correct steps to create a schema and explain how to fix common errors like ORA-65096 and ORA-01017.

What is a Schema in Oracle?

In Oracle, a schema is automatically created when a user is created. The schema name is the same as the username and contains all of that user's database objects (tables, views, procedures, etc.).

How to Create a Schema (User)?

Step 1. Log in to Oracle as SYSDBA.

Use SQL*Plus, SQL Developer, or any tool to log in as SYSDBA.

sqlplus sys as sysdba

Or through SQL Developer.

Username: SYS
Role: SYSDBA

Test

Step 2. Check Available Pluggable Databases (PDBs).

Run
SHOW PDBS;

PDBS

Example Output

CON_ID   CON_NAME   OPEN MODE     RESTRICTED

-------  ---------  -----------   ----------

2        PDB$SEED   READ ONLY     YES

3        XEPDB1     READ WRITE    NO

Step 3. Switch to a Pluggable Database (PDB)

If you're in the root container (CDB$ROOT), trying to create a user will throw.

ORA-65096: invalid common user or role name.

Fix it by setting the container.

ALTER SESSION SET CONTAINER = XEPDB1;

XEPDB

Step 4. Create a User (Schema).

Now that you're inside the PDB (XEPDB1), create your schema.

CREATE USER schema_name IDENTIFIED BY "Password";

Make sure your password meets complexity rules (use uppercase, lowercase, special characters, numbers).

Schema

Step 5. Grant Required Privileges.

GRANT CONNECT, RESOURCE TO sales_schema;

Grant Required Privileges

Common Errors and Solutions

ORA-65096: Invalid common user or role name
Cause: You are trying to create a local user in the CDB root.

Solution

ALTER SESSION SET CONTAINER = XEPDB1;

Solution

ORA-01017: invalid username/password; logon denied
Cause: Wrong username or password, or trying to connect to the wrong container.

Solution

Ensure the username is correct (no typos).
Verify the password (quotes are sensitive: "Sales@123").
In SQL Developer
- Username: student_schema
- Password: student@123
- Hostname: localhost
- Port: 1521
- SID or Service: use XEPDB1 if PDB is used

Use Service Name = XEPDB1 instead of SID if the schema is created in a pluggable database.

Use Service Name

Student schema

How to List All Schemas?

SELECT username FROM dba_users ORDER BY username;

The error

ORA-01950: no privileges on tablespace 'USERS'

means that your Oracle user doesn’t have permission to allocate space in the USERS tablespace, which is where Oracle is trying to create or insert data into your table (tblfmv_tax).

How to Fix It?

You (or your DBA) need to grant a quota on the tablespace the user. Here’s how to fix it:

1. Log in as a user with DBA privileges (e.g., SYS or SYSTEM)

Then run

ALTER USER your_username QUOTA UNLIMITED ON USERS;

Replace your_username with the actual Oracle username you're using (e.g., SCOTT, HR, MYUSER, etc.).

Recap

Step	Command
Check PDBs	SHOW PDBS;
Switch to PDB	ALTER SESSION SET CONTAINER = XEPDB1;
Create schema	CREATE USER sales_schema IDENTIFIED BY "Sales@123";
Grant access	GRANT CONNECT, RESOURCE TO sales_schema;

Final Thoughts

Creating a schema in Oracle is simple once you understand the container architecture. If you're using Oracle 12c or newer, always ensure you're working in the correct PDB (Pluggable Database) to avoid common errors.

If you found this guide helpful, feel free to share it with your team or comment below if you run into any issues!