Oracle Database Interview Question:

How To Run CREATE DATABASE Statement?

Submitted by: Administrator

This is Step 7. Oracle Administrator Guide provided a sample CREATE DATABASE statement. But it is a long statement. You can modify and same it in a file, $ORACLE_HOME/configscripts/create_database_ggl.sql, and run the file within SQL*Plus. Here is a copy of the modified CREATE DATABASE statement:

CREATE DATABASE ggl
USER SYS IDENTIFIED BY globalguideline
USER SYSTEM IDENTIFIED BY globalguideline
LOGFILE GROUP 1 ('/oraclexe/oradata/ggl/redo01.log') SIZE 10M,
GROUP 2 ('/oraclexe/oradata/ggl/redo02.log') SIZE 10M,
GROUP 3 ('/oraclexe/oradata/ggl/redo03.log') SIZE 10M
MAXLOGFILES 5
MAXLOGMEMBERS 5
MAXLOGHISTORY 1
MAXDATAFILES 100
MAXINSTANCES 1
CHARACTER SET US7ASCII
NATIONAL CHARACTER SET AL16UTF16
DATAFILE '/oraclexe/oradata/ggl/system01.dbf' SIZE 32M REUSE
EXTENT MANAGEMENT LOCAL
SYSAUX DATAFILE '/oraclexe/oradata/ggl/sysaux01.dbf' SIZE 32M REUSE
DEFAULT TABLESPACE tbs_1
DEFAULT TEMPORARY TABLESPACE tempts1
TEMPFILE '/oraclexe/oradata/ggl/temp01.dbf'SIZE 20M REUSE
UNDO TABLESPACE undotbs
DATAFILE '/oraclexe/oradata/ggl/undotbs01.dbf'
SIZE 20M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;
Submitted by: Administrator

Here is how to run this long CREATE DATABASE statement:

SQL> @$ORACLE_HOMEconfigscriptscreate_database_ggl.sql;
CREATE DATABASE ggl
*
ERROR at line 1:
ORA-01501: CREATE DATABASE failed
ORA-00200: control file could not be created
ORA-00202: control file: 'C:ORACLEXEORADATAgglCONTROL.DBF'
ORA-27040: file create error, unable to create file
OSD-04002: unable to open file
O/S-Error: (OS 3) The system cannot find the path specified.

Don't worry about the error messages. They are caused by the missing directory for data files in file system.
Submitted by: Administrator