12
Guidelines for Managing Schema Objects

This chapter describes guidelines for managing schema objects, and includes the following topics:

You should familiarize yourself with the concepts in this chapter before attempting to manage specific schema objects as described in Chapters 13-18.

Managing Space in Data Blocks

This section describes the various aspects of managing space in data blocks, and includes the following topics:

You can use the PCTFREE and PCTUSED parameters to make the following changes:

increase the performance of writing and retrieving data
decrease the amount of unused space in data blocks
decrease the amount of row chaining between data blocks

The PCTFREE Parameter

The PCTFREE parameter is used to set the percentage of a block to be reserved for possible updates to rows that already are contained in that block. For example, assume that you specify the following parameter within a CREATE TABLE statement:

PCTFREE 20

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 12-1 illustrates PCTFREE.

Figure 12-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.

Specifying PCTFREE

The default for PCTFREE is 10 percent. You can use any integer between 0 and 99, inclusive, as long as the sum of PCTFREE and PCTUSED does not exceed 100.

A smaller PCTFREE has the following effects:

reserves less room for updates to expand existing table rows
allows inserts to fill the block more completely
may save space, because the total data for a table or index is stored in fewer blocks (more rows or entries per block)

A small PCTFREE might be suitable, for example, for a segment that is rarely changed.

A larger PCTFREE has the following effects:

reserves more room for future updates to existing table rows
may require more blocks for the same amount of inserted data (inserting fewer rows per block)
may improve update performance, because Oracle does not need to chain row pieces as frequently, if ever

A large PCTFREE is suitable, for example, for segments that are frequently updated.

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 trade-off between densely packed data and good update performance.

PCTFREE for NonClustered Tables If the data in the rows of a nonclustered 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 nonclustered 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.

The PCTUSED Parameter

After a data block becomes full as determined by PCTFREE, Oracle does not consider the block for the insertion of new rows until the percentage of the block being used falls below the parameter PCTUSED. Before this value is achieved, Oracle uses the free space of the data block only for updates to rows already contained in the data block. For example, assume that you specify the following parameter within a CREATE TABLE statement:

PCTUSED 40

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 12-2 illustrates this.

Figure 12-2 PCTUSED

Specifying PCTUSED

The default value for PCTUSED is 40 percent. After the free space in a data block reaches PCTFREE, no new rows are inserted in that block until the percentage of space used falls below PCTUSED. The percent value is for the block space available for data after overhead is subtracted from total space.

You can specify any integer between 0 and 99 (inclusive) for PCTUSED, as long as the sum of PCTUSED and PCTFREE does not exceed 100.

A smaller PCTUSED has the following effects:

reduces processing costs incurred during UPDATE and DELETE statements for moving a block to the free list when it has fallen below that percentage of usage
increases the unused space in a database

A larger PCTUSED has the following effects:

improves space efficiency
increases processing cost during INSERTs and UPDATEs

Selecting Associated PCTUSED and PCTFREE Values

If you decide not to use the default values for PCTFREE or PCTUSED, keep the following guidelines in mind:

The sum of PCTFREE and PCTUSED must be equal to or less than 100.
If the sum equals 100, then Oracle attempts to keep no more than PCTFREE free space, and processing costs are highest.
Block overhead is not included in the computation of PCTUSED or PCTFREE.
The smaller the difference between 100 and the sum of PCTFREE and PCTUSED (as in PCTUSED of 75, PCTFREE of 20), the more efficient space usage is, at some performance cost.

Examples of Choosing PCTFREE and PCTUSED Values

The following examples show how and why specific values for PCTFREE and PCTUSED are specified for tables.

Example 1

Scenario:

Common activity includes UPDATE statements that increase the size of the rows.

Settings:

PCTFREE = 20
PCTUSED = 40

Example 2

Scenario:

Most activity includes INSERT and DELETE statements, and UPDATE statements that do not increase the size of affected rows.

Settings:

PCTFREE = 5
PCTUSED = 60

Explanation:

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.

Example 3

Scenario:

The table is very large; therefore,
storage is a primary concern. Most activity includes read-only transactions.

Settings:

PCTFREE = 5
PCTUSED = 40

Explanation:

PCTFREE is set to 5 because this is a large table and you want to completely fill each block.

Setting Storage Parameters

This section describes the storage parameters you can set for various data structures, and includes the following topics:

You can set storage parameters for the following types of logical storage structures:

tablespaces (most defaults for any segment in the tablespace)
tables, clusters, snapshots, and snapshot logs (data segments)
indexes (index segments)
rollback segments

Storage Parameters You Can Specify

Every database has default values for storage parameters. You can specify defaults for a tablespace, which override the system defaults to become the defaults for objects created in that tablespace only. Furthermore, you can specify storage settings for each individual object. The storage parameters you can set are:

INITIAL

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

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

MAXEXTENTS

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

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.

Default: 1 (extent)
Minimum: 1 (extent)
Maximum: unlimited

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.

Default: 50 (%)
Minimum: 0 (%)
Maximum: operating system specific

INITRANS

Specifies the number of DML transaction entries for which space should be initially reserved in the data block header. 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.

MAXTRANS

As multiple transactions concurrently access the rows of the same data block, space is allocated for each DML 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.

See Also: For specific details about storage parameters, see the Oracle8i SQL Reference.

Some defaults are operating system specific; see your operating system-specific Oracle documentation.

Setting INITRANS and MAXTRANS

Transaction entry settings for the data blocks allocated for a table, cluster, or index should be set individually for each object based on the following criteria:

the space you would like to reserve for transaction entries compared to the space you would reserve for database data
the number of concurrent transactions that are likely to touch the same data blocks at any given time

For example, if a table is very large and only a small number of users simultaneously access the table, the chances of multiple concurrent transactions requiring access to the same data block is low. Therefore, INITRANS can be set low, especially if space is at a premium in the database.

Alternatively, assume that a table is usually accessed by many users at the same time. In this case, you might consider preallocating 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 necessary data blocks.

Setting Default Storage Parameters for Segments in a Tablespace

You can set default storage parameters for each tablespace of a database. Any storage parameter that you do not explicitly set when creating or subsequently altering a segment in a tablespace automatically is set to the corresponding default storage parameter for the tablespace in which the segment resides.

With partitioned tables, you can set default storage parameters at the table level. When creating a new partition of the table, the default storage parameters are inherited from the partitioned table (unless you specify them for the individual partition). If no storage parameters are specified for the partitioned table, then they are inherited from the tablespace.

When specifying MINEXTENTS at the tablespace level, any extent allocated in the tablespace is rounded to a multiple of the number of minimum extents. Basically, the number of extents is a multiple of the number of blocks.

Setting Storage Parameters for Data Segments

You can set the storage parameters for the data segment of a nonclustered table, snapshot, or snapshot log using the STORAGE clause of the CREATE or ALTER statement for tables, snapshots, or snapshot logs.

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.

Setting Storage Parameters for Index Segments

Storage parameters for an index segment created for a table index can be set using the STORAGE clause of the CREATE INDEX or ALTER INDEX command. Storage parameters of an index segment created for the index used to enforce a primary key or unique key constraint can be set in the ENABLE clause of the CREATE TABLE or ALTER TABLE commands or the STORAGE clause of the ALTER INDEX command.

A PCTFREE setting for an index only has an effect when the index is created. You cannot specify PCTUSED for an index segment.

Setting Storage Parameters for LOB Segments

You can set storage parameters for LOB segments using the NOCACHE, NOLOGGING and PCTVERSION LOB storage parameters of the CREATE TABLE statement.

See Also: For a complete list of storage parameters for LOB segments, see the Oracle8i SQL Reference.

Changing Values for Storage Parameters

You can alter default storage parameters for tablespaces and specific storage parameters for individual segments if the current settings are incorrect. All default storage parameters can be reset for a tablespace. However, changes affect only new objects created in the tablespace, or new extents allocated for a 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.

Understanding Precedence in Storage Parameters

The storage parameters in effect at a given time are determined by the following types of SQL statements, listed in order of precedence:

ALTER TABLE/CLUSTER/SNAPSHOT/SNAPSHOT LOG/INDEX/ROLLBACK SEGMENT statement
CREATE TABLE/CLUSTER/SNAPSHOT/SNAPSHOT LOG/CREATE INDEX/ROLLBACK SEGMENT statement
ALTER TABLESPACE statement
CREATE TABLESPACE statement
Oracle default values

Any storage parameter specified at the object level overrides the corresponding option set at the tablespace level. When storage parameters are not explicitly set at the object level, they default to those at the tablespace level. When storage parameters are not set at the tablespace level, Oracle system defaults apply. If storage parameters are altered, the new options apply only to the extents not yet allocated.

Note:
The storage parameters for temporary segments always use the default storage parameters set for the associated tablespace.

Storage Parameter Example

Assume the following statement has been executed:

CREATE TABLE test_storage
   ( . . . )
   STORAGE (INITIAL 100K   NEXT 100K
      MINEXTENTS 2   MAXEXTENTS 5
      PCTINCREASE 50);

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:

Table 12-1 Extent Allocations

Extent#	Extent Size	Value for NEXT
1	50 blocks or 102400 bytes	50 blocks or 102400 bytes
2	50 blocks or 102400 bytes	75 blocks or153600 bytes
3	75 blocks or 153600 bytes	113 blocks or 231424 bytes
4	115 blocks or 235520 bytes	170 blocks or 348160 bytes
5	170 blocks or 348160 bytes	255 blocks or 522240 bytes

If you change the NEXT or PCTINCREASE storage parameters with an ALTER statement (such as ALTER TABLE), the specified value replaces the current value stored in the data dictionary. For example, the following statement modifies the NEXT storage parameter of the TEST_STORAGE table before the third extent is allocated for the table:

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.

Deallocating Space

This section describes aspects of deallocating unused space, and includes the following topics:

It is not uncommon to allocate space to a segment, only to find out later that it is not being used. For example, you may set PCTINCREASE to a high value, which could create a large extent that is only partially used. Or you could explicitly overallocate space by issuing the ALTER TABLE...ALLOCATE EXTENT statement. If you find that you have unused or overallocated space, you can release it so that the unused space can be used by other segments.

Viewing the High Water Mark

Prior to deallocation, you can use the DBMS_SPACE package, which contains a procedure (UNUSED_SPACE) that returns information about the position of the high water mark and the amount of unused space in a segment.

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.

Issuing Space Deallocation Statements

The following statements deallocate unused space in a segment (table, index or cluster). The KEEP clause is optional.

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 Oracle8i 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 Oracle8i Reference.

Deallocating Space: Examples

This section includes various space deallocation scenarios. Prior to reading it, you should familiarize yourself with the ALTER...DEALLOCATE UNUSED statements in the Oracle8i Reference.

Example 1

Table DQUON consists of three extents (see Figure 12-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 12-3 Deallocating All Unused Space

If you deallocate all unused space from DQUON and KEEP 10K (see Figure 12-4), the third extent is deallocated and the second extent remains in tact.

Figure 12-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;

Example 2

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.

Example 3

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.

Understanding Space Use of Datatypes

When creating tables and other data structures, you need to know how much space they will require. Each datatype has different space requirements, as described below.

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 4000 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.

Number
Datatype

The NUMBER datatype stores fixed and floating point numbers. Positive numbers in the range 1 x 10^-130 to 9.99...9 x 10¹²⁵ (with up to 38 significant digits), negative numbers in the range

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. Oracle guarantees portability of numbers with a precision equal to or less than 38 digits. You can specify a scale and no precision:

-1 x 10^-130 to -9.99..9x 10¹²⁵ (with up to 38 significant digits), and zero. If precision is not specified, the column stores values as given. If no scale and no precision are specified:

column_name NUMBER (*, scale)

In this case, the precision is 38 and the specified scale is maintained.

DATE Datatype

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.

LONG Datatype

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.
Note: You can convert LONG data to LOB data, which has fewer restrictions. For more information see the Oracle8i Application Developer's Guide - Large Objects (LOBs).

RAW data can be indexed; LONG RAW data cannot be indexed.

rowids, ROWID pseudocolumn and ROWID datatype

Every row in a nonclustered 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 has an internal pseudo-column named ROWID. This pseudocolumn 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.

See Also: For more information about NLS and support for different character sets, see the Oracle8i National Language Support Guide.

For more information about datatypes, see the Oracle8i SQL Reference.

Summary of Oracle Datatypes

Table 12-2 summarizes important information about each Oracle datatype.

Table 12-2 Summary of Oracle Datatype Information

Datatype	Description	Column Length (bytes)
CHAR (size) NCHAR (size)	Fixed-length character data of length size. Fixed-length character data of length size characters or bytes, depending on the choice of national character set.	Fixed for every row in the table (with trailing spaces); maximum size is 2000 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?) Maximum size is determined by the number of bytes required to store each character, with an upper limit of 2000 bytes. Default and minimum size is one character or one byte, depending on the character set.
VARCHAR2 (size) NVARCHAR2 (size)	Variable-length character string having maximum length size bytes. A maximum size must be specified. Variable-length character string having maximum length size characters or bytes, depending on the choice of national character set.	Maximum size is 4000, and minimum is 1. You must specify size for VARCHAR2. Maximum size is determined by the number of bytes required to store each character, with an upper limit of 4000 bytes. You must specify size for NVARCHAR2.
NUMBER (p, s)	Number having precision p and scale s. The precision p can range from 1 to 38. The scale s can range from -84 to 127.	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³¹ -1 bytes, or two gigabytes per row.
RAW (size)	Raw binary data of length size bytes.	Variable for each row in the table, up to 2000 bytes per row. You must specify size for a RAW value.
LONG RAW	Variable-length raw binary data.	Variable for each row in the table, up to two gigabytes per row.
ROWID	Hex data representing unique row addresses. This datatype is primarily for values returned by the ROWID pseudocolumn.	Fixed at six bytes for each row in the table.
UROWID [(size)]	Hexadecimal string representing the logical address of a row of an index-organized table.	The optional size is the size of a column of type UROWID. The maximum size and default is 4000 bytes.
CHAR (size)	Fixed-length character data of length size bytes.	Maximum size is 2000 bytes. Default and minimum size is 1 byte.

Example 1	Scenario:	Common activity includes UPDATE statements that increase the size of the rows.
	Settings:	PCTFREE = 20 PCTUSED = 40
Example 2	Scenario:	Most activity includes INSERT and DELETE statements, and UPDATE statements that do not increase the size of affected rows.
	Settings:	PCTFREE = 5 PCTUSED = 60
	Explanation:	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.
Example 3	Scenario:	The table is very large; therefore, storage is a primary concern. Most activity includes read-only transactions.
	Settings:	PCTFREE = 5 PCTUSED = 40
	Explanation:	PCTFREE is set to 5 because this is a large table and you want to completely fill each block.

	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 4000 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.

Number Datatype	The NUMBER datatype stores fixed and floating point numbers. Positive numbers in the range 1 x 10^-130 to 9.99...9 x 10¹²⁵ (with up to 38 significant digits), negative numbers in the range
	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. Oracle guarantees portability of numbers with a precision equal to or less than 38 digits. You can specify a scale and no precision:
	-1 x 10^-130 to -9.99..9x 10¹²⁵ (with up to 38 significant digits), and zero. If precision is not specified, the column stores values as given. If no scale and no precision are specified:
	`column_name NUMBER (*, scale)`
	In this case, the precision is 38 and the specified scale is maintained.

rowids, ROWID pseudocolumn and ROWID datatype	Every row in a nonclustered 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 has an internal pseudo-column named ROWID. This pseudocolumn 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.

12 Guidelines for Managing Schema Objects