Primary Key constraint (was: Re: getting DDL for all indexes on a table (using dbms_metadata.get))

On May 22, 6:37 am, Mark D Powell <Mark.Pow..._at_eds.com> wrote:
> On May 21, 11:59 am, GS <G..._at_GS.com> wrote:
>
>
>
>
>
> > gazzag wrote:
> > > On 21 May, 15:09, GS <G..._at_GS.com> wrote:
> > >> Thanks - but the schema that owns the table does not have create session
> > >> priv's, so I need to run this from system schema, and this examples does
> > >> not work..- Hide quoted text -
>
> > >> - Show quoted text -
>
> > > Simply apend the schema name:
>
> > > SELECT DBMS_METADATA.GET_DEPENDENT_DDL('INDEX','TEST',
> > > '<schema_name'>) FROM DUAL;
>
> > > HTH
>
> > > -g
>
> > got it, that did the trick
>
> > thanks!- Hide quoted text -
>
> > - Show quoted text -
>
> First, GS, very nice example.
>
> A couple of general comments about using get_dependend_ddl.
>
> If the table has a PK or UK on it and you tell run it with 'INDEX' you
> will get create index statements for the index used to support the PK
> or UK but not the constraint definition itself.  The create statements
> do not have ending semicolons so some editing is required.
>
> If you run the procedure with 'CONSTRAINT' you will get an ALTER TABLE
> to add the PK/UK constaint with index definition code.
>
> If you run both scripts depending on the order you run the scripts in
> you can get duplicate index creation errors.
>
> You may also want to extract the grants and check for synonyms for the
> table.
>
> HTH -- Mark D Powell --

I'm working on an ERP system that includes its own tables for extending DDL. I recently noticed that only those tables have primary keys, and the thousands of app tables don't. This has never been an issue for me, I only even noticed it because I tried to create a materialized view log on a few of the app tables (ORA-12014, using the rowid option gave inappropriate full-table-scans, negating the idea of just sending newly commited data). So I have an inquiry to the vendor on what that is all about (and am sure they are having lively discussions about it :-), but it leads me to wonder what other "new" features might be dependent on primary key constraints, besides, I assume, partitioning?

9.2.0.6 and 10.2.0.3/4, no partitioning.

jg

--
@home.com is bogus.
Just as google starts captcha:
http://arstechnica.com/news.ars/post/20080415-gone-in-60-seconds-spambot-cracks-livehotmail-captcha.html