Oracle7 Server Administrator's Guide

Contents Index Home Previous Next

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.

Character Datatypes 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.
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^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.
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.
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.
MLSLABEL Datatype 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.

Summary of Oracle Datatypes

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

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.
Table 10 - 2. Summary of Oracle Datatype Information


Contents Index Home Previous Next