A table is the most visible object in the Oracle RDBMS. It
is the structure used by Oracle for storing data and this consists of rows and
columns. It is normally created in a table segment which consists of one or
more extents. Extents consist of data blocks. It is normally used to minimize
the amount of wastage storage. Extents act as the building blocks of segments.
When a table grows filling the current extents, a new extent is created for
that table. These extents grow in the manner specified by the STORAGE clause
used while creating a table which if not included during table creation; the
default STORAGE clause defined on the tablespace is used.
Tables can be defined and created by using Enterprise
Manager, Schema Manager, or the CREATE TABLE command. I personally like the
CREATE TABLE way to create a table as it works as a powerful tool when we need
to accomplish some complex tasks. One more advantage is that we can include the
STORAGE clause while creating a table using the CREATE TABLE command. And as I
have started talking about STORAGE, let me give some more details on STORAGE as
this is important for specifying the initial size and characteristics of a
tablespace, etc. The DEFAULT STORAGE is used to define the storage parameters
on a tablespace which can be used for any table created on the particular
tablespace that does not have a STORAGE clause defined during its creation. And
if a STORAGE clause is defined which creation of a table in that particular
tablespace, it would override the DEFAULT STORAGE clause defined. The STORAGE
clause has various parameters such as:
·
INITIAL parameter – for specifying the initial size
·
NEXT parameter – for specifying the size of subsequent extents to
be of some number
·
MINEXTENTS parameter – for specifying the minimum number of
extents created (during the creation of the schema object)
·
MAXEXTENTS parameter – just the reverse of MINEXTENTS parameter
(used for specifying the maximum number)
·
PCTINCREASE parameter – for specifying the size of extends after
a second
Though there are many other parameters of STORAGE clause,
those mentioned above are the ones normally used.
Listing 1
CREATE TABLE demo
{
ID NUMBER,
Name VARCHAR2(30),
Address VARCHAR2(80)
}
TABLESPACE example
STORAGE
(
INITIAL 3M NEXT 3M PCTINCREASE 0 MINEXTENTS 3
);The various important data types that are supported by Oracle are the following.
1.
CHAR – stores fixed length characters
2.
DATE – stores date in the form containing Year, Month, Date, Hour,
Minute and Second
3.
NUMBER – stores fixed and floating point numbers
4.
VARCHAR – stores variable length character
5.
VARCHAR2 – Similar to VARCHAR data type
6.
ROWID – stores the data block, the row, and the datafile in the form of
a triplet
7.
LONG – stores variable length character data up to 2GB in size
8.
LONG RAW – similar to LONG data type, except that it is not converted,
as the LONG data type is converted by the Oracle utilities.
