Create Database   «Prev  Next»

Lesson 2Writing the CREATE DATABASE command
ObjectiveWrite the command to create the COIN database.

Writing the CREATE DATABASE command

To create a new database, you need to start a new Oracle instance and execute a CREATE DATABASE command. This command will create the control files, the system tablespace file (or files), and one rollback segment in the system tablespace. Because CREATE DATABASE is a somewhat long command, it's best to write it ahead of time, and store it in a text file. Move your mouse over the following image to learn about the syntax of the command.
Indicates that you want to reuse an existing set of control files by overwriting them.
Oracle `CREATE DATABASE` command script. Here is the printed version of the script based on the image: 
CREATE DATABASE database_name
[CONTROLFILE_REUSE]
[LOGFILE GROUP group_no
    ('log_file_name'[, 'log_file_name', ...])
    [SIZE integer [K|M]] [, 
    GROUP group_no 
    ('log_file_name'[, 'log_file_name', ...])
    [SIZE integer [K|M]]
    ...]]
[MAXLOGFILES integer]
[MAXLOGMEMBERS integer]
[MAXLOGHISTORY integer]
[MAXDATAFILES integer]
[MAXINSTANCES integer]
[ARCHIVELOG|NOARCHIVELOG]
[CHARACTER SET 'charset']
[NATIONAL CHARACTER SET 'charset']
[DATAFILE 'data_file_name' [SIZE integer [K|M]] 
    [AUTOEXTEND {OFF|ON [NEXT integer [K|M]] 
    [MAXSIZE {UNLIMITED | integer [K|M]}]
    }]
];

This script is used to create a new Oracle database with specified configurations such as control file reuse, log file groups, maximum limits for logs, data files, instances, and optional archive mode. You can also specify character sets and data files with auto-extension options.
  1. CONTROLFILE_REUSE: Indicates that you want to reuse an existing set of control files by overwriting them. I rarely use this option. It's usually just as easy to recreate them from scratch.
  2. LOGFILE: Introduces the LOGFILE clause, which lets you specify an initial set of redo logs for your database.
  3. GROUP group_no: Logfiles are organized into groups. Each group has a number to identify it. The same information is written to each member of a group.
  4. log_file_name: The full path and filename of a log file.
  5. SIZE integer[K M]]: An optional clause that allows you to specify the size of a log file or a datafile. You should always use this. Don't rely on default file sizes. You may specify the size in bytes, kilobytes (K), or megabytes (M).
  6. MAXLOGFILES integer: Places an upper limit on the number of redo log groups that may be created for the database. Oracle reserves space for these groups in the control file.
  7. MAXLOGMEMBERS integer: Places an upper limit on the number of members in a redo log group.
  8. MAXLOGHISTORY integer: Used when running Oracle Parallel Server in archivelog mode. This controls the maximum number of archived log files that are tracked in the control file.
  9. MAXDATAFILES integer: Controls the initial size of the area within the control file that is used to keep track of datafiles. Whether this is a maximum depends on the value of the DB_FILES initialization parameter.
  10. MAXINSTANCES integer: Used with Parallel Server, and specifies the maximum number of instances that can simultaneously open this database.
  11. ARCHIVELOG: Causes the database to immediately be placed into archive log mode when it is created. I usually prefer not to do this. Instead I turn on archive log mode after all database, schema, and creation tasks have been completed.
  12. NOARCHIVELOG: This is the default setting and causes the database to be created in noarchive log mode.
  13. CHARACTER_SET ‘charset': Specifies the character set that the database uses to store data.
  14. NATIONAL_CHARACTER_SET ‘charset': Specifies the character set used to store data in NLS columns, such as those defined as NCHAR, NCLOB, and NVARCHAR2.
  15. DATAFILE ‘data_file_name': Specifies the name and directory path to use for the system tablespace's datafile
  16. AUTOEXTEND: Controls whether Oracle is allowed to expand the size of a datafile automatically. You have two choices: ON and OFF
  17. OFF: Tells Oracle that the datafile may not be automatically extended. You will need to manually extend the file or add more files if you need more space.
  18. ON: Tells Oracle that it can expand the size of the datafile when necessary.
  19. NEXT integer [K M]]: If autoextend is on, you can specify the amount of space to be added each time you extend the datafile.
  20. MAXSIZE {UNLIMITED integer [K M]]: If autoextend is on, allows you to place an upper limit on the file size.


Specific clauses in command

The various clauses, such as MAXLOGFILES, MAXDATAFILES, and so forth, don't need to be written in the order shown above. You can write them in any order that you like. Most of the clauses are optional. They all have defaults, and unless I have specific reasons to change a particular default, I usually leave the clause out of the command. For this course, you should do the same. Two clauses that I always include, however, are the LOGFILE and DATAFILE clauses. I always include these so that I can explicitely name the files, specify their sizes, and place them where I want. Sizing is important. You, as the DBA, should always exert control over the size of your database files.
  • Using the MAX clauses:
    If you do decide to use the MAXLOGFILES, MAXLOGMEMBERS, and similar clauses of the CREATE DATABASE command, be sure to carefully read the manuals so that you understand exactly what these clauses do, and how they relate to the database initialization parameters.
    Take MAXLOGFILES, for example. The temptation will be to set it to match the number of log files specified in the CREATE DATABASE command. This parameter actually relates to the number of log file groups, not individual files. Further, it is constrained at the lower end by the value of the log_files initialization parameter. This varies by release and operating system. On Windows NT, using Oracle 8.1.5, the default log_files value is 255, and consequently the lower limit for MAXLOGFILES would also be 255. To go lower, you would need to modify the log_files parameter and restart the instance. There is nothing inherently wrong with using these clauses. It is just that sometimes there is more going on than meets the eye.

Write Create Database - Exercise

Now, click the Exercise button to write the CREATE DATABASE statement for your COIN database.
Write Create Database - Exercise

SEMrush Software 2SEMrush Software Banner 2