Re: Querying the dictionary Tables

"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

--
Sybrand Bakker
Senior Oracle DBA

to reply remove '-verwijderdit' from my e-mail address