Oracle create schema

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 the schema.

Access the Oracles database with permission privileges for creating a tablespace, create a user and create a table on 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 file) 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 create 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 create a table for 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 table test create above

SQL> select * from test;

The result created a table for test

Oracle create schema another method  use 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

Thought the article, you have created a schema in Oracle database. My topic another  “Install Oracle Database 12c on Centos 7“. I hope will this your helpful.