|
Category :
Database
Resources -> RDBMS Server
DB Version
:
Oracle 9i
OS Details :
Sun Solaris9
Oracle's
9i version of the database has introduced numerous new features that
make database management and development easier. Recorded below are a
few limitations that we hit as we started using these newer features.
These limitations are documented limitations but are ones that we need
to be aware of:
(1) Resumable Space
management:
Resumable space management can be
used to manage the impacts a space related failure may have on a
session. When enabled within a session, this features allows the
transaction to be suspended over a period of time when space related
errors occur. Once the space issues have been resolved, the
transaction will continue from the point of failure.
Limitation:
This feature cannot be used if
any remote objects are involved in any way as part of the transaction,
unless the is set to be read only.
Example:
Case 1:
-- View created using a database
link
SQL> create view test_view as
select * from
test_table@testdb;
View created.
-- Enable resumable space
management. The user needs to have RESUMABLE system privilege.
SQL> alter session enable
resumable name 'test';
Session altered.
SQL> select count(*) from
test_view;
ORA-30044: Resumable cannot be
enabled with distributed transactions
Case 2:
SQL> alter session enable
resumable name 'test';
Session altered.
SQL> set transaction read only;
Transaction set.
SQL> select count(*) from
test_view;
COUNT(*)
-------------
15633
(2) Table Compression:
Table Compression, a new
feature introduced in Oracle9i Release2, can be used to compress
entire tables, table partitions and materialized views. It will
drastically reduce the disk space and buffer cache requirements and in
many cases improve query performance especially on IO bound systems.
Data compression eliminates duplicate values in a database
block.Compression happens between column/row values, not within
column/row values.
Limitation:
Columns cannot be added or
dropped from compress tables and so make sure that you compress only
those tables whose structure will not be altered periodically.
Example:
SQL> create table test_table as
select * from dummy_table;
Table created.
SQL> alter table test_table
compress;
Table altered.
SQL> alter table test_table add
(col1 number);
ORA-22856: cannot add columns to
object tables.
(3) Automatic segment
space management:
Automatic segment space
management eleminates the need for freelists to keep track of free
blocks within a segment. Automatic segment space management creates a
bitmap within blocks in the segment called BMBs which normally takes
up the first three blocks in the segment.
Limitation:
There is an existing problem /
bug with this feature that's been supposedly fixed in version 10.2.
Here are the bug details:
Symptoms
When inserting into a table in an
ASSM (automatic segment space management) tablespace, the following
error is raised:
ORA-00600: internal error code,
arguments: [ktspgetmyb-1], [], [], [], [],
The problem can also be seen for
updates.
Changes
Previously, a truncate of the
table in question was interrupted - either deliberately by hitting
Ctrl-C, or due to a client crash etc.
Cause
This problem is addressed in
Bug 3279497.
By interrupting a truncate
command, the extent bitmap will be left in an inconsistent state. This
will result in a problem when an extent switch is required (ie. when
the current extent is full) and the ORA-600 [ktspgetmyb-1] is raised.
Solution
To resolve the problem, the
table needs to be truncated (to reset the extent bitmap).
If you've hit a limitation or
a bug while using any of the 9i features, email them to us at
infoxchange@dbaxchange.com
and we will post them
here. |
