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.
- 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.7Win -- Set up a service with the ORADIM utility. On the command line, type:
oradim -new -sid mydbsid -srvc OracleServiceMYDBSID
- Use SQL*Plus to start the database in NOMOUNT mode:
sqlplus /nolog connect sys as sydba startup nomount
- 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;
- 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.