How to create a database in Oracle?

2 years ago
William Carter
2 minutes

To create an Oracle database, you can follow these steps:

  1. Firstly, make sure that you have installed the Oracle database software and have started the database instance.
  2. Open SQL*Plus and login to the database with administrator privileges (sysdba). Enter the following command at the command prompt and press Enter:
sqlplus / as sysdba
  1. Create a database instance. In SQL*Plus, input the following command and press enter:
CREATE DATABASE database_name
  CONTROLFILE REUSE
  LOGFILE GROUP 1 ('log1a.f', 'log1b.f') SIZE 50M,
           GROUP 2 ('log2a.f', 'log2b.f') SIZE 50M
  MAXLOGFILES 5
  MAXLOGMEMBERS 5
  MAXDATAFILES 100
  MAXINSTANCES 1
  CHARACTER SET utf8
  NATIONAL CHARACTER SET utf8;

Among them, database_name is the name of the database instance you want to create.

  1. Create a tablespace. Input the following command in SQL*Plus and press Enter:
CREATE TABLESPACE tablespace_name
  DATAFILE 'datafile.f' SIZE 100M
  AUTOEXTEND ON
  NEXT 100M
  MAXSIZE 10G;

tablespace_name is the name of the tablespace you want to create, and datafile.f is the name of the data file for the tablespace.

  1. Create users and grant privileges. In SQL*Plus, type in the following command and press Enter:
CREATE USER username IDENTIFIED BY password
  DEFAULT TABLESPACE tablespace_name
  TEMPORARY TABLESPACE temp
  QUOTA UNLIMITED ON tablespace_name;
GRANT CONNECT, RESOURCE, CREATE SESSION TO username;

In this case, username is the designated username you want to create, password is the user’s password, and tablespace_name is the default tablespace for the user.

  1. Finally, exit SQL*Plus. Enter the following command at the command prompt and press Enter:
EXIT;

You have now successfully created an Oracle database.

bannerAds