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
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
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
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
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=188.8.131.52 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 184.108.40.206. 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
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
Get the Complete
Oracle Tuning Details
The landmark book
Tuning: The Definitive Reference Second Edition" has been
updated with over 1,150 pages of expert performance tuning tips.
It's packed with scripts and tools to hypercharge Oracle 11g
performance and you can
buy it for 40% off directly from the publisher.
Download your Oracle scripts now:
definitive Oracle Script collection for every Oracle professional DBA
1996 -2011 by Burleson Enterprises. All rights reserved.
Oracle® is the registered trademark of Oracle
Corporation. SQL Server® is the registered trademark of Microsoft
Many of the designations used by computer vendors to distinguish their
products are claimed as Trademarks