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.

Oracle create 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.