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