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.
1 thought on “Oracle create schema”