SEGMENTS PART I

 Each tablespace hosts segments. There are many types of segments.

CACHE
CLUSTER
INDEX
INDEX PARTITION
INDEX SUBPARTITION
LOBINDEX
LOBSEGMENT
NESTED TABLE
ROLLBACK
TABLE
TABLE PARTITION
TABLE SUBPARTITION
TEMPORARY
TYPE2 UNDO

All the above segment types are the logical structures next to tablespaces within Oracle Database and are associated with a tablespace and can not live independently. A DBA plans/ designs tablespaces of different types basing on the nature of the contents of the segment.

CACHE

Meatalink ID number 107709.1 explains what is CACHE segment and what are DO NOTs for you and me.

When the database is created, a segment of type 'CACHE' is created. It is owned by 'SYS' and resides in the system tablespace. It can been found by querying DBA_SEGMENTS or USER_SEGMENTS for a segment_type of 'CACHE' and has one extent allocated but MAX_EXTENTS = 0.

The name of the segment will be 1. which is derived from the values of HEADER_FILE.HEADER_BLOCK in DBA_SEGMENTS. This file and block marks the beginning of non-bootstrap data dictionary objects. All objects which are created prior to bootstrap$ in SQL.BSQ are required for warm starting the instance. These objects can only be created from CREATE DATABASE.

The CACHE object SYS.1. marks the starting block where non-bootstrap objects will reside. For example, SYS.1.527, segment_type='CACHE' is located in HEADER_FILE 1, HEADER_BLOCK 527

The data dictionary elements created prior to bootstrap$ are absolutely necessary for database operations since all other objects are built from them (fet$, uet$, obj$, tab$, c_ts#, etc...). The database will not open if these objects do not exist or are corrupted (ORA-704).

It is an unsupported action to modify data dictionary objects.

CLUSTER
(From Oracle Docs)

A cluster provides an optional method of storing table data. A cluster is made up of a group of tables that share the same data blocks. The tables are grouped together because they share common columns and are often used together. For example, the emp and dept table share the deptno column. When you cluster the emp and dept tables (see Figure 18-1), Oracle physically stores all rows for each department from both the emp and dept tables in the same data blocks.

Because clusters store related rows of different tables together in the same data blocks, properly used clusters offer two primary benefits:

Disk I/O is reduced and access time improves for joins of clustered tables.

The cluster key is the column, or group of columns, that the clustered tables have in common. You specify the columns of the cluster key when creating the cluster. You subsequently specify the same columns when creating every table added to the cluster. Each cluster key value is stored only once each in the cluster and the cluster index, no matter how many rows of different tables contain the value.

Therefore, less storage might be required to store related table and index data in a cluster than is necessary in non-clustered table format.

How to create CLUSTERS?
(From Oracle Docs)

CREATE CLUSTER emp_dept (deptno NUMBER(3))
PCTUSED 80
PCTFREE 5
SIZE 600
TABLESPACE users
STORAGE (INITIAL 200K
NEXT 300K
MINEXTENTS 2
MAXEXTENTS 20
PCTINCREASE 33);
If no INDEX keyword is specified, as is true in this example, an index cluster is created by default. You can also create a HASH cluster, when hash parameters (HASHKEYS, HASH IS, or SINGLE TABLE HASHKEYS) are specified

To create a table in a cluster, you must have either the CREATE TABLE or CREATE ANY TABLE system privilege. You do not need a tablespace quota or the UNLIMITED TABLESPACE system privilege to create a table in a cluster.

You create a table in a cluster using the CREATE TABLE statement with the CLUSTER option. The emp and dept tables can be created in the emp_dept cluster using the following statements:

CREATE TABLE emp (
empno NUMBER(5) PRIMARY KEY,
ename VARCHAR2(15) NOT NULL,
. . .
deptno NUMBER(3) REFERENCES dept)
CLUSTER emp_dept (deptno);

CREATE TABLE dept (
deptno NUMBER(3) PRIMARY KEY, . . . )
CLUSTER emp_dept (deptno);

You can specify the schema for a clustered table in the CREATE TABLE statement. A clustered table can be in a different schema than the schema containing the cluster. Also, the names of the columns are not required to match, but their structure must match.

A cluster index must be created before any rows can be inserted into any clustered table. The following statement creates a cluster index for the emp_dept cluster:

CREATE INDEX emp_dept_indexON CLUSTER emp_dept
INITRANS 2 MAXTRANS 5 TABLESPACE users STORAGE
(INITIAL 50K NEXT 50K MINEXTENTS 2 MAXEXTENTS 10 PCTINCREASE 33)
PCTFREE 5;

The cluster index clause (ON CLUSTER) identifies the cluster, emp_dept, for which the cluster index is being created. The statement also explicitly specifies several storage settings for the cluster and cluster index.

To alter a cluster, your schema must contain the cluster or you must have the ALTER ANY CLUSTER system privilege. You can alter an existing cluster to change the following settings:

Physical attributes (PCTFREE, PCTUSED, INITRANS, MAXTRANS, and storage characteristics)
The average amount of space required to store all the rows for a cluster key value (SIZE)
The default degree of parallelism
Additionally, you can explicitly allocate a new extent for the cluster, or de-allocate any unused extents at the end of the cluster. Oracle dynamically allocates additional extents for the data segment of a cluster as required. In some circumstances, however, you might want to explicitly allocate an additional extent for a cluster. For example, when using Oracle9i Real Application Clusters, you can allocate an extent of a cluster explicitly for a specific instance. You allocate a new extent for a cluster using the ALTER CLUSTER statement with the ALLOCATE EXTENT clause.

When you alter data block space usage parameters (PCTFREE and PCTUSED) or the cluster size parameter (SIZE) of a cluster, the new settings apply to all data blocks used by the cluster, including blocks already allocated and blocks subsequently allocated for the cluster. Blocks already allocated for the table are reorganized when necessary (not immediately).

When you alter the transaction entry settings (INITRANS and MAXTRANS) of a cluster, a new setting for INITRANS applies only to data blocks subsequently allocated for the cluster, while a new setting for MAXTRANS applies to all blocks (already and subsequently allocated blocks) of a cluster.

The storage parameters INITIAL and MINEXTENTS cannot be altered. All new settings for the other storage parameters affect only extents subsequently allocated for the cluster.

To alter a cluster, use the ALTER CLUSTER statement. The following statement alters the emp_dept cluster:

ALTER CLUSTER emp_dept
PCTFREE 30
PCTUSED 60;