Creating an Oracle Database

By Kamran Agayev A.

Before starting this chapter, I’d like to direct you to my step by step guide on “Create database manually“

If you omit to assign a password at CREATE DATABASE command, the SYS and SYSOPER users are assigned the default passwords change_on_install and manager, respectively. Oracle strongly recommends that you specify these clauses, even though they are optional

Specify the EXTENT MANAGEMENT LOCAL clause in the CREATE DATABASE statement to create a locally managed SYSTEM tablespace. If you do not specify the EXTENT MANAGEMENT LOCAL clause, by default the database creates a dictionary managed SYSTEM tablespace.

The SYSAUX tablespace serves as an auxiliary tablespace to the SYSTEM tablespace. BECAUSE IT IS THE DEFAULT TABLESPACE FOR MANY Oracle Database features and products that previously required their own tablespaces, it reduces the number of tablespaces required by the database and that you must maintain.

You can’t drop or rename the SYSAUX tablespace

The SYSAUX tablespace needs to be at least 240MB at the time of database creation

The maximum number of datafiles in an Oracle Databsae is limited (usually to 64K files – 65536 files)

The maximum number of blocks in a datafile is 4GB (4 million blocks). 

Bigfile tablespace can contain only one file, but that file can have up to 4G blocks

A smallfile tablespace can contain up to 1022 files with up to 4M block each

To dynamically change the default tablespace type after database creation, use alter DATABASE SET DEFAULT BIGFILE TABLESPACE;  command

It is recommended that you use a server parameter file

DB_NAME must be set to a text string of no more than eight characters. During database creation, the name provided for DB_NAME is recorded in the datafiles, redo log files, and contro file of the database.

You can’t change the block size after database creation except by recreating the database.

The SGA_MAX_SIZE initialization parameter specifies the maximum size of the System Global Area for the lifetime of the instance. You can dynamically alter the initialization parameters affecting the size of the buffer caches, shared pool, large pool, Java pool and streams pool.

You enable automatic shared memory management feature by setting the SGA_TARGET parameter to a non-zero value. In addition, you must set the value of all automatically sized SGA components to a zero to enable full automatic tuning of these components. When SGA_TARGET is not set, the automatic shared memory management feature is not enabled.

When you increase the size of a manually sized component, extra memory is taken away from one or more automatically sized components. And when you decrease the size of a manually sized component, the memory that is released is given to the automatically sized components

The COMPATIBLE initialization parameter enables or disables the use of features in the databsae that affect file format on disk. For example, if you create an Oracle Database 10g database, but specify  COMPATIBLE=9.2.0.2 in the initialization parameter file, then features that requires 10.0 compatibility will generate an error if you try to use them. Such a database is said to be at the 9.2.0.2. compatibility level.

To limit the number of users created in a database, set the LICENSE_MAX_USERS initialization parameter in the database initialization parameter file

The COMMENT clause at ALTER SYSTEM command lets you associate a text string with the parameter update. When you specify SCOPE as SPFILE or BOTH, the comment is written to the server parameter file.

To view initialization file’s parameter use SHOW PARAMETER command, CREATE PFILE command or V$PARAMETER view (this view displays the values of parameters currently in effect)

V$SPPARAMETER view displays the current contents of the server parameter file. The view returns FALSE values in the ISSPECIFIED column if a server parameter file is not being used by the instance