Oracle create schema: A Step-by-Step Guide

Introduction

A schema is defined as a user that owns data such as tables, indexes, and so forth. In this tutorial, How to use Oracle create schema.

Dive into the world of Oracle with this practical guide on creating schemas. This tutorial walks you through the essential steps to establish a schema in Oracle Database, starting with creating a tablespace, defining a new user, and culminating in setting up a test table. Ideal for database administrators and developers who want to streamline their database structure and enhance data management.

Oracle create schema

Access the Oracle database with permission privileges for creating a tablespace, creating a user, and creating a table on the Oracle Database.

[oracle@DBOracle ~]$ sqlplus / as sysdba

Step 1: Create tablespace on Oracle Database

Before creating a schema you need to create a file ( one or more files) for the schema to place its data into. This file schema writes data is called a tablespace. One tablespace has one or more datafile.

SQL> create tablespace datafile_test datafile '/U02/datafile_test_01.dbf' size 1000M extent management local autoallocate segment space management auto;

Step 2: Oracle Create a new user

The explain use command creates a user in Oracle

User: HuuPV
Password: pwd4HuuPV
Default Tablespace: datafile_test
Temporary Tablespace: TEMP

SQL> CREATE USER HuuPV IDENTIFIED BY pwd4HuuPV DEFAULT TABLESPACE datafile_test TEMPORARY TABLESPACE TEMP;

Oracle Privileges Granted to the APPDEV Role

SQL> GRANT CONNECT TO HuuPV;
SQL> GRANT UNLIMITED TABLESPACE TO HuuPV;
SQL> GRANT CREATE TABLE TO HuuPV;
SQL> GRANT CREATE PROCEDURE TO HuuPV;
SQL> GRANT CREATE VIEW TO HuuPV;
SQL> GRANT CREATE TRIGGER TO HuuPV;
SQL> GRANT CREATE SEQUENCE TO HuuPV;
SQL> GRANT CREATE SYNONYM TO HuuPV;

Step 3: Oracle creates a table for the test

SQL> create table test (NAME varchar2(32), AGE number);
SQL> insert into test (NAME, AGE) values ('HuuPV', 29);
SQL> insert into test (NAME, AGE) values ('Huu', 30);
SQL> commit;

Examine the content of the table test created above

SQL> select * from test;

The result created a table for the test

Oracle create schema another method uses the script oracle_create_user.sql  file

The content oracle_create_user.sql file

CREATE USER HuuPV IDENTIFIED BY pwd4HuuPV DEFAULT TABLESPACE datafile_test TEMPORARY TABLESPACE TEMP;
GRANT CONNECT TO HuuPV;
GRANT UNLIMITED TABLESPACE TO HuuPV;
GRANT CREATE TABLE TO HuuPV;
GRANT CREATE PROCEDURE TO HuuPV;
GRANT CREATE VIEW TO HuuPV;
GRANT CREATE TRIGGER TO HuuPV;
GRANT CREATE SEQUENCE TO HuuPV;
GRANT CREATE SYNONYM TO HuuPV;

Running script oracle_create_user.sql file

SQL>@/home/oracle/scripts/oracle_create_user.sql

Conclusion

By following the steps outlined in this guide, you’ve learned how to create an Oracle schema effectively. This foundation is crucial for managing and organizing data in Oracle databases efficiently, providing you with the skills to maintain a robust database environment.

My topic is “Install Oracle Database 12c on Centos 7“. I hope you find this helpful.