Oracle8: Database Administration 12-13.
Oracle Data Types
Earlier versions of Oracle used the restricted ROWID format. A restricted
ROWID used only 6 bytes internally and did not contain the data object
number. This format was acceptable in Oracle7 or an earlier release, because
the file numbers were unique within a data base. Thus earlier releases did
not permit more than 1022 data files.
Even though Oracle8 removed this restriction by using tablespace-relative
file numbers, the restricted ROWID is still used in objects like
nonpartitioned indexes on nonpartitioned tables where all the index entries
refer to rows within the same segment.
12-8
Copyright  Oracle Corporation, 1998. All rights reserved.
Restricted ROWID
• Can identify rows within a segment
• Needs less space
BBBBBBBB FFFFRRRR
Block number Row number File number

12-14 Oracle8: Database Administration.
Lesson 12: Managing Tables
Collections
Two types of collection data types are available to store data that is repetitive
for a given row in a table. The objects option is needed to define and use
collections. A brief discussion of these types follows.

Nested tables provide a means of defining a table as a column within a table.
They can be used to store sets that may have a large number of records such
as number of items in an order. In this example, ORDERS is the parent table,
and ITEMS is the nested table.
Nested tables generally have the following characteristics:
• A nested table is an unordered set of records or rows.
• The rows in a nested table have the same structure.
• Rows in a nested table are stored separate from the parent table with a
pointer from the corresponding row in the parent table.
• Storage characteristics for the nested table can be defined by the
database administrator.
• There is no predetermined maximum size for a nested table.
Relationship Type (REF)
Relationship types are used as pointers within the database. The use of these
types require the Objects option. As an example, each item that is ordered
could point to, or reference a row in the PRODUCTS table, without having
to store the product code.
User-Defined Types
Oracle allows a user to define abstract data types and use them within the
application. The use of this feature requires the Objects option.
12-16 Oracle8: Database Administration.
Lesson 12: Managing Tables
Creating a Table
Syntax
Use the following command to create a table:
CREATE TABLE [schema.] table
(column datatype [ , column datatype ] )

TABLESPACE identifies the tablespace where the table
will be created
PCTFREE is the amount of space reserved in each
block (in a percentage of total space minus
the block header) for rows to grow in length
PCTUSED determines lower limit of space used on a
block (after it fills to PCTFREE) before it
becomes available for further row inserts
INITRANS specifies the number of transaction entries
preallocated in each block (The default is
1.)
MAXTRANS limitsthenumberoftransactionentriesthat
can be allocated to each block (The default
is 255.)
STORAGE identifiesthestorageclausethatdetermines
how extents will be allocated to the table
LOGGING specifies that the creation of the table will
be logged in the redo log file
(It also specifies that all subsequent
operationsagainstthetablearelogged.This
is the default.)
NOLOGGING specifies that the creation of the table and
certain types of data loads will not be
logged in the redo log file
12-18 Oracle8: Database Administration.
Lesson 12: Managing Tables
CACHE specifies that the blocks retrieved for this

storage characteristics as an existing table.
Oracle8: Database Administration 12-19.
Creating a Table
Other Options
1 While using Oracle Schema Manager, the user also has the option to let
the tool automatically define the storage and block utilization parameters
based on an estimate of the initial volume, the growth rate, and the DML
activity on the table.
2 On Windows NT, it is possible to use the Table Creation wizard inside
the Oracle Schema Manager to create a table.
Instructor Note
Demonstrate the use of the Table Wizard or Auto Calculation of storage and
block utilization parameters in Oracle Schema Manager.
12-20 Oracle8: Database Administration.
Lesson 12: Managing Tables
• Place tables in a separate tablespace—not in the tablespace that has
rollback segments, temporary segments, and indexes.
• Use a few standard extent sizes that are multiples of
5*DB_BLOCK_SIZE to minimize fragmentation.
• To improve performance of full table scans, align extent sizes with
DB_FILE_MULTIBLOCK_READ_COUNT, which is an initialization
parameter that defines how many blocks are requested by the server
processes in each read call to the operating system while reading the
whole table.

reached. Using the formula given reduces the time taken to scan the free list
by increasing the probability of finding a block with the required free space.
Note
The value for average row size can be estimated using the ANALYZE
TABLE command, which is discussed in a subsequent section.
12-12
Copyright  Oracle Corporation, 1998. All rights reserved.
Setting PCTFREE and PCTUSED
• Compute PCTFREE
(Average Row Size −− Initial Row Size) * 100
Average Row Size
• Compute PCTUSED
Average Row Size * 100
100 −− PCTFREE −−
Available Data Space
12-22 Oracle8: Database Administration.
Lesson 12: Managing Tables
Row Migration
If PCTFREE is set to a low value, there may be insufficient space in a block
to accommodate a row that grows as a result of an update. When this
happens, the Oracle server will move the entire row to a new block and leave
a pointer from the original block to the new location. This process is referred
to as row migration. When a row is migrated, I/O performance associated
with this row decreases because the Oracle server must scan two data blocks
to retrieve the row.
Row Chaining
Row chaining occurs when a row is too large to fit into any block. This

speed up the creation of the table.
Constraints, triggers, and table privileges are not copied to the new table that
is created in this manner. If a column was defined as NOT NULL in the
original table, the corresponding column in the new table will also be
defined as NOT NULL.
12-14
Copyright  Oracle Corporation, 1998. All rights reserved.
Copying an Existing Table
CREATE TABLE new_emp
STORAGE(INITIAL 200K NEXT 200K
PCTINCREASE 0 MAXEXTENTS 50)
NOLOGGING
TABLESPACE data01
AS
SELECT * FROM scott.employees;
12-24 Oracle8: Database Administration.
Lesson 12: Managing Tables
Controlling Space Used by Tables
Some of the storage parameters and any of the block utilization parameters can
be modified by using the ALTER TABLE command.
Syntax
ALTER TABLE [schema.]table
{[ storage-clause ]
[ PCTFREE integer ]
[ PCTUSED integer ]
[ INITRANS integer ]
[ MAXTRANS integer]}

The value of MINEXTENTS can be changed to any value that is less
than or equal to the current number of extents in the table. It will have no
immediate effect on the table, but will be used if the table is truncated.
• MAXEXTENTS
The value of MAXEXTENTS can be set to any value equal to or greater
than the current number of extents for the table.
RESTRICTIONS
• The value of INITIAL cannot be modified for a table.
• The value of NEXT specified will be rounded to a value that is a multiple
of the block size greater than or equal to the value specified.
Block Utilization Parameters
Block utilization parameters may be changed to:
• Improve space utilization
• Minimize the possibility of migration
The effects of changing the block utilization parameters are as follows:
• PCTFREE
A change to PCTFREE will affect future inserts. Blocks that are not used
for inserts because they had already been filled to (100-PCTFREE) will
not be affected until they are back on the free list. They can only be
placed on the free list if their use drops to below PCTUSED.
12-26 Oracle8: Database Administration.
Lesson 12: Managing Tables
• PCTUSED
Any change to PCTUSED will affect all the blocks in the table. If a row
is updated or deleted, the block containing the row will be checked for
its use and reused for inserts if the use is below PCTUSED.
• INITRANS

in one of the files in the tablespace containing the table.
12-16
Copyright  Oracle Corporation, 1998. All rights reserved.
Manually Allocating Extents
ALTER TABLE scott.employees
ALLOCATE EXTENT(SIZE 500K
DATAFILE ‘/DISK3/DATA01.DBF’);
12-28 Oracle8: Database Administration.
Lesson 12: Managing Tables
Note
NEXT_EXTENT value in DBA_TABLES will not be affected by manual
extent allocation. The Oracle server will not recalculate the size of the next
extent when this command is executed.
Oracle8: Database Administration 12-29.
Controlling Space Used by Tables
• The high water mark for a table indicates the last block that was ever
used for the table.
• As data is inserted into the table, the high water mark is moved to mark
the last block used.
• The high water mark is not reset when rows are deleted from the table.
• The high water mark is stored in the segment header of the table.
• When the Oracle server performs full table scans, it reads all the blocks
up to the high water mark.
12-17

High water mark
LAST_USED_EXTENT_FILE_ID,
LAST_USED_EXTENT_BLOCK_ID
TOTAL_BLOCKS
UNUSED_BLOCKS
Oracle8: Database Administration 12-31.
Controlling Space Used by Tables
The following PL/SQL block can be used to find and print the number of
blocks allocated to a table and the number of unused blocks:
SVRMGR> DECLARE
2> v_owner VARCHAR2(30) := 'SCOTT' ;
3> v_segment_name VARCHAR2(30) := 'EMPLOYEES';
4> v_segment_type VARCHAR2(30) := 'TABLE';
5> v_total_blocks NUMBER;
6> v_total_bytes NUMBER;
7> v_unused_blocks NUMBER;
8> v_unused_bytes NUMBER;
9> v_last_used_extent_file_id NUMBER;
10> v_last_used_extent_block_id NUMBER;
11> v_last_used_block NUMBER;
12>
13> BEGIN
14> dbms_space.unused_space(v_owner,
15> v_segment_name,
16> v_segment_type,
17> v_total_blocks,
18> v_total_bytes,

Use the following command to deallocate unused space for a table:
ALTER TABLE [schema.]table
DEALLOCATE UNUSED [KEEP integer [ K|M ] ]
KEEP specifies the number of bytes above the high water mark that should
be retained.
If the command is used without the KEEP clause, the Oracle server will
deallocate all unused space above the high water mark. If the high water
mark is at an extent less than the value of MINEXTENTS, the Oracle server
will release extents above MINEXTENTS.
12-19
Copyright  Oracle Corporation, 1998. All rights reserved.
Free space after deleteUnused block
High water mark
Before
deallocation
Used block
Deallocation of Unused Space
ALTER TABLE scott.employees
DEALLOCATE UNUSED;
After
deallocation
Oracle8: Database Administration 12-33.
Controlling Space Used by Tables
Consider the example in the graphic. If MINEXTENTS for the table are four
or lower, the Oracle server deallocates all unused blocks above the high
water mark as shown. Notice that the fifth extent (with ID=4) now contains
only five blocks. If MINEXTENTS are five for the table, the Oracle server

TRUNCATE TABLE scott.employees;
Extent ID 0 1
High water mark
Free space
Oracle8: Database Administration 12-35.
Controlling Space Used by Tables
• If the DROP clause, which is the default, is used:
- All extents except those specified by MINEXTENTS are
deallocated.
- The high water mark is reset to point to the first block in the table.
- The value of NEXT_EXTENT for the table is reset to the size of the
extent with the lowest extent_id that is deallocated—that is, if
MINEXTENTS is 2, NEXT_EXTENT size will be set to the size of
the third extent of the table.
• Specify the REUSE clause to retain all the space used by the table.
• The effect of REUSE or DROP cascades to the indexes.
12-36 Oracle8: Database Administration.
Lesson 12: Managing Tables
A table may be dropped if it is no longer needed or if it is to be reorganized.
Syntax
Use the following command to drop a table:
DROP TABLE [schema.] table
[CASCADE CONSTRAINTS]
When a table is dropped, the extents used by the table are released. If they