How To Create Schema In Sql Developer

Dear readers of our blog, we'd like to recommend you to visit the main page of our website, where you can learn about our product SQLS*Plus and its advantages.

SQLS*Plus - best SQL Server command line reporting and automation tool! SQLS*Plus is several orders of magnitude better than SQL Server sqlcmd and osql command line tools.

Enteros UpBeat offers a patented database performance management SaaS platform. It proactively identifies root causes of complex revenue-impacting database performance issues across a growing number of RDBMS, NoSQL, and deep/machine learning database platforms. We support Oracle, SQL Server, IBM DB2, MongoDB, Casandra, MySQL, Amazon Aurora, and other database systems.

20 August 2020

Oracle CREATE SCHEMA

The CREATE SCHEMA operator does not actually create the circuit in Oracle. (Learn how to create a schema in Oracle.)

The CREATE SCHEMA statement is only used to create objects (i.e. tables, views) in your schema in one SQL statement instead of running the individual CREATE TABLE and CREATE VIEW statements.

If an error occurs when creating any of the objects in the CREATE SCHEMA statement, the Oracle database rolls back all creation operations (i.e. tables and views) in the CREATE SCHEMA statement.

Syntax of the CREATE SCHEMA operator

CREATE SCHEMA AUTHORIZATION schema_name
[create_table_statement]
[create_view_statement]
[grant_statement];

Parameters and arguments of the operator

schema_name – the name of the schema (which is the same as the Oracle user you logged on with).
create_table_statement – optional. This is a valid CREATE TABLE operator.
create_view_statement is optional. This is a valid CREATE VIEW operator.
grant_statement is optional. This is a valid GRANT operator.

Below is the CREATE SCHEMA operator (creating one table in the schematic):

In this example, a scheme called samvel is created. In this new scheme, one table is created, called products.

You can also create multiple tables using the CREATE SCHEMA operator as follows:

CREATE SCHEMA AUTHORIZATION samvel
CREATE TABLE products
( product_id number(10) not null,
product_name varchar2(50) not null,
category varchar2(50),
CONSTRAINT products_pk PRIMARY KEY (product_id)
)
CREATE TABLE suppliers
( supplier_id number(10) not null,
supplier_name varchar2(50) not null,
city varchar2(25),
CONSTRAINT suppliers_pk PRIMARY KEY (supplier_id)
);

This CREATE SCHEMA operator will create two tables, the products and the suppliers. If an error occurs when creating any of these tables, no table will be created.

Alternatively, you could create these 2 tables using two separate CREATE TABLE operators as follows (as long as you are a samvel user):

CREATE TABLE products
( product_id number(10) not null,
product_name varchar2(50) not null,
category varchar2(50),
CONSTRAINT products_pk PRIMARY KEY (product_id)
);

CREATE TABLE suppliers
( supplier_id number(10) not null,
supplier_name varchar2(50) not null,
city varchar2(25),
CONSTRAINT suppliers_pk PRIMARY KEY (supplier_id)
);