1. How to we migrate ORACLE RDB objects to Oracle10g ? This includes
tables, indexes, DBO views, Stored Procedure, Triggers etc.

2. How do we migrate data from Oracle RDB to Oracle 10g

3. Any challenges and issue we will face in such migration

4. Any tools that can be leveraged for this Migration.

db2sysc (AT) gmail (DOT) com wrote:

There are various ways of doing this... (unfortunately have had to do this way
too often)

Set up an OCI service in SQL Services for your Rdb database
Create a db link in your Oracle db
do a create table blah as select * from table@rdb_dblink limit to 1 row;

You are going to want to create the table then make sure you move it to the
appropriate tablespace (partition, correct extent size, etc...).

Depending on the data, you can rmu/unload the data and use sql*loader to load it
or insert into oracletable select * from table*rdb_dblink;

Triggers are going to be somewhat manual.. most of the syntax is transportable -
but you will need to rewrite some of these.

All constraints will need to be added after the fact. Syntax is the slightly
different and Rdb does not store a PK in an index. Create a Unique index if
you need it indexed.

Rdb SYNTAX:
create table a (a real not null constraint a_a,
b integer not null constraint a_b,
Primary Key (a,b),
c char);

SQL> sh table a
Information for table A

Columns for table A:
Column Name Data Type Domain
----------- --------- ------
A REAL
Not Null constraint A_A
B INTEGER
Not Null constraint A_B
C CHAR(1)

Table constraints for A:
A_A
Not Null constraint
Column constraint for A.A
Evaluated on COMMIT
Source:
A.A NOT null

A_B
Not Null constraint
Column constraint for A.B
Evaluated on COMMIT
Source:
A.B NOT null

A_PRIMARY_A_B
Primary Key constraint
Table constraint for A
Evaluated on COMMIT
Source:
PRIMARY Key (a,b)

Constraints referencing table A:
No constraints found

Indexes on table A:
No indexes found

Storage Map for table A:
No Storage Map found

Triggers on table A:
No triggers found

*note in Rdb you must name the constraint at create time unlike Oracle which
will generate a name for you.

--
Michael Austin.
DBA Consultant
Donations welcomed. Http://www.firstdbasource.com/donations.html

Michael Austin wrote:
Not wishing to labour the logically obvious, but there is no need for a NOT
NULL constraint in Rdb.
NOT NULL is implicit in PRIMARY KEY (aka NOT NULL UNIQUE).

O10G will create an index automatically for a PRIMARY KEY and create a NOT
NULL constraint on each of the components. This is because O10G is
logically brain dead and does not know the difference between a KEY and an
INDEX.

PTo the original poster.

Please note:

O10G does not correctly deal with isolation levels as Rdb does. You must be
very careful that your application does not rely on Rdb's correct semantics.

O10G has different behaviour for error conditions. The same set of commands
executed on Rdb and O10G can give different results.

O10G has no notion of meta data versioning or transactional DDL (which will
drive you crazy).

Welcome to the disaster zone - I wish you luck.

Dr. Dweeb

Dr. Dweeb wrote:

Thx for the correction - I meant that the NOT NULL could be on a column that was
not a part of the PK and that the syntax is slightly different from that of
Oracle (anyVersion).

--
Michael Austin.
DBA Consultant
Donations welcomed. Http://www.firstdbasource.com/donations.html