Re: ORACLE RDB to Oracle 10g migration
- From: "Dr. Dweeb" <NOSPAM_5msg0h202@xxxxxxxxxxxxxx>
- Date: Sun, 19 Feb 2006 19:46:58 +0100
Michael Austin wrote:
db2sysc@xxxxxxxxx wrote:
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.
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);
sh table aInformation 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.
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
.
- Follow-Ups:
- Re: ORACLE RDB to Oracle 10g migration
- From: Michael Austin
- Re: ORACLE RDB to Oracle 10g migration
- References:
- ORACLE RDB to Oracle 10g migration
- From: db2sysc
- Re: ORACLE RDB to Oracle 10g migration
- From: Michael Austin
- ORACLE RDB to Oracle 10g migration
- Prev by Date: Re: suggestion will be appreciated!
- Next by Date: Re: ORACLE RDB to Oracle 10g migration
- Previous by thread: Re: ORACLE RDB to Oracle 10g migration
- Next by thread: Re: ORACLE RDB to Oracle 10g migration
- Index(es):
Relevant Pages
|
