|
|
|
|
|
|
| Author |
Message |
db2sysc@gmail.com
*nix forums beginner
Joined: 19 May 2005
Posts: 8
|
 Posted: Tue Feb 14, 2006 8:14 am Post subject:
ORACLE RDB to Oracle 10g migration
|
|
|
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. |
|
| Back to top |
|
 |
Michael Austin
*nix forums addict
Joined: 06 Sep 2005
Posts: 51
|
| Posted: Sat Feb 18, 2006 10:20 pm Post subject:
Re: ORACLE RDB to Oracle 10g migration
|
|
|
db2sysc@gmail.com wrote:
| Quote: | 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);
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
 |
|
| Back to top |
|
|
Dr. Dweeb
*nix forums Guru Wannabe
Joined: 23 Jul 2005
Posts: 125
|
| Posted: Sun Feb 19, 2006 6:46 pm Post subject:
Re: ORACLE RDB to Oracle 10g migration
|
|
|
Michael Austin wrote:
| Quote: | db2sysc@gmail.com 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 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.
|
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 |
|
| Back to top |
|
|
Michael Austin
*nix forums addict
Joined: 06 Sep 2005
Posts: 51
|
| Posted: Sun Feb 19, 2006 10:29 pm Post subject:
Re: ORACLE RDB to Oracle 10g migration
|
|
|
Dr. Dweeb wrote:
| Quote: | Michael Austin wrote:
db2sysc@gmail.com 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 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.
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
|
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
|
|
| Back to top |
|
|
Google
|
|
| Back to top |
|
|
|
|
The time now is Wed Dec 03, 2008 1:46 am | All times are GMT
|
|
Loans | Loans | Virtual Pets Blog | Gas Suppliers | Credit Cards
|
|
Copyright © 2004-2005 DeniX Solutions SRL
|
|
|
|
Other DeniX Solutions sites:
Unix/Linux blog |
electronics forum |
medicine forum |
science forum |
|
|
Privacy Policy
|
Powered by phpBB © 2001, 2005 phpBB Group
|
|
FAQ
Search
Memberlist
Usergroups
Register
Profile
Log in to check your private messages
Log in
Forum index
»
Databases
»
rdb
