Now that I know what are the physical structures and how I am to care for, please let me know about the logical structures of Oracle database.

Database is the highest-level logical structure in an Oracle Database, which is created by executing ‘create database command’. The following are the important decisions needed before a database is created.

01. Space required for creating tables and indexes
02. Lay out of the data files to balance the I/O
03. Decide on the global database name
04. Decide on the memory usage and define the initialization parameter file
05. Select the database character
06. Consider what time zones your database must support
07. Select the standard database block size which is used to create system tablespace and other tablespaces you wanted to create with that data block size and other data block sizes which you want to use for the tablespaces
08. Undo tablespace
09. Decide on the backup and recover strategy

Command to create a database:

Oracle 9i (including R2)

CREATE DATABASE mynewdb
USER SYS IDENTIFIED BY sys
USER SYSTEM IDENTIFIED BY manager
LOGFILE GROUP 1 (‘d:Oracleoradatamynewdbredo_01_01.log’,
‘e:Oracleoradatamynewdbredo_01_02.log’) SIZE 100M,
GROUP 2 (‘e:Oracleoradatamynewdbredo_02_01.log’,
‘f:Oracleoradatamynewdbredo_02_02.log’) SIZE 100M,
GROUP 3 (‘f:Oracleoradatamynewdbredo_03_01.log’,
‘d:Oracleoradatamynewdbredo_03_01.log’) SIZE 100M
MAXLOGFILES 5
MAXLOGMEMBERS 5
MAXLOGHISTORY 1
MAXDATAFILES 100
MAXINSTANCES 1
CHARACTER SET US7ASCII
NATIONAL CHARACTER SET AL16UTF16
DATAFILE ‘d:Oracleoradatamynewdbsystem01.dbf’ SIZE 325M REUSE
EXTENT MANAGEMENT LOCAL
DEFAULT TEMPORARY TABLESPACE tempts1
DATAFILE ‘d:Oracleoradatamynewdbtemp01.dbf’ SIZE 20M REUSE
UNDO TABLESPACE undotbs
DATAFILE ‘d:Oracleoradatamynewdbundotbs01.dbf’
SIZE 200M REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED;

Oracle 10g

You are to specify the tablespace for system objects also.

Upgrading the existing databases from one version to another version or migrating databases from one platform and version to another platform and version is an important topic for separate discussion. This could be an important job in the life an Oracle DBA. So, let us discuss that at an appropriate time and place and not hurry up us.


After the database is created putting together some physical structures with the instance created using the initialization parameter file and controlfiles that have been associated with that instance.

Now it is time to create tablespaces that are required to hold user data. When the database is created, a system tablespace is created; we create a default temporary tablespace and an undo tablespace. You can take out those clauses related to temporary tablespace and undo tablespace and include them in the listed tablespaces to be created for the user data. In such circumstances, you should not forget to comment out the undo tablespace parameters prescribed. After creating undo tablespace uncomment them, so that the undo management is done by Oracle which we call AUTO UNDO MANAGEMENT.

The tablespaces are the second level of logical structures.

One or more data files are associated with each tablespace and thus the relationship between the logical and physical structures is established.

When creating tablespaces, you should know which objects you are going to create in that tablespace. The estimated size of each object is necessary to estimate the size of the tabelspace, which hosts them. The spread of the associated data files across the disks determines the I/O distribution for that tablespace. When you are viewing all the tablespaces the spread of the data files associated with those tablespaces should be in such a way as to help distribute the IO across the disks evenly and then help the users access the data as quickly as possible with no contentions of what so ever.

The steps for creating tablespaces vary by operating system. In all cases, however, you should create through your operating system a directory structure in which your data files will be allocated. On most operating systems you indicate the size and fully specified filenames when creating a new tablespace or altering a tablespace by adding data files. In each situation Oracle automatically allocates and formats the data files as specified.

To create a new tablespace, use the SQL statement CREATE TABLESPACE or CREATE TEMPORARY TABLESPACE. You must have the CREATE TABLESPACE system privilege to create a tablespace. Later, you can use the ALTER TABLESPACE or ALTER DATABASE statements to alter the tablespace. You must have the ALTER TABLESPACE or ALTER DATABASE system privilege, correspondingly.

Prior to Oracle8i, all tablespaces were created as dictionary-managed. Dictionary-managed tablespaces rely on data dictionary tables to track space utilization. Beginning with Oracle8i, you were able to create locally managed tablespaces, which use bitmaps (instead of data dictionary tables) to track used and free space. These locally managed tablespaces provide better performance and greater ease of management.

You can migrate one type of tablespace to another. But the benefit of creating locally managed tabelspace is not had, personally it is my experience, when a dictionary managed is converted to locally managed. And vice versa.

Beginning in Oracle9i the default for non-SYSTEM permanent tablespaces is locally managed whenever both of the following criteria are met:

The EXTENT MANAGEMENT clause is not specified
The COMPATIBLE initialization parameter is set to 9.0.0 or higher

Basing on the nature of the tablespaces we have 4 types of tablespaces.
(1) Locally Managed tablepsaces
(2) Dictionary Managed tablepsaces
(3) Temporary tablepsaces
(4) Undo tabelspace

Creating and managing a locally managed tablespace (Per Oracle Docs)

Locally managed tablespaces track all extent information in the tablespace itself, using bitmaps, resulting in the following benefits:

Improved concurrency and speed of space operations, because space allocations and de-allocations predominantly modify locally managed resources (bitmaps stored in header files) rather than requiring centrally managed resources such as enqueues.

Improved performance, because recursive operations that are sometimes required during dictionary-managed space allocation are eliminated.

Readable standby databases are allowed, because locally managed temporary tablespaces (used, for example, for sorts) are locally managed and thus do not generate any undo or redo.
Simplified space allocation–when the AUTOALLOCATE clause is specified, appropriate extent size is automatically selected

Reduced user reliance on the data dictionary because necessary information is stored in file headers and bitmap blocks

All tablespaces, including the SYSTEM tablespace, can be locally managed.

Additionally, the DBMS_SPACE_ADMIN package provides maintenance procedures for locally managed tablespaces.

To create a locally managed tablespace, specify LOCAL in the EXTENT MANAGEMENT clause of the CREATE TABLESPACE statement. You then have two options. You can have Oracle manage extents for you automatically with the AUTOALLOCATE option (the default), or you can specify that the tablespace is managed with uniform extents of a specific size (UNIFORM SIZE).

If the tablespace is expected to contain objects of varying sizes requiring different extent sizes and having many extents, then AUTOALLOCATE is the best choice. If it is not important to you to have a lot of control over space allocation and deallocation, AUTOALLOCATE presents a simplified way for you to manage a tablespace. Some space may be wasted but the benefit of having Oracle manage your space most likely outweighs this drawback.

On the other hand, if you want exact control over unused space, and you can predict exactly the space to be allocated for an object or objects and the number and size of extents, then UNIFORM is a good choice. This ensures that you will never have an unusable amount of space in your tablespace.

Note:
When you do not explicitly specify the type of extent management, and the default is to create a locally managed tablespace, Oracle determines extent management as described below.

If your CREATE TABLESPACE statement does not include a DEFAULT storage clause, then Oracle creates a locally managed auto allocated tablespace.

If your CREATE TABLESPACE statement does include a DEFAULT storage clause, then Oracle considers the following:

If you specified the MINIMUM EXTENT clause, Oracle evaluates whether the values of MINIMUM EXTENT, INITIAL, and NEXT are equal and the value of PCTINCREASE is 0. If so, Oracle creates a locally managed uniform tablespace with extent size = INITIAL. If the MINIMUM EXTENT, INITIAL, and NEXT parameters are not equal, or if PCTINCREASE is not 0, Oracle ignores any extent storage parameters you may specify and creates a locally managed, auto allocated tablespace.
If you did not specify MINIMUM EXTENT clause, Oracle evaluates only whether the storage values of INITIAL and NEXT are equal and PCTINCREASE is 0. If so, the tablespace is locally managed and uniform. Otherwise, the tablespace is locally managed and auto allocated.


The following statement creates a locally managed tablespace named lmtbsb and specifies AUTOALLOCATE:

CREATE TABLESPACE lmtbsb DATAFILE
‘/u02/oracle/data/lmtbsb01.dbf’
SIZE 50M
EXTENT MANAGEMENT LOCAL
AUTOALLOCATE;


AUTOALLOCATE causes the tablespace to be system managed with the smallest extent size being 64K. There is an increase in initial space allocated for objects in autoallocated tablespaces. This is because the objects have a minimum size of two blocks in dictionary-managed tablespaces, whereas in autoallocated locally managed tablespaces, the minimum object size is 64K.

Alternatively, this tablespace could be created specifying the UNIFORM clause. If UNIFORM SIZE is specified, then the tablespace is managed with uniform size extents of the specified SIZE. The default SIZE is 1M.

In the following example, a 128K extent size is specified. Each 128K extent (which, if the tablespace block size is 2K, is equivalent to 64 Oracle blocks) is represented by a bit in the extent bitmap for this file.

CREATE TABLESPACE lmtbsb DATAFILE ‘/u02/oracle/data/lmtbsb01.dbf’ SIZE 50M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K;


You cannot specify the DEFAULT storage clause, MINIMUM EXTENT, or TEMPORARY when you explicitly specify EXTENT MANAGEMENT LOCAL. If you want to create a temporary locally managed tablespace, use the CREATE TEMPORARY TABLESPACE statement.

Note:
When you allocate a datafile for a locally managed tablespace, you should allow space for metadata used for space management (the extent bitmap or space header segment) which are part of user space. For example, if you do not specify the SIZE parameter in the extent management clause when UNIFORM is specified, the default extent size is 1MB. Therefore, in this case, the size specified for the datafile must be larger (at least one block plus space for the bitmap) than 1MB.


Specifying Segment Space Management in Locally Managed Tablespaces

When you create a locally managed tablespace using the CREATE TABLESPACE statement, the SEGMENT SPACE MANAGEMENT clause allows you to specify how free and used space within a segment is to be managed. Your choices are:

MANUAL

Specifying MANUAL tells Oracle that you want to use free lists for managing free space within segments. Free lists are lists of data blocks that have space available for inserting rows. This form of managing space within segments is called manual segment-space management because of the need to specify and tune the PCTUSED, FREELISTS, and FREELISTS GROUPS storage parameters for schema objects created in the tablespace.

MANUAL is the default.

AUTO

This keyword tells Oracle that you want to use bitmaps to manage the free space within segments. A bitmap, in this case, is a map that describes the status of each data block within a segment with respect to the amount of space in the block available for inserting rows. As more or less space becomes available in a data block, its new state is reflected in the bitmap. Bitmaps allow Oracle to manage free space more automatically, and thus, this form of space management is called automatic segment-space management.

Automatic segment-space management is a simpler and more efficient way of managing space within a segment. It completely eliminates any need to specify and tune the PCTUSED, FREELISTS, and FREELISTS GROUPS storage parameters for schema objects created in the tablespace. If such attributes should be specified, they are ignored.

Automatic segment-space management delivers better space utilization than manual segment-space management, and it is self tuning in that it scales with increasing the number of users, as well as instances. For a Real Application Clusters environment, automatic segment-space management allows for a dynamic affinity of space to instances, thus avoiding the hard partitioning of space inherent with using free list groups.

For many standard workloads, application performance when using automatic segment space management is better than the performance of a well tuned application using manual segment-space management.

The following statement creates tablespace lmtbsb with automatic segment-space management:

CREATE TABLESPACE lmtbsb DATAFILE ‘/u02/oracle/data/lmtbsb01.dbf’ SIZE 50M
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO;


Your specification at tablespace creation time of your method for managing available space in segments, applies to all segments subsequently created in the tablespace. Also, your choice of method cannot be subsequently altered. Only permanent, locally managed tablespaces can specify automatic segment-space management.

Altering a Locally Managed Tablespace

You cannot alter a locally managed tablespace to a locally managed temporary tablespace, nor can you change its method of segment space management.

Some reasons for using the ALTER TABLESPACE statement for locally managed tablespaces include:

Adding a datafile. For example:
ALTER TABLESPACE lmtbsb
ADD DATAFILE ‘/u02/oracle/data/lmtbsb02.dbf’ SIZE 1M;

Coalescing free extents is unnecessary for locally managed tablespaces.