Re: Querying the dictionary Tables

Well I know that I am querying the views and not the tables. But that is not the point. The partition name and Constraint names are unique to a schema so I am using the schema name to qualify the owner in the queries. Is it still potentially incorrect?

"Sybrand Bakker" <postbus_at_sybrandb.demon.nl> wrote in message news:<unid6gaob2l8f4_at_corp.supernews.com>...
> "Presks" <presks_at_yahoo.com> wrote in message
> news:d30aa02b.0209061403.3b9d042d_at_posting.google.com...
> > Hello
> >
> > I am working on a process to move recently updated records from an old
> > partiton to new partriton before removing the old partition. I have
> > to query the dict tables to get the Parent tables and child tables
> > along with the partiton name and column on which primary or
> > referential integrity is defined. The two queries I am using are :
> >
> > To get the detail tables of Contacts table:
> >
> > SELECT distinct(cons.TABLE_NAME) TABLE_NAME,
> > conscol.column_name REF_COL_NAME,
> > part.partition_name PART_NAME
> > FROM ALL_CONSTRAINTS cons,
> > ALL_CONSTRAINTS consr,
> > ALL_CONS_COLUMNS conscol,
> > ALL_TAB_PARTITIONS part
> > WHERE part.table_name=cons.table_name
> > AND conscol.constraint_name = cons.r_constraint_name
> > AND consr.constraint_name = cons.r_constraint_name
> > AND part.partition_name like '%2002Q3%'
> > AND cons.owner=user
> > AND cons.CONSTRAINT_TYPE = 'R'
> > AND cons.table_name = 'CONTACTS';
> >
> > To get the master table for Addresses table
> >
> > SELECT distinct(cons.table_name) PARENT_TAB,
> > conscol.column_name PARENT_COL,
> > part.partition_name PART_NAME
> > FROM all_constraints cons,
> > all_constraints consr,
> > all_cons_columns conscol,
> > all_tab_partitions part
> > WHERE part.table_name=conscol.table_name
> > AND conscol.constraint_name=cons.constraint_name
> > AND cons.constraint_name=consr.r_constraint_name
> > AND part.partition_name like '%2002Q3%'
> > AND cons.owner=USER
> > AND consr.table_name='ADDRESSES';
> >
> >
> > Are these queries optimized. Is there any other way to write them to
> > get the same result?
>
> They are potentially incorrect. A constraint name is unique with a schema,
> and the same would apply to partition name.
> If you can, use the DBA or the USER level of the views (you are not querying
> tables, you are querying views)
>
> Hth
Received on Mon Sep 09 2002 - 15:10:13 CDT