Segments Part 3

A table can be created with data or with no data or with partitions or with no partitions. The basic to determine what should be design of table you want to create depends on

(1) The nature of the data you want to store in that table.
(2) The nature of the activity on the table
(3) The sensitivity of the data to be stored in the table to determine the grants on the table
(4) De-normalized or Normalized data

Depending on the nature of the data stored in the tables we can have the following tables

Serial Number	Type of the Table	Description
01	Heap Table or Heap Organized Tables	This is a normal standard table to store data. The data is stored as a heap. The data after duly processed may be truncated. This is a kind of stage for processing data and inserting into other tables.
02	Index Organized Table	A table is stored in an index structure. This imposes physical order for the rows inserted basing on the defined primary key
03	Temporary table	This table is to store scratch data for the life of a transaction or a session only.
04	Clustered Tables	Related tables are grouped together to created clusters
05	Hash Clustered Tables	Hash Clusters cluster table data in a manner similar to normal, index clusters. To find or store a row in a hash cluster, Oracle applies a hash function to the row's cluster key value. The resulting hash value corresponds to a data block in the cluster.All rows with the same key value are stored together on disk.
06	Nested Tables	A nested table is a table stored within the structure of another table.
07	Object Tables	Oracle object types are user-defined types that make it possible to model real-world entities such as customers and purchase orders as objects in the database.

Depending upon the nature of the activity on those tables, they are defined as under:

Serial Number	Type of the Table	Description
01	Historic Tables	This data is mostly read only in nature. Does not demand any PCTFREE
02	Dynamic Tables	This data changes vary fast. Does demand High PCTFREE as the row length may change dynamically
03	Semi-Dynamic Tables	Data changes, but periodically. May require high PCT depending upon the nature of change to the data
04	Dimension Tables	These are dimensions like time
05	Hierarchy Tables	These are to list the hierarchies
06	Measure Tables	These are measures like units that are required for summaries
07	Summary Tables	Summarized data is stored here in these types of tables
08	Static Tables	They are also called Master Tables, which generally do not change. But may require periodical updates
09	Look Up Tables	These are for detailing a data element in a summary table and like
10	Temporary Tables	This data is purely temporary in nature.

It is good to have naming conventions for the tables. Other wise they tend to loose readability and understanding the design of the database. Oracle suggested so many conventions and the naming conventions can be observed by looking at Data Dictionary Objects.

STORAGE PARAMETERS	DEFINITIONS
INITIAL	This defines the initial extent to be acquired by Oracle to create the table in a given tablespace
NEXT	this defines the next extent to be acquired by oracle when one extent is populated by data
PCTINCREASE	This is to determine how the extent growth is to be for a given segment. 0 (Zero) enable to have even sized extents for the segment.
MAXEXTENTS	this defines the maximum extents that can be acquired for that table segment.
MINEXTENTS	This is to define the minimum extents to be acquired by Oracle to create that table segment.
PCTFREE	The extent in a segment is made up of Oracle Blocks as determined and defined in initialization parameter file. PCTFREE is to decide how much space in an Oracle Data Block is to be left to be free for future updates. The decision is dependent on the nature of the data you store in that table.
PCTUSED	this decides how much of space can be used in an Oracle data block.

Parameter	Description
INITIAL	The size, in bytes, of the first extent allocated when a segment is created. This parameter cannot be specified in an ALTER statement.
NEXT	The size, in bytes, of the next incremental extent to be allocated for a segment. The second extent is equal to the original setting for NEXT. From there forward, NEXT is set to the previous size of NEXT multiplied by (1 + PCTINCREASE/100).
PCTINCREASE	The percentage by which each incremental extent grows over the last incremental extent allocated for a segment. If PCTINCREASE is 0, then all incremental extents are the same size. If PCTINCREASE is greater than zero, then each time NEXT is calculated, it grows by PCTINCREASE. PCTINCREASE cannot be negative. The new NEXT equals 1 + PCTINCREASE/100, multiplied by the size of the last incremental extent (the old NEXT) and rounded up to the next multiple of a block size.
MINEXTENTS	The total number of extents to be allocated when the segment is created. This allows for a large allocation of space at creation time, even if contiguous space is not available.
MAXEXTENTS	The total number of extents, including the first, which can ever be allocated for the segment.
FREELIST GROUPS	The number of groups of free lists for the database object you are creating. Oracle uses the instance number of Oracle Real Application Cluster instances to map each instance to one free list group. .Note: This parameter is ignored for objects created in locally managed tablespaces with segment space management specified as AUTO.
FREELISTS	Specifies the number of free lists for each of the free list groups for the schema object. Not valid for tablespaces. Note: This parameter is ignored for objects created in locally managed tablespaces with segment space management specified as AUTO.
OPTIMAL	Relevant only to rollback segments (not AUM)
BUFFER POOL	Defines a default buffer pool (cache) for a schema object. Not valid for tablespaces or rollback segments

Oracle provides a means for suspending, and later resuming, the execution of large database operations in the event of space allocation failures. This enables you to take corrective action instead of the Oracle database server returning an error to the user. After the error condition is corrected, the suspended operation automatically resumes. This feature is called resumable space allocation. The statements that are affected are called resumable statements.

The following is an overview of how resumable statements work. Details are contained in later sections.

A statement executes in a resumable mode only when the client explicitly enables resumable semantics for the session using the ALTER SESSION statement.
A resumable statement is suspended when one of the following conditions occur (these conditions result in corresponding errors being signaled for non-resumable statements):
Out of space condition

Maximum extents reached condition
Space quota exceeded condition.
On suspending a resumable statement's execution, there are mechanisms to perform user-supplied operations, log errors, and to query the status of the statement execution. When a resumable statement is suspended the following actions are taken:

If the user registered a trigger on the AFTER SUSPEND system event, the user trigger is executed. A user supplied PL/SQL procedure can access the error message data using the DBMS_RESUMABLE package and DBA/USER_RESUMABLE view.

Suspending a statement automatically results in suspending the transaction. Thus all transactional resources are held through a statement suspend and resume.

When the error condition disappears (for example, as a result of user intervention or perhaps sort space released by other queries), the suspended statement automatically resumes execution.

A suspended statement can be forced to throw the exception using the DBMS_RESUMABLE.ABORT() procedure. This procedure can be called by a DBA, or by the user who issued the statement.

A suspension time out interval is associated with resumable statements. A resumable statement that is suspended for the timeout interval (the default is two hours) wakes up and returns the exception to the user.

A resumable statement can be suspended and resumed multiple times during execution.

Resumable space allocation is fully supported when using locally managed tablespaces. There are certain limitations when using dictionary-managed tablespaces

SELECT statements that run out of temporary space (for sort areas) are candidates for resumable execution. When using OCI, the calls LNOCIStmtExecute() and LNOCIStmtFetch() are candidates.

INSERT, UPDATE, and DELETE statements are candidates. The interface used to execute them does not matter; it can be OCI, JSQL, PL/SQL, or another interface. Also, INSERT INTO ... SELECT from external tables can be resumable.

As for SQL*Loader, a command line parameter controls whether statements are resumable after recoverable errors.

CREATE TABLE ... AS SELECT
CREATE INDEX
ALTER INDEX ... REBUILD
ALTER TABLE ... MOVE PARTITION
ALTER TABLE ... SPLIT PARTITION
ALTER INDEX ... REBUILD PARTITION
ALTER INDEX ... SPLIT PARTITION
CREATE MATERIALIZED VIEW
CREATE MATERIALIZED VIEW LOG

The operation cannot acquire any more extents for a table/index/temporary segment/rollback segment/undo segment/cluster/LOB/table partition/index partition in a tablespace. For example, the following errors fall in this category:

ORA-1650 unable to extend rollback segment ... in tablespace ...
ORA-1653 unable to extend table ... in tablespace ...
ORA-1654 unable to extend index ... in tablespace ...

The number of extents in a table/index/temporary segment/rollback segment/undo segment/cluster/LOB/table partition/index partition equals the maximum extents defined on the object. For example, the following errors fall in this category:

ORA-1628 max # extents ... reached for rollback segment ...
ORA-1631 max # extents ... reached in table ...
ORA-1654 max # extents ... reached in index ...

The user has exceeded his assigned space quota in the tablespace. Specifically, this is noted by the following error:

Resumable Space Allocation Limitations for Dictionary-Managed Tablespaces
There are certain limitations of resumable space allocation when using dictionary-managed tablespaces. These limitations are listed below:

If a DDL operation such as CREATE TABLE or CREATE INDEX is executed with an explicit MAXEXTENTS setting which causes an out of space error during its execution, the operation will not be suspended. Instead, it will be aborted. This error is treated as not repairable because the properties of an object (for example, MAXEXTENTS) cannot be altered before its creation. However if a DML operation causes an already existing table or index to reach the MAXEXTENTS limit, it will be suspended and can be resumed later. This restriction can be overcome either by setting the MAXEXTENTS clause to UNLIMITED or by using locally managed tablespaces.

If rollback segments are located in dictionary-managed tablespaces, then space allocation for rollback segments is not resumable. However, space allocation for user objects(tables, indexes, and the likes) would still be resumable. To workaround the limitation, we recommend using automatic undo management or placing the rollback segments in locally managed tablespaces.

In parallel execution, if one of the parallel execution server processes encounters a correctable error, that server process suspends its execution. Other parallel execution server processes will continue executing their respective tasks, until either they encounter an error or are blocked (directly or indirectly) by the suspended server process. When the correctable error is resolved, the suspended process resumes execution and the parallel operation continues execution. If the suspended operation is terminated, the parallel operation aborts, throwing the error to the user.

Different parallel execution server processes may encounter one or more correctable errors. This may result in firing an AFTER SUSPEND trigger multiple times, in parallel. Also, if a parallel execution server process encounters a non-correctable error while another parallel execution server process is suspended, the suspended statement is immediately aborted.

For parallel execution, every parallel execution coordinator and server process has its own entry in DBA/USER_RESUMABLE view.

Enabling and Disabling Resumable Space Allocation
Resumable space allocation is only possible when statements are executed within a session that has resumable mode enabled.

Because suspended statements can hold up some system resources, users must be granted the RESUMABLE system privilege before they are allowed to enable and execute resumable statements.

You can also specify a timeout interval, and you can provide a name used to identify a resumable statement. These are discussed separately in following sections.

When you enable resumable mode for a session, you can also specify a timeout interval, after which a suspended statement will error if no intervention has taken place. The following statement specifies that resumable transactions will time out and error after 3600 seconds:

The value of TIMEOUT remains in effect until it is changed by another ALTER SESSION ENABLE RESUMABLE statement, it is changed by another means, or the session ends. The default timeout interval is 7200 seconds.

Naming Resumable Statements
Resumable statements can be identified by name. The following statement assigns a name to resumable statements:

The NAME value remains in effect until it is changed by another ALTER SESSION ENABLE RESUMABLE statement, or the session ends. The default value for NAME is:

The name of the statement is used to identify the resumable statement in the DBA_RESUMABLE and USER_RESUMABLE views.

To set default resumable mode, a DBA can register a database level LOGON trigger to alter a user's session to enable resumable and set a timeout interval.

Changing the Timeout Interval
In addition to the ALTER SESSION ENABLE RESUMABLE statement, there are other methods for setting or changing the timeout interval.

The DBMS_RESUMABLE package contains procedures for setting the timeout period for a specific session or for the current session. A DBA can change the default system timeout by creating a system wide AFTER SUSPEND trigger that calls DBMS_RESUMABLE to set it. For example, the following code sample sets a system wide default timeout to one hour:

CREATE OR REPLACE TRIGGER resumable_default_timeout
AFTER SUSPEND
ON DATABASE
BEGIN
DBMS_RESUMABLE.SET_TIMEOUT(3600);
END;

Oracle first provided an opportunity to stripe the files across disks to create tablespaces to balance the IO.

From 8i onwards it allowed the users to create a segments to be partitioned and assigned to a tablespace that can have associated data files across the disks.

The partitioning is introduced to gain from better performance. If the segment is a large segment and the data to be accessed for a given query is very limited then we need to access that data only. To enable the user to access that piece of data, the partitioning concept works. Here the user has to identify the key for partitioning. The key should be the best key. Otherwise he cannot blame Oracle for no performance gains.

Partitioning can be done based on ranges, hash and list and composite partitioning with a mix where sub-partitioning is allowed.