Oracle DB Create Schema: How to Create Schema in Oracle Database

In Oracle Database, a schema is a collection of objects owned by one user. It can contain tables, views, indexes, sequences, and stored code. Oracle gives the schema the same name as its owner, so setting up an application's schema begins with a user account.

To create a schema in Oracle, first create the user who will own it. This answers the common question “how to create database schema in Oracle”: choose tablespaces and a quota, grant the account the privileges it needs, then add objects under its name. The same sequence applies to a test instance and a production database. Each choice affects where objects are stored and what the account can do.

What Is an Oracle Database Schema

An Oracle schema is the namespace for objects owned by a particular database user. When that user creates tables, views, or procedures, those objects belong to the user's schema. The account and schema share a name, although the account can exist before any objects are added.

Oracle Schema vs Oracle User: The Core Concept

Oracle User: an account created with CREATE USER. It has authentication settings and can receive privileges.
Oracle Schema: the collection of objects owned by that user. Oracle establishes it when the account is created and gives it the account's name.
Key Point: every user has a schema, even if the user does not yet own any objects.

What Objects Belong to an Oracle Schema

Tables: store structured data in rows and columns.
Views: present the results of a saved query against one or more tables.
Indexes: provide access paths that can speed up table queries.
Sequences: generate numeric values, often for primary keys.
Stored Procedures and Functions: contain code that runs in the database.
Triggers: run in response to data changes or specified database events.
Synonyms: provide alternate names for objects.

Oracle Database Schema Example

Suppose an application needs an orders table. First, create a user called sales. Oracle gives sales an empty schema with the same name. Tables created by the user under that name belong to its schema.

Logical Schema Structure in Oracle

A schema is a namespace for one user's objects. Two users can each own an orders table because their schema names distinguish the tables.

For example, create the account and grant it roles:

CREATE USER sales IDENTIFIED BY strongpassword; GRANT CONNECT, RESOURCE TO sales;

The first statement creates the sales account and its schema. The second statement grants roles to the same account. At this point, the sales schema exists, though it is still empty.

After the account has the required privileges, it can create objects in its schema. Here is an orders table:

CREATE TABLE sales.orders ( order_id NUMBER PRIMARY KEY, order_date DATE, amount NUMBER );

The table belongs to sales, so its qualified name is sales.orders.

How Oracle Resolves Schema Ownership

Oracle associates schema ownership with the user that owns an object. In this example, the table's owner also gives the schema its name.
A reference to sales.orders identifies the schema and table, even if another user owns a table with the same name.

Knowing the table's name does not give another account permission to read it. If hr needs access, grant it read permission on the table:

GRANT SELECT ON sales.orders TO hr;

The grant identifies the object and the account allowed to read it. This makes access between schemas explicit.

How to Create a Database Schema in Oracle

To create new schema in Oracle, create the user that will own it. Oracle establishes the empty schema at the same time. The account then needs the appropriate privileges and storage settings before it can connect and create objects.

Create Schema in Oracle by Creating a User

Required System Privileges

Setting up a new user involves privileges for two accounts: the administrator who creates it and the new owner who will use it. To create schema in Oracle and work in it, check these privileges:

CREATE USER: allows the administrator to create the account and its schema.
CREATE SESSION: allows the new user to connect to the database. Without it, the account and schema still exist.
CREATE TABLE, VIEW, PROCEDURE: allow the owner to create the corresponding types of schema objects.
RESOURCE or DBA roles: grant sets of privileges rather than one right at a time. Review what each role grants before using it; DBA is for administrative accounts.

The user can exist without login or object-creation privileges. Grant the login privilege to allow a connection, then add only the object-creation rights the account needs.

Default and Temporary Tablespaces

When creating a schema in Oracle, specify where permanent objects belong and where temporary work occurs. A quota can limit how much space the user consumes in a tablespace:

Default tablespace: the default location for permanent objects, including tables and indexes.
Temporary tablespace: space Oracle uses for temporary operations such as sorts and joins.
Quota: the amount of space the user may use in a specified tablespace.

Set these values to match the application's storage needs. A finite quota limits the user's growth in that tablespace; the account can then create objects under its own schema name.

Oracle DB Create Schema: SQL Commands Explained

For an Oracle DB create schema task, start with CREATE USER and then grant the access that account requires. The commands below show how the account, storage settings, and privileges fit together.

CREATE USER Statement Breakdown

CREATE USER defines the account and establishes a schema with the same name. This example also specifies four settings:

Username and password: identify and authenticate the owner. The schema takes the username.
Default tablespace: selects the default location for permanent objects.
Temporary tablespace: provides space for operations such as sorts and hash joins.
Quota: states how much space the user may use in the named tablespace. The example below leaves that amount unlimited.

For example:

CREATE USER sales IDENTIFIED BY strongpassword
        DEFAULT TABLESPACE users
        TEMPORARY TABLESPACE temp
        QUOTA UNLIMITED ON users;

The sales account and the sales schema now exist. This example imposes no quota on the user's tablespace. The account still needs privileges to connect and create objects.

Granting Roles and Object Privileges

The next part of the Oracle database create schema process is deciding what the user may do. A role groups privileges; an object privilege gives access to a specific table or other object:

Roles: named groups of privileges. CONNECT, RESOURCE, and DBA are examples with different scopes.
Object privileges: rights such as SELECT, INSERT, or UPDATE on a particular object.

These grants illustrate both approaches:

GRANT CONNECT, RESOURCE TO sales;
        GRANT CREATE TABLE, CREATE VIEW, CREATE PROCEDURE TO sales;
        GRANT SELECT ON hr.employees TO sales;

The first line grants two broad roles. Review their included privileges before using them for an application account.
The second line names the system privileges needed to create tables, views, and procedures.
The third line lets sales read a table owned by the hr schema.

Creating a Schema in Oracle: Common Scenarios

The same user and privilege steps apply whether a new application needs its own objects or a developer needs a DBA to provision the account. The difference is who has permission to create a user.

Create a New Schema in Oracle for an Application

Scenario: an HR, Sales, or Finance application needs a dedicated owner for its tables, code, and data.
Approach:

1. Create the user with a strong password and store that password securely.
2. Choose its default and temporary tablespaces, then set a quota that fits the expected data.
3. Grant the privileges the application needs. If you use roles such as CONNECT or RESOURCE, review their scope first.
4. Create the application's tables, views, and procedures under that schema.

Benefit: the application has a named owner for its objects, and access from other schemas can be granted deliberately.

Creating Schema in Oracle Without DBA Role

Scenario: a developer needs a new schema but does not have the DBA role.
Clarification:

An account needs the CREATE USER privilege to create another user and its schema. A DBA team often holds this privilege.
Without that privilege, a developer cannot provision the new account. Once it exists, the developer can create objects there if the account has the required privileges.

Workaround: ask a DBA to create the user and schema, then use granted privileges such as CREATE TABLE or CREATE VIEW to add objects.
Best Practice: give account-creation rights to administrators and grant developers the object-creation rights their work requires.

Schema Creation in Oracle: Best Practices

Creating schema in Oracle starts with CREATE USER, but the account also needs a suitable name, privileges, and storage settings. Review these choices when creating the account so its access and space use match the application.

Schema Naming and Security Boundaries

Use clear, descriptive names: choose a name tied to the application or department, such as HR, SALES, or FINANCE. Names like TEST and USER1 provide little context.
Enforce security boundaries: keep each application's objects under its designated owner and grant access to other schemas only where needed.
Follow least privilege principle: grant the workload the rights it uses, such as CREATE TABLE or SELECT, rather than assigning DBA by default.
Audit regularly: review grants at planned intervals and remove access the account no longer needs.

Tablespace Planning for Performance and Recovery

Separate tablespaces by workload: use a default tablespace for permanent objects and a temporary tablespace for operations that need temporary space.
Quota management: set a finite quota when the user's space consumption needs a limit within a shared tablespace.
Performance tuning: if an account's I/O load warrants separate storage, place its objects in an appropriate tablespace and assess the effect on other workloads.
Recovery readiness: include the relevant datafiles in RMAN backups and test that they can be restored.
Growth planning: track tablespace use and adjust capacity or quotas before the application runs out of space.

Common Mistakes When Creating Oracle Schemas

Creating Oracle schema structures is straightforward, but two mistakes cause avoidable problems: expecting a statement for an empty namespace and granting an owner more access than it needs.

Misunderstanding CREATE SCHEMA Syntax

Confusion point: CREATE SCHEMA sounds like a command for creating an empty schema.
Reality: Oracle establishes a schema when it creates a user. Its CREATE SCHEMA statement groups CREATE TABLE, CREATE VIEW, and GRANT statements for an existing user into one transaction. It does not create the user or schema.
Mistake: expecting CREATE SCHEMA my_schema; to create a new, empty schema.
Best practice: create the owner with CREATE USER, grant the required privileges, and then add objects.

Over‑Privileged Schema Owners

Confusion point: assigning DBA or extra privileges to an application owner in case it needs them later.
Risk: broad privileges can let the account change or remove objects outside its intended scope. They also make access harder to review.
Mistake: granting every developer RESOURCE or DBA without checking the work each account must do.
Best practice:

Grant the required privileges, such as CREATE TABLE and CREATE VIEW, after checking the workload.
Group related privileges into roles when that makes grants easier to manage and revoke.
Review the account's privileges regularly and remove those it no longer uses.

Oracle Schema vs Database vs Instance

Concept	Scope	Purpose
Schema	Logical	Object ownership
Database	Physical	Data storage
Instance	Memory + Processes	Access control

Oracle Schema Data Risks in Virtualized Environments

Running Oracle databases inside virtual machines introduces new layers of complexity. While schemas themselves are logical constructs, the underlying storage and virtualization platform determine how resilient they are to crashes, restarts, and corruption.

Oracle Schemas on VMware: Where Data Lives

Schemas are logical: They exist inside the Oracle database instance, but their objects (tables, indexes, procedures) ultimately reside in physical or virtual storage.
VMware environments: When Oracle runs on VMware, schema data is stored in VMDK files on VMFS datastores.
Risk factor: A VM crash or improper restart can affect the VMDK file, which in turn impacts the Oracle schema objects stored within.
Operational takeaway: Protecting schemas requires not only Oracle‑level backups but also VM‑level storage resilience.

VMFS, VMDK, and Tablespace Dependencies

Tablespaces map to storage: Oracle tablespaces are backed by datafiles, which in a VMware setup are stored inside VMDKs.
VMFS metadata: If VMFS corruption occurs, Oracle datafiles may become inaccessible, putting entire schemas at risk.
Snapshot dependencies: VMware snapshots can complicate Oracle recovery if not managed carefully, especially when active transactions are involved.
Recovery linkage: Schema recovery often depends on restoring VMDKs and VMFS integrity before Oracle can rebuild tablespaces and objects.

Storage Failure, RAID Issues, and Oracle Schema Loss

Even though Oracle schemas are logical constructs, they depend entirely on the integrity of underlying storage. When RAID arrays or virtual disks fail, schema objects can become inaccessible, leading to downtime and potential data loss.

How RAID Failure Affects Oracle Datafiles

Datafiles dependency: Oracle tablespaces are backed by physical datafiles. In virtualized environments, these datafiles reside inside VMDKs on RAID‑protected storage.
RAID failure impact:

RAID controller corruption or disk loss can make entire datafiles unreadable.
Incomplete writes during rebuilds may leave Oracle redo logs or control files inconsistent.

Schema consequence: If datafiles are damaged, the schema objects (tables, indexes, procedures) they contain become inaccessible, even though the schema definition still exists logically.

When Schemas Become Inaccessible After VM or Disk Failure

VM crash scenario: A sudden VM shutdown can corrupt VMDKs, breaking Oracle’s access to datafiles.
Disk failure scenario: Physical disk loss in RAID arrays may orphan VMFS metadata, making Oracle unable to locate schema data.
Result: The schema itself is intact in Oracle’s dictionary, but its objects cannot be read or recovered without restoring the underlying storage.
Recovery path:

1. First, restore VMFS/VMDK integrity using specialized recovery tools.
2. Then validate Oracle datafiles and tablespaces with RMAN or DBVERIFY.
3. Finally, re‑register or rebuild schema objects if corruption persists.

Recovering Oracle Data After RAID or VMFS Damage

When Oracle schemas become inaccessible due to storage failures, recovery depends on restoring the underlying RAID arrays or VMFS datastores. Specialized tools provide a reliable path to rebuild storage and extract Oracle datafiles.

RAID Recovery Scenarios in Oracle Environments

Scenario: A degraded RAID array or failed disk causes Oracle datafiles to become unreadable.
Impact: Tablespaces and schema objects tied to those datafiles are lost until the RAID is rebuilt.
Recovery:

1. Identify the RAID level (RAID 0, 5, 6, 10).
2. Rebuild the array using specialized recovery software.
3. Restore Oracle datafiles to regain access to schema objects.

Example: DiskInternals RAID Recovery software can reconstruct degraded RAID arrays, repair metadata, and restore Oracle datafiles, ensuring schemas are accessible again.

VMFS Recovery™ for Oracle Databases on VMware

Scenario: VMFS datastore corruption or unexpected VM shutdown damages VMDK files containing Oracle datafiles.
Impact: Oracle schemas stored inside those VMDKs become inaccessible, even though the database dictionary still recognizes them.
Recovery:

1. Scan the VMFS datastore for lost or corrupted VMDKs.
2. Extract Oracle datafiles from recovered VMDKs.
3. Re‑import datafiles into Oracle to restore tablespaces and schema objects.

Example: DiskInternals VMFS Recovery™ is designed to recover VMDK data from damaged VMFS volumes, making it possible to extract Oracle schemas after datastore corruption or VM crashes.

Ready to get RAID data back?

To start recovering your data, documents, databases, images, videos, and other files from your RAID 0, RAID 1, 0+1, 1+0, 1E, RAID 4, RAID 5, 50, 5EE, 5R, RAID 6, RAID 60, RAIDZ, RAIDZ2, and JBOD, press the FREE DOWNLOAD button to get the latest version of DiskInternals RAID Recovery® and begin the step-by-step recovery process. You can preview all recovered files absolutely for free. To check the current prices, please press the Get Prices button. If you need any assistance, please feel free to contact Technical Support. The team is here to help you recover Oracle virtual machine!

FREE DOWNLOADVer 4.28, Win BUY NOWFrom $699

Ready to get VMFS data back?

To start recovering your data, documents, databases, images, videos, and other files, press the FREE DOWNLOAD button below to get the latest version of DiskInternals VMFS Recovery® and begin the step-by-step recovery process. You can preview all recovered files absolutely for FREE. To check the current prices, please press the Get Prices button. If you need any assistance, please feel free to contact Technical Support. The team is here to help you get your data back!