Dumb story short- need to recreate foreign keys in oracle db

[D]

Database 1 is corrupted. Database 2 was created by dba but none of
the primary or foreign key constraints were ported over. TOAD won't
let me export. I will try ErWin next. What is the best way to
recover this information if the views are corrupted and through
errors?

thanks,

Lowly developer

[Mark C. Stock]

Re: Dumb story short- need to recreate foreign keys in oracle db


"D" <bbcrock@hotmai l.comwrote in message
news:22e171df.0 404191145.291b4 a51@posting.goo gle.com...
| Database 1 is corrupted. Database 2 was created by dba but none of
| the primary or foreign key constraints were ported over. TOAD won't
| let me export. I will try ErWin next. What is the best way to
| recover this information if the views are corrupted and through
| errors?
|
| thanks,
|
| Lowly developer

sad story, but a few more details would help...

database versions?
how did the DBA create database 2? is there an EXP file? was it take at the
system, user, or table level? does it contain any constraints or unique
indexes?
can database 1 be opened?
can data dictionary views be read?
can sys tables be read if the data dictionary views cannot be?
how many schemas are involved?
which schemas cannot have object definitions read from the data dictionary?
is your resume up to date?

;-{ mcs

[D]

Re: Dumb story short- need to recreate foreign keys in oracle db

Oracle 8.1.7 on solaris.

This involves one schema. The data dictionary can be read, but there
are other sys tables and views that have problems. Some of the errors
come from the previous, now fired, dba screwing up the transfer from
the 32-bit version to the 64-bit version- those are the errors I'm
getting. I am not a maintenance dba- I'm a programmer who designs his
own databases. It would not surprise me that I missed something
obvious you can do with system files in oracle, but not sqlserver or
DB2, etc.

DBA created new database by creating a script written in perl that
pulled out the structure of the db only and not the constraints. also
pulled out the data and inserted the data. The scripts I have to
generate the table are not up to date enough to recreated the
database- too many changes in this development system.

So I have the correct structure and the correct data but no
constraints. I can view all the constraints in TOAD which probably
means if I could write or better yet find, a statement to select what
I need from the system table that manages constraints then that is
what I need to find.

thanks,

Don


"Mark C. Stock" <mcstockX@Xenqu ery .comwrote in message news:<1uKdnRtq1 MF8shndRVn-jw@comcast.com> ...

"D" <bbcrock@hotmai l.comwrote in message
news:22e171df.0 404191145.291b4 a51@posting.goo gle.com...
| Database 1 is corrupted. Database 2 was created by dba but none of
| the primary or foreign key constraints were ported over. TOAD won't
| let me export. I will try ErWin next. What is the best way to
| recover this information if the views are corrupted and through
| errors?
|
| thanks,
|
| Lowly developer
>
sad story, but a few more details would help...
>
database versions?
how did the DBA create database 2? is there an EXP file? was it take at the
system, user, or table level? does it contain any constraints or unique
indexes?
can database 1 be opened?
can data dictionary views be read?
can sys tables be read if the data dictionary views cannot be?
how many schemas are involved?
which schemas cannot have object definitions read from the data dictionary?
is your resume up to date?
>
;-{ mcs

[Hans Forbrich]

Re: Dumb story short- need to recreate foreign keys in oracle db

D wrote:

Oracle 8.1.7 on solaris.
>
This involves one schema. The data dictionary can be read, but there
are other sys tables and views that have problems. Some of the errors
come from the previous, now fired, dba screwing up the transfer from
the 32-bit version to the 64-bit version- those are the errors I'm
getting. I am not a maintenance dba- I'm a programmer who designs his
own databases. It would not surprise me that I missed something
obvious you can do with system files in oracle, but not sqlserver or
DB2, etc.
>
DBA created new database by creating a script written in perl that
pulled out the structure of the db only and not the constraints. also
pulled out the data and inserted the data. The scripts I have to
generate the table are not up to date enough to recreated the
database- too many changes in this development system.
>
So I have the correct structure and the correct data but no
constraints. I can view all the constraints in TOAD which probably
means if I could write or better yet find, a statement to select what
I need from the system table that manages constraints then that is
what I need to find.

Oracle uses views such as DBA_TABLES, DBA_CONSTRAINTS and so on to make the
dictionary available.

The scripts to (re)create these views should be found in
$ORACLE_HOME/rdbms/admin - usually catalog.sql

It's not unusual for DBAs to miss running the script after an upgrade, or
fail to look at the result file after running the script or even missing
legit errors when looking at the output. (The output frequently has many
errors listed, mainly "failed to drop non-existant objects")

Sounds like the upgraded source DB is only mildly insane - you may want to
check some of the DBA_ views against the catalog.sql script, esp. those
related to the constraints. If not in sync, then consider rerunning
catalog.sql

/Hans