Export and Import and Referential Integrity Constraints

Export and Import and Referential Integrity Constraints

Post by Boo » Sun, 31 Dec 1899 09:00:00



Running Oracle 8.0.5 on Solaris 2.7.
Is there any reasons why, when moving a database with quite a few
referential integrity constraints to a new server, that the
export/import option
can't be used?
My understanding is, if it's a fresh database built on the new server,
and doesn't have existing data, than an export/import should not pose
any problems.

TIA

 
 
 

Export and Import and Referential Integrity Constraints

Post by Boo » Sun, 31 Dec 1899 09:00:00


Running Oracle 8.0.5 on Solaris 2.7.
Is there any reasons why, when moving a database with quite a few
referential integrity constraints to a new server, that the
export/import option
can't be used?
My understanding is, if it's a fresh database built on the new server,
and doesn't have existing data, than an export/import should not pose
any problems.

TIA

 
 
 

Export and Import and Referential Integrity Constraints

Post by Jerry Gitome » Sun, 31 Dec 1899 09:00:00


        If you have referential integrity on the database you are going to
export
You can run export and not export the constraints.  If you do so you
should then
apply the constraints to the target databaes once it has been created.

        The problem with doing an export with constraints is that you will
have
either make several import runs.  For example is table A is dependent
on table
B which is dependent on table C you must first import C then import B
and then
(finally) import A.  

Quote:>>>>>>>>>>>>>>>>>> Original Message <<<<<<<<<<<<<<<<<<


Import and Referential Integrity Constraints:
Quote:> Running Oracle 8.0.5 on Solaris 2.7.
> Is there any reasons why, when moving a database with quite a few
> referential integrity constraints to a new server, that the
> export/import option
> can't be used?
> My understanding is, if it's a fresh database built on the new server,
> and doesn't have existing data, than an export/import should not pose
> any problems.
> TIA

 
 
 

Export and Import and Referential Integrity Constraints

Post by boo » Sun, 31 Dec 1899 09:00:00


True --
However,  not fully knowing every intricate detail in how Oracle Exports and
Imports, would disabling the constraints before exporting then allow the
import?
What I am asking, is would the export push the constraints as disabled, and
then import then disabled as well?

I'll start digging thru docs, but thought I would ask here as well.


>         If you have referential integrity on the database you are going to
> export
> You can run export and not export the constraints.  If you do so you
> should then
> apply the constraints to the target databaes once it has been created.

>         The problem with doing an export with constraints is that you will
> have
> either make several import runs.  For example is table A is dependent
> on table
> B which is dependent on table C you must first import C then import B
> and then
> (finally) import A.

> >>>>>>>>>>>>>>>>>> Original Message <<<<<<<<<<<<<<<<<<


> Import and Referential Integrity Constraints:

> > Running Oracle 8.0.5 on Solaris 2.7.
> > Is there any reasons why, when moving a database with quite a few
> > referential integrity constraints to a new server, that the
> > export/import option
> > can't be used?
> > My understanding is, if it's a fresh database built on the new server,
> > and doesn't have existing data, than an export/import should not pose
> > any problems.

> > TIA

 
 
 

Export and Import and Referential Integrity Constraints

Post by Doug Coa » Sun, 31 Dec 1899 09:00:00




Quote:> Running Oracle 8.0.5 on Solaris 2.7.
> Is there any reasons why, when moving a database with quite a few
> referential integrity constraints to a new server, that the
> export/import option
> can't be used?
> My understanding is, if it's a fresh database built on the new server,
> and doesn't have existing data, than an export/import should not pose
> any problems.

> TIA

Using import/export is, in my opinion, the preferred method for moving a
database to a new server especially if you are moving to a new version
or patch level.  I use this regularlly not only for the initial load,
but if you are in a parallel environment, to do periodic shcema level
updates to keep the dbs in sync until cutover.

--
Doug Coan
Oracle Certified Professional DBA

Sent via Deja.com http://www.deja.com/
Before you buy.

 
 
 

Export and Import and Referential Integrity Constraints

Post by Ushkalo Igo » Sun, 31 Dec 1899 09:00:00



> Running Oracle 8.0.5 on Solaris 2.7.
> Is there any reasons why, when moving a database with quite a few
> referential integrity constraints to a new server, that the
> export/import option
> can't be used?
> My understanding is, if it's a fresh database built on the new server,
> and doesn't have existing data, than an export/import should not pose
> any problems.

> TIA

I'd like to recommend following methodic:

1. Disable all jobs.
2. Restart oracle in restricted mode
3. Export FULL=Y ROWS=N CONSTRAINTS=Y STATISTIC=COMPUTE
   (full export w/o rows, i.e. all meta-data)
4. Export FULL=Y ROWS=Y CONSTRAINTS=N (all data)
   You can export data by parts - 8.0.5 for some platforms
   couldn't accept dmp files>2Gb 8-(((((
5. Create empty database with all tablespaces (you can change datafiles)
6. Import p.4
7. Import p.3

Good idea after that start both databases and compare (via dblinx)
ALL_OBJECTS as SYS.
This scheme is brief, experience needed to do everything w/o mistakes...
Good luck 8-)

-- Best regards, Igor Ushkalo (igorus!) - igorus(at)mail.ru,  
   ICQ #19972198

 
 
 

1. Enforcing referential integrity using CASCADE constraint, MSDE

I am a newbie, but I've searched MSDN, the faq, Mastering
SQL, and I can not see what I'm doing wrong. I'm trying to
enforce referential integrity on MSDE using the CASCADE
Keyword. However, I keep getting the message "syntax error
near keyword ON" when I do something akin to the following:
USE DQP2
GO

CREATE TABLE Category (
 CategoryKey INT NOT NULL PRIMARY KEY,
 Caption VARCHAR(255)
)
GO

CREATE TABLE Scheme (
 SchemeKey INT NOT NULL PRIMARY KEY REFERENCES Category
(CategoryKey) ON UPDATE CASCADE ON DELETE CASCADE,
 Caption VARCHAR (255)
)
GO

2. VFUG Thursday Internet Conference

3. NEWBIE: Cascading referential integrity constraint questions

4. Database Replication Tips and Tricks??

5. Merge replication and referential integrity constraints

6. SQL: table name as column value

7. About Cascading Referential Integrity Constraints...

8. loading jdbc.sqlserver.SQLServerDatabase form a Servlet

9. Late time referential integrity/constraints

10. Problem: Referential Integrity Constraints lost

11. Problem: Referential Integrity Constraints lost: Correction

12. Foreign keys: how to turn referential integrity constraint off

13. Two-column Referential Integrity Constraint where one one colum value is a constant