Quick and dirty is a count of constraints by table_name and constraint_type.
More definite is to count by table_name, constraint_type and condition, but of course, because the
latter is a LONG, you might need to extract the longs into a varchar2 first.
if you find some, then you just drop the duplicates, and perhaps rename the remaining one to a
non-auto generated name
hth
connor
- Wolfson Larry - lwolfs <lawrence.wolfson_at_acxiom.com> wrote:
> Connor, It's 0130 AM here and I'm working on a DB that won't come up after
> we restarted it after a cold backup. This is after we spent 24 hours
> recovering it from veritas yesterday. I'm a little (lot) groggy.
>
> What's the fastest way to unveil the scenario you described below?
>
> Thanks
> Larry
> -----Original Message-----
> From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]
> On Behalf Of Connor McDonald
> Sent: Saturday, November 13, 2004 10:07 PM
> To: oracle-l_at_freelists.org
> Subject: Re: What this is?
>
> Another thing to look for is the (poor) practice of people leaving their
> check constraints with
> auto-generated names (SYS_C....). When you export/import et al, you can get
> 'n' copies of the
> same constraint.
>
> My favourite example is a vendor that once sent us a seed data export dump
> file with some 300
> copies of the same constraint on SYSTEM.DEF$...(something) because they
> always unload/reloaded
> with a FULL=Y. This was picked up because it took longer to import this
> (virtually empty) table
> then any table in the import!
>
> hth
> connor
>
>
> **********************************************************************
> The information contained in this communication is
> confidential, is intended only for the use of the recipient
> named above, and may be legally privileged.
> If the reader of this message is not the intended
> recipient, you are hereby notified that any dissemination,
> distribution, or copying of this communication is strictly
> prohibited.
> If you have received this communication in error,
> please re-send this communication to the sender and
> delete the original message or any copy of it from your
> computer system. Thank You.
>
> --
> http://www.freelists.org/webpage/oracle-l
>
Connor McDonald
Co-author: "Mastering Oracle PL/SQL - Practical Solutions"
ISBN: 1590592174
web:
http://www.oracledba.co.uk
web:
http://www.oaktable.net
email: connor_mcdonald_at_yahoo.com
Coming Soon! "Oracle Insight - Tales of the OakTable"
"GIVE a man a fish and he will eat for a day. But TEACH him how to fish, and...he will sit in a boat and drink beer all day"
Win a castle for NYE with your mates and Yahoo! Messenger
http://uk.messenger.yahoo.com
--
http://www.freelists.org/webpage/oracle-l
Received on Sun Nov 14 2004 - 07:13:15 CST