How To Set Current Schema In Oracle

---

Oracle / PLSQL: Create a Schema

This Oracle tutorial explains how to create a schema in Oracle with syntax and examples.

Description

Creating a schema in Oracle, can at first, announced to be a little confusing. You might think that the CREATE SCHEMA statement would create your schema, but that is not the case. The CREATE SCHEMA argument is used only to create objects (ie: tables, views, etc) in your schema in a single SQL statement, but does not actually create the schema itself.

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

Step 1 - Create a new user in Oracle

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

Nosotros can create a new user with the CREATE USER statement 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 statement would create a new user called smithj in the Oracle database whose countersign is pwd4smithj, the default tablespace would exist tbs_perm_01 with a quota of 20MB, and the temporary tablespace would be tbs_temp_01.

If you don't accept tablespaces yet, learn how to create default and temporary tablespaces.

Step 2 - Assign System privileges to new user in Oracle

The adjacent step in setting up your schema is to assign "system privileges" to the new user smithj.

These "system privileges" will allow our new user to create a session in Oracle also as create tables, views, triggers, procedures, sequences, and synonyms in the new schema. Hither is an example of how nosotros might grant those organisation 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 process TO smithj; GRANT create sequence TO smithj; GRANT create synonym TO smithj;

These new privileges are at present granted to the user called smithj.

Step 3 - Create objects in the schema

Now that the schema (chosen smithj) has been created with the necessary privileges, you can create objects in the schema. This tin exist done one of 2 ways:

1. Executing individual SQL statements to create each object. This would exist washed through CREATE TABLE statements and CREATE VIEW statements.
2. Executing a CREATE SCHEMA statement to create multiple objects in a unmarried SQL statement.

Footstep 4 - Grant Object Privileges

After you have created your objects in the schema, you volition demand to grant privileges and so that other schemas/users can access your database objects (ie: tables).

Pace v - Create Synonyms for Objects

Every bit a last step, you may want to create synonyms so that other schemas can access the new database objects (ie: tables) without having to prefix the object names with the schema name.

For example, if you lot were another user named smithj and wanted to select from the suppliers table in new_schema, you would have to run the post-obit SELECT statement (before whatsoever synonyms are created):

SELECT *  FROM new_schema.suppliers;

If you then created a synonym for the suppliers tabular array every bit 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 table name with the schema proper name.

Source: https://www.techonthenet.com/oracle/schemas/create_schema.php

Share this post