Creating a new database without using Oracle Data Assistant

How to create a new database in Oracle without using Oracle Data Assistant? Can you please explain how I can use the CREATE DATABASE statement in Oracle?

You didn't specify which platform you are creating a database on. So I'll give you the UNIX and Windows platform instructions.

1. UNIX -- Set up your environment variables ORACLE_SID and ORACLE_HOME. This is shell dependant. For instance, in cshell, I type:

     
      setenv ORACLE_SID mydbsid
      setenv ORACLE_HOME /install_dir/8.1.7
   

   Win -- Set up a service with the ORADIM utility. On the command line, type:

     
     oradim -new -sid mydbsid -srvc OracleServiceMYDBSID
   

   
   
2. Use SQL*Plus to start the database in NOMOUNT mode:

     
      sqlplus /nolog
      connect sys as sydba
      startup nomount
   

3. Issue the CREATE DATABASE command:

   create database mydbsid
     logfile
       group 1 ('/somedir/redo_mydbsid01a.log',
                '/somedir/redo_mydbsid01b.log') size 2M,
       group 2 ('/somedir/redo_mydbsid02a.log',
                '/somedir/redo_mydbsid02b.log') size 2M,
       group 3 ('/somedir/redo_mydbsid03a.log',
                '/somedir/redo_mydbsid03b.log') size 2M
         datafile
           '/somedir/system_mydbsid.dbf'    size 300M
   autoextend on
                next 50M maxsize 500M
   maxlogfiles 32
   maxlogmembers 3
   maxdatafiles 500
   character set "US7ASCII"
   noarchivelog;
   

4. Run CATALOG and CATPROC to finalize the creation:

   SQL> @$ORACLE_HOME/rdbms/admin/catalog.sql
   SQL> @$ORACLE_HOME/rdbms/admin/catproc.sql
   

That's the basics!!! Of course, the CREATE DATABASE command has many different options and parameters. You'll probably want to look these up before creating the database. After the database is created, you'll need to create tablespaces, users, etc.

For More Information

• What do you think about this answer? E-mail the editors at editor@searchDatabase.com with your feedback.
• The Best Oracle Web Links: tips, tutorials, scripts, and more.
• Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
• Ask your technical Oracle and SQL questions -- or help out your peers by answering them -- in our live discussion forums.
• Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.