SQL CREATE DATABASE Statement

by

The SQL CREATE DATABASE Statement

The SQL CREATE DATABASE statement is used to create a new database with the help of DBUA and the command line (manually) in SQL. But we create the database with some other methods too, below are the types of methods. Please find all types of methods step by step.

Types of sql database creation in oracle

Method 1: Create an Oracle database with a CREATE DATABASE statement.
Method 2: Create an Oracle database with DBCA (Database Configuration Assistant).
Method 3: Create an Oracle database with a response file.
Method 4: Create an Oracle database from an existing database.
Method 5: Create an Oracle database with the Oracle SQL Developer.

Method 1: Create an Oracle database with a CREATE DATABASE statement.

Syntax for SQL CREATE DATABASE Statement

CREATE DATABASE databasename;

Example of CREATE DATABASE

The below following below SQL statement creating a new database called “PRODDB”.

CREATE DATABASE PRODDB;

This article will walk you through the process of creating an Oracle database, from the first preparatory tasks all the way to the syntax of the Oracle CREATE DATABASE statement.

Detailed instructions for five different Oracle database creation methods are provided here. The CREATE DATABASE command will form the basis of two of these command-line-based approaches.

On top of pulling the CREATE DATABASE script from an already-existent database, In the other two, you’ll find instructions for using graphical user interface tools like DBCA and Oracle SQL. Developer. Oracle 12c, 11g, 10g, and later versions are compatible with this guide.

Before creating an Oracle database

Usually, sets or explore the ORACLE_HOME and ORACLE_SID automatically in the Windows. But here, we need to configure ORACLE_SID manually.

Set the Instance Identifier (SID)
The name of the new Oracle database you are going to establish is ORACLE_SID, one of the most significant environment variables. By using the following command, you can adjust it.

set ORACLE_SID=proddb (Windows)
export ORACLE_SID=proddb (Linux, AIX, Sun Solaris,etc)

Create the initialization parameter file

Making the init.ora file is the next thing to do. This file is going to be used to set up the new database. There may or may not be an example init.ora file in ORACLE_HOME/dbs, depending on your Oracle version. If one exists, you may customize it to fit your requirements by using it as a template.

The format for the new database’s initialization file should be INIT{ORACLE_SID}.ora. The name of the file will be initproddb.ora for us.

If you don’t have the default init.ora template in ORACLE_HOME/dbs. then use the template below and make changes according to your needs.

db_name=’PRODDB’
memory_target=5G
processes = 250
db_block_size=8192
db_domain=”
db_recovery_file_dest=’/flash_recovery_area’
db_recovery_file_dest_size=10G
diagnostic_dest=”
dispatchers='(PROTOCOL=TCP) (SERVICE=ORCLXDB)’
open_cursors=400
remote_login_passwordfile=’EXCLUSIVE’
undo_tablespace=’UNDOTBS1′

You may want to ensure that control files are created on separate physical devices

control_files = (ora_control1, ora_control2)
compatible =’12.0.0′

When you run the CREATE DATABASE command, use the same name for undo_tablespace as before.

It’s also important to change the directory names to fit your system. Do not forget to change the word “proddb” to the name of your ORACLE_SID.

Create an instance

The next action would be to create an instance, which would require running the ORADIM command from Command Prompt if it doesn’t exist.

oradim -NEW -SID sid -STARTMODE MANUAL -PFILE file

Connect to an instance
You need to connect to the instance after it has been made. That’s what we’ll do with SQL*Plus. In case you don’t already have it on your computer.

$ sqlplus /nolog

SQL*Plus will Connected to an idle instance.

After launching the SQL*Plus, Use the following steps to connect to your Oracle database machine as a SYSDBA privilege.

SQL> CONNECT / AS SYSDBA

Create server parameter file (spfile)

Because spfile, it is binary file, So you can’t edit or change the server parameter setting by manually. The spfile can be made from init.ora instead.
In SQL*Plus follow the below command to create spfile.

CREATE SPFILE FROM PFILE;

SQL> STARTUP NOMOUNT;
ORACLE instance started.

How to create a database in Oracle, using CREATE DATABASE command:

Using the CREATE DATABASE statement is one of the most popular ways to create a database in Oracle.

To CREATE ORACLE DATABASE statement.

CREATE DATABASE proddb
USER SYS IDENTIFIED BY sys_password
USER SYSTEM IDENTIFIED BY system_password
LOGFILE GROUP 1 (‘/u01/app/oracle/oradata/logs/redo01a.log’,’/u02/app/oracle/oradata/logs/redo01b.log’) SIZE 100M,
GROUP 2 (‘/u01/app/oracle/oradata/logs/redo02a.log’,’/u02/app/oracle/oradata/logs/redo02b.log’) SIZE 100M,
GROUP 3 (‘/u01/app/oracle/oradata/logs/redo03a.log’,’/u02/app/oracle/oradata/logs/redo03b.log’) SIZE 100M
MAXLOGHISTORY 1
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 1024
CHARACTER SET AL32UTF8
NATIONAL CHARACTER SET AL16UTF16
EXTENT MANAGEMENT LOCAL
DATAFILE ‘/u01/app/oracle/oradata/newproddb/system01.dbf’
SIZE 1000M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED
SYSAUX DATAFILE ‘/u01/app/oracle/oradata/newproddb/sysaux01.dbf’
SIZE 660M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED
DEFAULT TABLESPACE users
DATAFILE ‘/u01/app/oracle/oradata/newproddb/users01.dbf’
SIZE 700M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED
DEFAULT TEMPORARY TABLESPACE tempts1
TEMPFILE ‘/u01/app/oracle/oradata/newproddb/temp01.dbf’
SIZE 40M REUSE AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITED
UNDO TABLESPACE undotbs1
DATAFILE ‘/u01/app/oracle/oradata/newproddb/undotbs01.dbf’
SIZE 500M REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED
USER_DATA TABLESPACE usertbs
DATAFILE ‘/u01/app/oracle/oradata/newproddb/usertbs01.dbf’
SIZE 500M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;

NOTE:

  • Using the Oracle command line, this example makes a database called proddb.
  • The setup parameter file is where factors like the global database name and CONTROL_FILES are taken from.
  • We named three groups of redo log files in the LOGFILE line. They each have two members in them.
  • To define limits for the redo log, limits are set by the MAXLOGFILES, MAXLOGMEMBERS, and MAXLOGHISTORY parameter settings.
  • MAXDATAFILES changes how big the control file starts out and limits the number of data files that can be opened in the database.
  • The character sets that will be used to hold data in the database we are about to make are AL32UTF8 and AL16UTF16.
  • The SYSTEM tablespace is another important part of this command in Oracle. The operating system file /u01/app/oracle/oradata/newproddb/system01.dbf is made based on the DATAFILE clause.
  • The SYSTEM tablespace is set up to be locally managed.
  • As stated in the SYSAUX DATAFILE line, SYSAUX is made up of the system file /u01/app/oracle/oradata/newproddb/sysaux01.dbf.
  • DEFAULT TABLESPACE makes a default permanent tablespace and gives it a name.

Method 2: Create an Oracle Database with DBCA (Database Configuration Assistant)

Leave a Comment