Using Create a Schema in Oracle

This article is written about how to create a schema in Oracle with syntax and examples.

Description

Creating a schema in Oracle, can at first, show up to be a little confusing. You might assume that the CREATE SCHEMA assertion would create your schema, however that is now not the case. The CREATE SCHEMA declaration is used only to create objects (ie: tables, views, etc) in your schema in a single SQL statement, however does no longer honestly create the schema itself.

To create a schema in Oracle, you want to do the following steps:

Step 1 – Create a new user in Oracle

In essence, a schema is created in Oracle when a consumer is created. (Learn the syntax for the CREATE USER statement).

We can create a new consumer with the CREATE USER announcement as follows:

CREATE USER smithj
  IDENTIFIED BY pwd4smithj
  DEFAULT TABLESPACE tbs_perm_01
  TEMPORARY TABLESPACE tbs_temp_01
  QUOTA 20M on tbs_perm_01;

This CREATE USER assertion would create a new consumer known as smithj in the Oracle database whose password is pwd4smithj, the default tablespace would be tbs_perm_01 with a quota of 20MB, and the brief tablespace would be tbs_temp_01.

If you don’t have tablespaces yet, study how to create default and temporary tablespaces.

Step two – Assign SYSTEM privileges to new consumer in Oracle

The subsequent step in putting up your schema is to assign “system privileges” to the new person smithj.

These “system privileges” will permit our new person to create a session in Oracle as nicely as create tables, views, triggers, procedures, sequences, and synonyms in the new schema. Here is an example of how we would possibly grant these machine privileges:

GRANT create session TO smithj;
GRANT create table TO smithj;
GRANT create view TO smithj;
GRANT create any trigger TO smithj;
GRANT create any procedure TO smithj;
GRANT create sequence TO smithj;
GRANT create synonym TO smithj;

These new privileges are now granted to the consumer known as smithj.

Step 3 – Create objects in the schema

Now that the schema (called smithj) has been created with the vital privileges, you can create objects in the schema. This can be accomplished one of 2 ways:

Executing man or woman SQL statements to create each object. This would be achieved thru CREATE TABLE statements and CREATE VIEW statements. Executing a CREATE SCHEMA assertion to create a couple of objects in a single SQL statement.

Step 4 – Grant Object Privileges

After you have created your objects in the schema, you will want to supply privileges so that different schemas/users can access your database objects (ie: tables).

Step 5 – Create Synonyms for Objects

As a last step, you may additionally favor to create synonyms so that other schemas can get admission to the new database objects (ie: tables) except having to prefix the object names with the schema name.

For example, if you were some other person named smithj and desired to pick from the suppliers desk in new_schema, you would have to run the following SELECT declaration (before any synonyms are created):

SELECT * 
FROM new_schema.suppliers;

If you then created a synonym for the suppliers table as follows:

CREATE PUBLIC SYNONYM suppliers
FOR new_schema.suppliers;

You could run the SELECT statement as follows:

SELECT * 
FROM suppliers;

No longer needing to prefix the desk title with the schema name.