|Oracle7 Server Administrator's Guide||
See Also: This chapter contains several references to Oracle Server Manager. For more information about performing specific tasks using Server Manager/GUI or Server Manager/LineMode, see the Oracle Server Manager User's Guide.
This indicates that 20% of each data block used for this table's data segment will be kept free and available for possible updates to the existing rows already within each block. Figure 10 - 1 illustrates PCTFREE.
Figure 10 - 1. PCTFREE
Notice that before the block reaches PCTFREE, the free space of the data block is filled by both the insertion of new rows and by the growth of the data block header.
A smaller PCTFREE has the following effects:
A larger PCTFREE has the following effects:
Ensure that you understand the nature of the table or index data before setting PCTFREE. Updates can cause rows to grow. New values might not be the same size as values they replace. If there are many updates in which data values get larger, PCTFREE should be increased. If updates to rows do not affect the total row width, PCTFREE can be low. Your goal is to find a satisfactory tradeoff between densely packed data and good update performance.
PCTFREE for Non-Clustered Tables If the data in the rows of a non-clustered table is likely to increase in size over time, reserve some space for these updates. Otherwise, updated rows are likely to be chained among blocks.
PCTFREE for Clustered Tables The discussion for non-clustered tables also applies to clustered tables. However, if PCTFREE is reached, new rows from any table contained in the same cluster key go into a new data block that is chained to the existing cluster key.
PCTFREE for Indexes You can specify PCTFREE only when initially creating an index.
In this case, a data block used for this table's data segment is not considered for the insertion of any new rows until the amount of used space in the block falls to 39% or less (assuming that the block's used space has previously reached PCTFREE). Figure 10 - 2 illustrates this.
Figure 10 - 2. PCTUSED
The default for PCTUSED is 40 percent. You may specify any integer between 0 and 99, inclusive, as long as the sum of PCTUSED and PCTFREE does not exceed 100.
A smaller PCTUSED has the following effects:
Scenario: Common activity includes UPDATE statements that increase the size of the rows.
PCTFREE = 20 PCTUSED = 40
PCTFREE is set to 20 to allow enough room for rows that increase in size as a result of updates. PCTUSED is set to 40 so that less processing is done during high update activity, thus improving performance.
Scenario: Most activity includes INSERT and DELETE statements, and UPDATE statements that do not increase the size of affected rows.
PCTFREE = 5 PCTUSED = 60
PCTFREE is set to 5 because most UPDATE statements do not increase row sizes. PCTUSED is set to 60 so that space freed by DELETE statements is used soon, yet processing is minimized.
Scenario: The table is very large; therefore, storage is a primary concern. Most activity includes read-only transactions.
PCTFREE = 5 PCTUSED = 90
PCTFREE is set to 5 because this is a large table and you want to completely fill each block.
The size, in bytes, of the first extent allocated when a segment is created.
Default: 5 data blocks Minimum: 2 data blocks (rounded up) Maximum: operating system-specific
Although the default system value is given in data blocks, use bytes to set a value for this parameter. You can use the abbreviations K and M to indicate kilobytes and megabytes. Anything less than 2 data blocks is rounded up to the next multiple of the data block size, as determined by the parameter DB_BLOCK_SIZE.
For example, if the data block size of a database is 2048 bytes, then the system default for the INITIAL storage parameter of tablespaces is 10240 bytes. If you create a tablespace in this database and specify its default storage parameter INITIAL as 20000 (bytes), Oracle automatically rounds this value up to 20480 (10 data blocks).
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).
Default: 5 data blocks Minimum: 1 data block Maximum: operating system-specific
As with INITIAL, although the default system value is given in data blocks, use bytes to set a value for this parameter. You can use the abbreviations K and M to indicate kilobytes and megabytes. The value is rounded up to the next multiple of the data block size, as determined by the parameter DB_BLOCK_SIZE.
The total number of extents, including the first, that can ever be allocated for the segment.
Default: dependent on the data block size and operating system Minimum: 1 (extent) Maximum: unlimited
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.
Default: 1 (extent) Minimum: 1 (extent) Maximum: operating system-specific
If MINEXTENTS is greater than 1, then the specified number of incremental extents are allocated at creation time using the values INITIAL, NEXT, and PCTINCREASE.
Note: The default and minimum values of MINEXTENTS for a rollback segment are always 2. If you want to guarantee that you have enough space to load all the data for one table, create the table with a large MINEXTENTS value so that the LOAD operation is successful even if your database is fragmented.
The percent 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.
Default: 50 (%) Minimum: 0 (%) Maximum: operating system-specific
Note: PCTINCREASE is always 0 for rollback segments. PCTINCREASE cannot be specified for rollback segments.
By using PCTINCREASE correctly, you can reduce the fragmentation of a segment by enlarging incremental extents and reducing the number of extents that need to be allocated for the segment. The segment contains a few large extents, rather than many smaller extents.
If you change PCTINCREASE for a segment, the current value of NEXT for that segment does not change. Only future values of NEXT are affected.
Reserves a pre-allocated amount of space for an initial number of transaction entries to access rows in the data block concurrently. Space is reserved in the headers of all data blocks in the associated data or index segment. The default value is 1 for tables and 2 for clusters and indexes.
As multiple transactions concurrently access the rows of the same data block, space is allocated for each transaction's entry in the block. Once the space reserved by INITRANS is depleted, space for additional transaction entries is allocated out of the free space in a block, if available. Once allocated, this space effectively becomes a permanent part of the block header. The MAXTRANS parameter limits the number of transaction entries that can concurrently use data in a data block. Therefore, you can limit the amount of free space that can be allocated for transaction entries in a data block using MAXTRANS. The default value is an operating system-specific function of block size, not exceeding 255.
If MAXTRANS is too low, transactions blocked by this limit must wait until other transactions complete and free transaction entry space. For example, if MAXTRANS is 3 and a fourth concurrent transaction attempts to access a block already being accessed by three active transactions, Oracle selects one of the three and waits until it commits or rolls back, and then proceeds with the fourth transaction.
See Also: Some defaults are operating system specific; see your operating system-specific Oracle documentation.
Alternatively, assume that a table is usually accessed by many users at the same time. In this case, you might consider pre-allocating transaction entry space by using a high INITRANS (to eliminate the overhead of having to allocate transaction entry space, as required when the object is in use) and allowing a higher MAXTRANS so that no user has to wait to access any necessary data blocks.
In contrast, you set the storage parameters for the data segments of a cluster using the STORAGE clause of the CREATE CLUSTER or ALTER CLUSTER command, rather than the individual CREATE or ALTER commands that put tables and snapshots into the cluster. Storage parameters specified when creating or altering a clustered table or snapshot are ignored. The storage parameters set for the cluster override the table's storage parameters.
A PCTFREE setting for an index only has an effect when the index is created. You cannot specify PCTUSED for an index segment.
The INITIAL and MINEXTENTS storage parameters cannot be altered for an existing table, cluster, index, or rollback segment. If only NEXT is altered for a segment, the next incremental extent is the size of the new NEXT, and subsequent extents can grow by PCTINCREASE as usual.
If both NEXT and PCTINCREASE are altered for a segment, the next extent is the new value of NEXT, and from that point forward, NEXT is calculated using PCTINCREASE as usual.
Note: The storage parameters for temporary segments always use the default storage parameters set for the associated tablespace.
CREATE TABLE test_storage
( . . . )
STORAGE (INITIAL 100K NEXT 100K
MINEXTENTS 2 MAXEXTENTS 5
Also assume that the initialization parameter DB_BLOCK_SIZE is set to 2K. The following table shows how extents are allocated for the TEST_STORAGE table. Also shown is the value for the incremental extent, as can be seen in the NEXT column of the USER_SEGMENTS or DBA_SEGMENTS data dictionary views:
|Extent#||Extent Size||Value for NEXT|
|1||100K or 50 blocks||100K|
|2||100K or 50 blocks||CEIL(100K*1.5)=150K|
|3||150K or 75 blocks||CEIL(150K*1.5)=228K|
|4||228K or 114 blocks||CEIL(228K*1.5)=342K|
|5||342K or 171 blocks||CEIL(342K*1.5)=516K|
|Table 10 - 1. Extent Allocations|
ALTER TABLE test_storage STORAGE (NEXT 500K);
As a result, the third extent is 500K when allocated, the fourth is (500K*1.5)=750K, and so on.
Within a segment, the high water mark indicates the amount of used space. You cannot release space below the high water mark (even if there is no data in the space you wish to deallocate). However, if the segment is completely empty, you can release space using the TRUNCATE DROP STORAGE statement.
ALTER TABLE table DEALLOCATE UNUSED KEEP integer;
ALTER INDEX index DEALLOCATE UNUSED KEEP integer;
ALTER CLUSTER cluster DEALLOCATE UNUSED KEEP integer;
When you explicitly identify an amount of unused space to KEEP, this space is retained while the remaining unused space is deallocated. If the remaining number of extents becomes smaller than MINEXTENTS, the MINEXTENTS value changes to reflect the new number. If the initial extent becomes smaller, the INITIAL value changes to reflect the new size of the initial extent.
If you do not specify the KEEP clause, all unused space (everything above the high water mark) is deallocated, as long as the size of the initial extent and MINEXTENTS are preserved. Thus, even if the high water mark occurs within the MINEXTENTS boundary, MINEXTENTS remains and the initial extent size is not reduced.
See Also: For details on the syntax and options associated with deallocating unused space, see the Oracle7 Server SQL Reference.
You can verify that deallocated space is freed by looking at the DBA_FREE_SPACE view. For more information on this view, see the Oracle7 Server Reference.
For details about the DBMS_SPACE package, see page 16 - 25.
Table dquon consists of three extents (see figure Figure 10 - 3). The first extent is 10K, the second is 20K, and the third is 30K. The high water mark is in the middle of the second extent, and there is 40K of unused space. The following statement deallocates all unused space, leaving table dquon with two remaining extents. The third extent disappears, and the second extent size is 10K.
ALTER TABLE dquon DEALLOCATE UNUSED;
Figure 10 - 3. Deallocating All Unused Space
If you deallocate all unused space from dquon and KEEP 10K (see Figure 10 - 4), the third extent is deallocated and the second extent remains in tact.
Figure 10 - 4. Deallocating Unused Space, KEEP 10K
If you deallocate all unused space from dquon and KEEP 20K, the third extent is cut to 10K, and the size of the second extent remains the same.
ALTER TABLE dquon DEALLOCATE UNUSED KEEP 20K;
When you issue the ALTER TABLE dquon DEALLOCATE UNUSED statement, you completely deallocate the third extent, and the second extent is left with 10K. Note that the size of the next allocated extent defaults to the size of the last completely deallocated extent, which in this example, is 30K. However, if you can explicitly set the size of the next extent using the ALTER ... STORAGE [NEXT] statement.
To preserve the MINEXTENTS number of extents, DEALLOCATE can retain extents that were originally allocated to an instance (added below the high water mark), while deallocating extents that were originally allocated to the segment.
For example, table dquon has a MINEXTENTS value of 2. Examples 1 and 2 still yield the same results. However, if the MINEXTENTS value is 3, then the ALTER TABLE dquon DEALLOCATE UNUSED statement has no effect, while the ALTER TABLE dquon DEALLOCATE UNUSED KEEP 10K statement removes the third extent and changes the value of MINEXTENTS to 2.
The CHAR and VARCHAR2 datatypes store alphanumeric data in strings of ASCII (American Standard Code for Information Interchange) or EBCDIC (Extended Binary Coded Decimal Interchange Code) values, depending on the character set used by the hardware that runs Oracle. Character datatypes can also store data using character sets supported by the National Language Support (NLS) feature of Oracle.
The CHAR datatype stores fixed length character strings. When a table is created with a CHAR column, a column length (in bytes, not characters) between 1 and 255 can be specified for the CHAR column; the default is 1 byte. Extra blanks are used to fill remaining space in the column for values less than the column length.
The VARCHAR2 datatype stores variable length character strings. When a table is created with a VARCHAR2 column, a maximum column length (in bytes, not characters) between 1 and 2000 is specified for the VARCHAR2 column. For each row, each value in the column is stored as a variable length field. Extra blanks are not used to fill remaining space in the column.
The NUMBER datatype stores fixed and floating point numbers. Positive numbers in the range 1 x 10^-130 to 9.99...9 x 10^125 (with up to 38 significant digits), negative numbers in the range
-1 x 10^-130 to -9.99..9 x 10^125 (with up to 38 significant digits), and zero. You can optionally specify a
precision (total number of digits) and scale (number of digits to the right of the decimal point) when defining a NUMBER column. If precision is not specified, the column stores values as given. If no scale is specified, the scale defaults to zero.
Oracle guarantees portability of numbers with a precision equal to or less than 38 digits. You can specify a scale and no precision:
column_name NUMBER (*, scale)
In this case, the precision is 38 and the specified scale is maintained.
The DATE datatype stores point-in-time values, such as dates and times. Date data is stored in fixed length fields of seven bytes each.
Columns defined as LONG store variable length character data containing up to two gigabytes of information. LONG data is text data, and is appropriately converted when moved between different character sets. LONG data cannot be indexed.
RAW and LONG RAW Datatypes
RAW is a variable length datatype like the VARCHAR2 character datatype, except that SQL*Net (which connects users sessions to the instance) and the Import and Export utilities do not perform character conversion when transmitting RAW or LONG RAW data. In contrast, SQL*Net and Export/Import automatically convert CHAR, VARCHAR2, and LONG data between the database character set and the user session character set (set by the NLS_LANGUAGE parameter of the ALTER SESSION command) if the two character sets are different.
LONG RAW data cannot be indexed, while RAW data can be indexed.
ROWIDs and the ROWID Datatype
Every row in a non-clustered table of an Oracle database is assigned a unique ROWID that corresponds to the physical address of a row's row piece (or the initial row piece if the row is chained among multiple row pieces).
Each table in an Oracle database internally has a pseudo-column named ROWID. This pseudo-column is not evident when listing the structure of a table by executing a SELECT statement, or a DESCRIBE statement using SQL*Plus, but can be retrieved with a SQL query using the reserved word ROWID as a column name.
ROWIDs use a binary representation of the physical address for each row selected. A ROWID's VARCHAR2 hexadecimal representation is divided into three pieces: block.slot.file. Here, block is the data block within a file that contains the row, relative to its datafile; row is the row in the block; and file is the datafile that contains the row. A row's assigned ROWID remains unchanged usually. Exceptions occur when the row is exported and imported (using the Import and Export utilities). When a row is deleted from a table (and the encompassing transaction is committed), the deleted row's associated ROWID can be assigned to a row inserted in a subsequent transaction.
Trusted Oracle7 provides one special datatype, called MLSLABEL. You can declare columns of this datatype in standard Oracle, as well as Trusted Oracle7, for compatibility with Trusted Oracle7 applications.
The MLSLABEL datatype stores a variable length tag (two to five bytes) that represents a binary label in the data dictionary. The ALL_LABELS data dictionary view lists all of the labels ever stored in the database.
See Also: For more information about NLS and support for different character sets, see the Oracle7 Server Reference.
For more information about MLSLABEL datatypes, see the Trusted Oracle7 Server Administrator's Guide.
|Datatype||Description||Column Length (bytes)|
|CHAR (size)||Fixed length character data of length size.||Fixed for every row in the table (with trailing spaces); maximum size is 255 bytes per row, default size is one byte per row. Consider the character set that is used before setting size. (Are you using a one or two byte character set?)|
|VARCHAR2 (size)||Variable length character data. A maximum size must be specified.||Variable for each row, up to 2000 bytes per row. Consider the character set that is used before setting size. (Are you using a one or two byte character set?)|
|NUMBER (p, s)||Variable length numeric data. Maximum precision p and/or scale s is 38.||Variable for each row. The maximum space required for a given column is 21 bytes per row.|
|DATE||Fixed length date and time data, ranging from January 1, 4712 B.C. to December 31, 4712 A.D. Default format: DD-MON-YY.||Fixed at seven bytes for each row in the table.|
|LONG||Variable length character data.||Variable for each row in the table up to 2^31 bytes, or two gigabytes, per row.|
|RAW (size)||Variable length raw binary data. A maximum size must be specified.||Variable for each row in the table, up to 255 bytes per row.|
|LONG RAW||Variable length raw binary data.||Variable for each row in the table, up to 2^31 bytes, or two gigabytes, per row.|
|ROWID||Binary data representing row addresses.||Fixed at six bytes for each row in the table.|
|MLSLABEL||Variable length binary data representing OS labels.||Variable for each row in the table, ranging from two to five bytes per row.|
Copyright © 1996 Oracle Corporation.
All Rights Reserved.