SQL Server vs Oracle Database Creation Similarities and Differences

By: Pablo Echeverria   |   Updated: 2022-02-09   |   Comments   |   Related: > SQL Server and Oracle Comparison

Problem

You might think creating a database for SQL Server versus Oracle would be somewhat similar, but in this tutorial we look at the differences of creating new databases in Oracle and then creating new databases in SQL Server to give you an idea of what needs to be done if you are ever tasked with creating a database for either of these database platforms.

Solution

There are different steps to be performed in each database system, you will see them described in the following sections and remember not all are mandatory and this is not a definitive guide on how this needs to be done. This is just a guideline and reference you can use to see the differences in the two database platforms. The below is done after the database system has been installed. Also, the below shows how databases can be created with commands so you learn SQL, not using a GUI.

Create Databases in Oracle

To create an Oracle database, you must choose the instance name (8 chars), create a parameter file (PFILE), create an instance (ORADIM), and finally create the database using PL/SQL. The official documentation is located here for administration and here for installation, these are both for Oracle version 19c.

The first step is choosing the instance name (SID), which is limited to 8 chars. This is the Site Identifier used to uniquely attach the shared memory segment called System Global Area (SGA). It must be unique in your network, changing it later is hard to do since it is referenced in all future steps regarding the database, so choose wisely. The equivalent in SQL Server can be up to 128 characters.

The second step is to create the parameter file, you can look at the sample located in the folder C:\app\Oracle19c\dbs\init.ora, DB_NAME doesn't need to match the SID but it's easier to manage if it does, and the parameter file name must be initSID.ora. The official documentation about the initialization parameters can be found here, and the available/deprecated parameters, their sample usage and their recommended values is in an article I've written which can be found here. The equivalent in SQL Server are "alter server", "sp_configure", "dbcc traceon", "alter database", and "alter database scoped configuration". In my case I created the simplest parameter file with the PowerShell commands below (define a multiline variable, add the only mandatory parameter, and write the file):

$init=@" 
db_name=ORCL 
"@ 
Add-Content C:\APP\ORACLE19C\DATABASE\INITORCL.ORA $init 

The third step is to create a Windows service to run the database, in SQL Server the services are already created and running after you installed the software. To create the service for Oracle you need to run ORADIM from the command prompt which creates the Oracle Database, VSS Writer and Oracle Scheduler services, the documentation can be found here and the syntax is as follows:

Example:

oradim -NEW -SID prod -STARTMODE manual -PFILE "C:\app\username\admin\prod\pfile\init.ora"

Note you can omit the parameter file if it's already in the default location which it is in my case, so the command I ran was:

ORADIM -NEW -SID ORCL

Then you can verify the service is created by running this PowerShell command:

Get-Service -DisplayName '*Oracle*'

The output in my case is as follows:

If any service is stopped, you can start it with this PowerShell command:

net START OracleServiceORCL

Or you can start it with the PowerShell command below:

Start-Service OracleServiceORCL

Or you can start it using ORADIM, the documentation can be found here and the syntax is as follows:

oradim -STARTUP -SID SID | -ASMSID SID [-SYSPWD password] [-STARTTYPE srvc | inst | srvc,inst] [-PFILE filename | -SPFILE]

Example:

oradim -STARTUP -SID puma -STARTTYPE inst -PFILE C:\app\username\admin\prod\pfile\init.ora

Once the service has been created and is running, you can set the ORACLE_SID variable to the SID you chose in the first step, login to the database, and start the SGA and background processes as follows:

set ORACLE_SID=ORCL 
sqlplus / as sysdba 
STARTUP NOMOUNT 

Finally, you can proceed to the last step: creating the database.

Below you will find a table with various options for creating the database. On the left are the commands and on the right is a description and some examples. Whatever options you use, they all must be a single command. Although the instructions contain multiple parameters not all of them are mandatory; they depend on what action you're performing and if you want to override the defaults or not.

In Oracle there is no "MODEL" database like in SQL Server, because you only create one database per instance, but you can choose either container (CDB) and pluggable (PDB) databases. The documentation can be found here.

Example 1: Create a non-CDB (non-container) create database statement with all defaults:

CREATE DATABASE ORCL;

Example 2: Create a non-CDB (non-container) database with default options for SYSAUX, DEFAULT, DEFAULT TEMPORARY and USER_DATA. Note how you need to specify each system file with their size and increment. The log will be replicated in three files.

CREATE DATABASE prod 
USER SYS IDENTIFIED BY sys_password 
USER SYSTEM IDENTIFIED BY system_password 
MAXLOGFILES 5 
MAXDATAFILES 100 
DATAFILE 'C:\app\username\oradata\prod\system01.dbf' SIZE 325M REUSE 
AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED 
UNDO TABLESPACE "UNDOTBS" DATAFILE 'app\username\oradata\prod\undotbs01.dbf' 
SIZE 200M REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED 
CHARACTER SET AL32UTF8 
LOGFILE 'C:\app\username\oradata\prod\redo01.log' size 100M reuse, 
        'C:\app\username\oradata\prod\redo02.log' size 100M reuse, 
        'C:\app\username\oradata\prod\redo03.log' size 100M reuse 
EXTENT MANAGEMENT LOCAL; 

Example 3: Create a CDB (container) database with three log groups each consisting of two files. The PDB (pluggable) databases need to be created later, which are the ones that will contain the data.

CREATE DATABASE newcdb 
  USER SYS IDENTIFIED BY sys_password 
  USER SYSTEM IDENTIFIED BY system_password 
  LOGFILE GROUP 1 ('C:\redo01a.log','D:\redo01b.log') 
             SIZE 100M BLOCKSIZE 512, 
          GROUP 2 ('C:\redo02a.log','D:\redo02b.log') 
             SIZE 100M BLOCKSIZE 512, 
          GROUP 3 ('C:\redo03a.log','D:\redo03b.log') 
             SIZE 100M BLOCKSIZE 512 
  MAXLOGHISTORY 1 
  MAXLOGFILES 16 
  MAXLOGMEMBERS 3 
  MAXDATAFILES 1024 
  CHARACTER SET AL32UTF8 
  NATIONAL CHARACTER SET AL16UTF16 
  EXTENT MANAGEMENT LOCAL 
  DATAFILE 'C:\system01.dbf' 
    SIZE 700M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED 
  SYSAUX DATAFILE ' C:\sysaux01.dbf' 
    SIZE 550M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED 
  DEFAULT TABLESPACE deftbs 
    DATAFILE 'C:\deftbs01.dbf' 
    SIZE 500M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED 
  DEFAULT TEMPORARY TABLESPACE tempts1 
    TEMPFILE 'C:\temp01.dbf' 
    SIZE 20M REUSE AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITED 
  UNDO TABLESPACE undotbs1 
    DATAFILE 'C:\undotbs01.dbf' 
    SIZE 200M REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED 
  ENABLE PLUGGABLE DATABASE 
    SEED 
    FILE_NAME_CONVERT = (' C:\app\oracle\oradata\newcdb\', 
                         ' C:\app\oracle\oradata\pdbseed\') 
    SYSTEM DATAFILES SIZE 125M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED 
    SYSAUX DATAFILES SIZE 100M 
  USER_DATA TABLESPACE usertbs 
    DATAFILE 'C:\usertbs01.dbf' 
    SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED; 

I just used the simplest way possible with all the defaults as shown below:

CREATE DATABASE ORCL;

At this point your database has been created, but you need to manually create system objects running the scripts below:

@?/rdbms/admin/catalog.sql --create data dictionary views and dynamic performance views 
@?/rdbms/admin/catproc.sql --scripts for the procedural option (PL/SQL) 
@?/sqlplus/admin/pupbld.sql --re-create the PRODUCT_USER_PROFILE table, allowing to disable commands on a user-by-user basis 
@?/rdbms/utlrp.sql -- validate and recompile invalid objects 

After the scripts have been run, you can view the database configuration with queries below.

You can view the control file location with the query below, note it's binary so you can't read its content with a text editor:

SHOW PARAMETER CONTROL_FILES

You can view the MAXDATAFILES with the query below, the output is 32:

SELECT RECORDS_TOTAL FROM V$CONTROLFILE_RECORD_SECTION WHERE TYPE='DATAFILE';

You can view the MAXINSTANCES with the query below, the output is 16:

SELECT RECORDS_TOTAL FROM V$CONTROLFILE_RECORD_SECTION WHERE TYPE='REDO THREAD';

You can view the CHARACTER SET with the query below (you can also use SYS.DATABASE_PROPERTIES), the output is US7ASCII:

SELECT VALUE$ FROM SYS.PROPS$ WHERE NAME='NLS_CHARACTERSET';

You can view the default tablespace with the query below (you can also use SYS.DATABASE_PROPERTIES), the output is SYSTEM:

SELECT VALUE$ FROM SYS.PROPS$ WHERE NAME='DEFAULT_PERMANENT_TABLESPACE';

You can view the created log groups and files with the query below:

COL GROUP# FOR 99 
COL MEMBER FOR A50 
SELECT GROUP#, MEMBER FROM V$LOGFILE; 

You can view the MAXLOGFILES and MAXLOGHISTORY with the query below, the output is 32 and 1752 respectively:

SELECT TYPE, RECORDS_TOTAL FROM V$CONTROLFILE_RECORD_SECTION WHERE TYPE IN ('REDO LOG', 'LOG HISTORY');

You can view the created tablespaces and files with the query below:

SET LINESIZE 300 
COL FILE_NAME FOR A40 
COL TABLESPACE_NAME FOR A15 
COL BLOCKS FOR 999999 
COL MAXBYTES FOR 999999999999 
SELECT FILE_NAME, TABLESPACE_NAME, BYTES, BLOCKS, AUTOEXTENSIBLE, MAXBYTES, MAXBLOCKS, INCREMENT_BY FROM DBA_DATA_FILES;

And the rest of the default parameters are in one of these tables already described, you can explore them to see what you can find.

Create Database Statement in SQL Server

To create a Microsoft SQL Server 2019 database, you only need to create the database using T-SQL run from SQL Server Management Studio (SSMS) with the proper permissions. The SQL Server instance is already running when you installed SQL Server, but there may be additional steps needed to access additional features.

Below you will find a similar table with SQL CREATE DATABASE commands on the left and a description and examples on the right. Although the instructions contain multiple parameters not all of them are mandatory; they depend on what action you're performing and if you want to override the defaults or not. Also, all command options need to be run at once to create the database. For the SQL Server database engine, the "model" database is used as the example for new databases so all settings are copied unless specified along with any tables, views, stored procedures, data types that exist in the model database. The documentation can be found here: CREATE DATABASE

Example 1: SQL command to create a database with all default options (using model database settings).

CREATE DATABASE mytest; 

Example 2: SQL command to specify the data and log files with their size, max size, and increment.

CREATE DATABASE Sales 
ON 
    ( NAME = SalesData, FILENAME = 'C:\Data\SalesData.mdf', SIZE = 10, MAXSIZE = 50, FILEGROWTH = 5 ) 
LOG ON 
    ( NAME = SalesLog, FILENAME = 'D:\Log\SalesLog.ldf', SIZE = 5MB, MAXSIZE = 25MB, FILEGROWTH = 5MB); 

Example 3: SQL command to create a database with two data files, two file groups and each filegroup with two data files.

CREATE DATABASE Sales 
ON PRIMARY 
    ( NAME = SalesData1, FILENAME = 'C:\Data\SalesData1.mdf', SIZE = 10, MAXSIZE = 50, FILEGROWTH = 15% ), 
    ( NAME = SalesData2, FILENAME = 'C:\Data\SalesData2.ndf', SIZE = 10, MAXSIZE = 50, FILEGROWTH = 15% ), 
FILEGROUP SalesFileGroup1 
    ( NAME = SalesData3, FILENAME = 'C:\Data\SalesData3.ndf', SIZE = 10, MAXSIZE = 50, FILEGROWTH = 5 ), 
    ( NAME = SalesData4, FILENAME = 'C:\Data\SalesData4.ndf', SIZE = 10, MAXSIZE = 50, FILEGROWTH = 5 ), 
FILEGROUP SalesFileGroup2 
    ( NAME = SalesData5, FILENAME = 'C:\Data\SalesData5.ndf', SIZE = 10, MAXSIZE = 50, FILEGROWTH = 5 ), 
    ( NAME = SalesData6, FILENAME = 'C:\Data\SalesData6.ndf', SIZE = 10, MAXSIZE = 50, FILEGROWTH = 5 ) 
LOG ON 
    ( NAME = SalesLog, FILENAME = 'D:\Log\SalesLog.ldf', SIZE = 5MB, MAXSIZE = 25MB, FILEGROWTH = 5MB ); 

Example 4: Create a SQL contained database in a specific language that can take ownership and impersonate in other databases.

CREATE DATABASE Contained 
CONTAINMENT=PARTIAL 
COLLATE French_CI_AI 
WITH TRUSTWORTHY ON, DB_CHAINING ON; 

Conclusion

As you can see, there are several differences, one of them being the max database size: in SQL Server 524 TB, in Oracle 8 PB (smallfile and 32K block size) or 8589 PB (bigfile and 32K block size).

In Oracle the database creation process allows you to truly separate Log, Data, SYSAUX/USER_DATA (additional functionality), user space, Temp and Undo. If you know the database will be big, it's better to create tablespaces as BIGFILE which is like how SQL Server manages data, in a single file; note it still can be backed up by RMAN in parallel using multiple channels, simplifies management and improves performance (checkpoint operations no longer must update too many data file headers).

In SQL Server, one of the interesting things is that you can specify the file growth in percentage (which isn't available in Oracle) and the system objects are already created for you, there's no need to create them or re-compile them.

Next Steps

• Here is the tip about installing SQL Server and Oracle on Windows Containers.
• Here is a tip on creating databases in SQL Server.
• Here is a tip about SQL Server and Oracle terminology comparison.
• Additional reading:
  • New Drop Database If Exists Syntax in SQL Server 2016
  • Drop All Tables in a SQL Server Database
  • Script to Drop and Delete Offline SQL Server Databases and Data Files

About the author

Pablo Echeverria is a talented database administrator and C#.Net software developer since 2006. Pablo wrote the book "Hands-on data virtualization with Polybase". He is also talented at tuning long-running queries in Oracle and SQL Server, reducing the execution time to milliseconds and the resource usage up to 10%. He loves learning and connecting new technologies providing expert-level insight as well as being proficient with scripting languages like PowerShell and bash. You can find several Oracle-related tips in his LinkedIn profile.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips