By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,197 Members | 974 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,197 IT Pros & Developers. It's quick & easy.

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

P: n/a
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
Jul 19 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a

"D" <bb*****@hotmail.com> wrote in message
news:22**************************@posting.google.c om...
| 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
Jul 19 '05 #2

P: n/a
D
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@Xenquery .com> wrote in message news:<1u********************@comcast.com>...
"D" <bb*****@hotmail.com> wrote in message
news:22**************************@posting.google.c om...
| 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

Jul 19 '05 #3

P: n/a
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
Jul 19 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.