Creating a New Database

A new database can be created from the DBCA (Database Creation Assistant) GUI or from the command line.

Here is the SQL commands required to create a new Oracle database:

CONNECT SYS AS SYSDBA;
ALTER SYSTEM SET DB_CREATE_FILE_DEST = ‘/u01/oradata/’;
ALTER SYSTEM SET DB_CREATE_ONLINE_LOG_DEST_1=’ /u02/oradata/’;
ALTER SYSTEM SET DB_CREATE_ONLINE_LOG_DEST_2=’ /u03/oradata/’;
CREATE DATABASE;

A more advanced example:

CREATE DATABASE orcl CONTROLFILE REUSE
LOGFILE
  GROUP 1 ('/oradata/orcl/log01a.log', '/oradata/orcl/log01b.log') size 50M,
  GROUP 2 ('/oradata/orcl/log02a.log', '/oradata/orcl/log02b.log') size 50M,
  GROUP 3 ('/oradata/orcl/log03a.log', '/oradata/orcl/log03b.log') size 50M
DATAFILE
  '/oradata/orcl/system.dbf' 250M 
AUTOEXTEND ON MAXSIZE 500M
UNDO TABLESPACE undots
   DATAFILE '/oradata/orcl/undots.dbf' 50M
DEFAULT TEMPORARY TABLESPACE temp 
   TEMPFILE '/oradata/orcl/temp.dbf' size 75M
EXTENT MANAGEMENT LOCAL
CHARACTER SET US7ASCII
MAXLOGFILES 10
MAXLOGMEMBERS 10
MAXLOGHISTORY 1
MAXDATAFILES 500;