Steps to create a database using windows cmd prompt:

Steps to create a database using windows cmd prompt:

Pre-requisite:

1. Oracle Database must be installed:

2. Basic knowledge of script and database is must.

3. Run the below scripts in ORACLE HOME path. 

Step 1:

Set dbname and parameters

set /p ORACLE_HOME=Enter the ORACLE_HOME path:

cd %ORACLE_HOME%

cd ../jjj../..

cd oradata

set /p name=Enter the database name:

mkdir %name%

cd %name%

echo "YOUR FILES WILL BE STORED ON THIS DIRECTORY: %cd%"

mkdir diag

Step 2:

Create file 1.sql:

echo startup pfile='%cd%\init%name%.ora' nomount; > 1.sql

echo create spfile from pfile='%cd%\init%name%.ora'; >> 1.sql

Step 3:

Create init.ora file with parameters and values

echo pga_aggregate_target=922746880 > init%name%.ora

echo sga_target=2751463424 >> init%name%.ora

echo compatible='11.2.0.0.0' >> init%name%.ora

echo control_files='%cd%/control01.ctl','%cd%\control02.ctl' >> init%name%.ora

echo db_block_size=8192 >> init%name%.ora

echo db_name=%name% >> init%name%.ora

echo diagnostic_dest='%cd%\diag' >> init%name%.ora

echo processes=600 >> init%name%.ora

echo undo_tablespace='undotbs' >> init%name%.ora

echo open_cursors=5000 >> init%name%.ora

Step 3:

Create file 2.sql with create database query.

echo CREATE DATABASE "%name%" > 2.sql

echo LOGFILE GROUP 1 '%cd%\redo01.log' SIZE 50M BLOCKSIZE 512, GROUP 2 '%cd%\redo02.log' SIZE 50M BLOCKSIZE 512, GROUP 3 '%cd%\redo03.log' SIZE 50M BLOCKSIZE 512

echo DATAFILE '%cd%\system01.dbf' size 500m REUSE >> 2.sql

echo SYSAUX DATAFILE '%cd%\sysaux01.dbf' SIZE 325M REUSE >> 2.sql

echo UNDO TABLESPACE undotbs DATAFILE '%cd%\undotbs01.dbf' SIZE 900M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED >> 2.sql

echo DEFAULT TABLESPACE users DATAFILE '%cd%\users01.dbf' SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED >> 2.sql

echo DEFAULT TEMPORARY TABLESPACE tempts1 TEMPFILE '%cd%\temptbs01.dbf' SIZE 200M REUSE; >> 2.sql

echo CREATE TABLESPACE T24DATA DATAFILE '%cd%\T24DATA.DBF' SIZE 500M AUTOEXTEND ON MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO; > tablespace.sql

echo CREATE TABLESPACE T24INDEX DATAFILE '%cd%\T24INDEX.DBF' SIZE 500M AUTOEXTEND ON MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO; >> tablespace.sql

Step 4:

Create file user.sql with privileges 

echo CREATE USER t24 IDENTIFIED BY t24 DEFAULT TABLESPACE T24DATA QUOTA UNLIMITED ON T24DATA QUOTA UNLIMITED ON T24INDEX; > user.sql

echo grant connect to t24; >> user.sql

echo grant resource to t24; >> user.sql

echo grant query rewrite to t24; >> user.sql

echo grant create synonym to t24; >> user.sql

echo grant drop any procedure to t24; >> user.sql

echo grant create any table to t24; >> user.sql

echo grant select any table to t24; >> user.sql

echo grant update any table to t24; >> user.sql

echo grant insert any table to t24; >> user.sql

echo grant delete any table to t24; >> user.sql

echo grant drop any table to t24; >> user.sql

echo grant create any index to t24; >> user.sql

echo grant alter any index to t24; >> user.sql

echo grant drop any index to t24; >> user.sql

echo grant create any view to t24; >> user.sql

echo grant drop any view to t24; >> user.sql

echo grant create any directory to t24; >> user.sql

echo grant exp_full_database to t24; >> user.sql

echo grant imp_full_database to t24; >> user.sql

echo @%ORACLE_HOME%/rdbms/admin/catblock.sql >> user.sql

echo grANT EXECUTE ON DBMS_LOCK TO T24; >> user.sql

Step 5:

Create file run.sql with order of files to run 

echo @1.sql; > run.sql

echo @2.sql; >> run.sql

echo @%ORACLE_HOME%/rdbms/admin/catalog.sql; >> run.sql

echo @%ORACLE_HOME%/rdbms/admin/catproc.sql; >> run.sql

echo CONNECT system/system as sysdba; >> run.sql

echo @%ORACLE_HOME%/sqlplus/admin/pupbld.sql; >> run.sql

echo startup force >> run.sql

echo @tablespace.sql; >> run.sql

echo @user.sql; >> run.sql

Update the %name% with actual db name you are going to create and run the below steps. :

ORADIM -NEW -SID %name%

set ORACLE_SID=%name%

sqlplus / as sysdba < run.sql