Oracle system table
in [Oracle]
|
From: Mounilk on 28 Jul 2010 01:16 Hi, I am working on oracle database which has a few tables that do not have any primary keys. Unfortunately, I cannot modify the table designs. I need to find out the fields in the table that make the record unique. I am not very familiar with Oracle and its system tables, but am pretty sure I should be able to get this information from some system table. Any help is greatly appreciated. Regards, MounilK
From: Maxim Demenko on 28 Jul 2010 02:38 On 28.07.2010 07:16, Mounilk wrote: > Hi, > I am working on oracle database which has a few tables that do not > have any primary keys. Unfortunately, I cannot modify the table > designs. > > I need to find out the fields in the table that make the record > unique. > > I am not very familiar with Oracle and its system tables, but am > pretty sure I should be able to get this information from some system > table. > > Any help is greatly appreciated. > > Regards, > MounilK You can achieve uniqueness in oracle by utilization of unique indexes or unique constraints. To find them you can query select * from user_indexes where uniqueness='UNIQUE' resp. select * from user_constraints where constraint_type = 'U' After you have the names, you can drill down to columns - there are user_ind_columns and user_cons_columns. For all user_ views there are as well all_/dba_ which may be less/more sutitable for your needs. Best regards Maxim
From: Mladen Gogala on 28 Jul 2010 08:18 On Tue, 27 Jul 2010 22:16:44 -0700, Mounilk wrote: > Hi, > I am working on oracle database which has a few tables that do not have > any primary keys. Unfortunately, I cannot modify the table designs. > > I need to find out the fields in the table that make the record unique. > > I am not very familiar with Oracle and its system tables, but am pretty > sure I should be able to get this information from some system table. > > Any help is greatly appreciated. > > Regards, > MounilK Why would a RDBMS need to maintain a record of uniqueness? How would it do that? Nope, there are tools which allow you to determine whether there are duplicates and add unique constraints. RDBMS per se does not maintain anything about the uniqueness of columns nor is it required to. -- http://mgogala.byethost5.com
From: Robert Klemme on 28 Jul 2010 08:54 On 28.07.2010 14:18, Mladen Gogala wrote: > On Tue, 27 Jul 2010 22:16:44 -0700, Mounilk wrote: > >> I am working on oracle database which has a few tables that do not have >> any primary keys. Unfortunately, I cannot modify the table designs. >> >> I need to find out the fields in the table that make the record unique. >> >> I am not very familiar with Oracle and its system tables, but am pretty >> sure I should be able to get this information from some system table. >> >> Any help is greatly appreciated. > > Why would a RDBMS need to maintain a record of uniqueness? How would it > do that? Nope, there are tools which allow you to determine whether there > are duplicates and add unique constraints. RDBMS per se does not maintain > anything about the uniqueness of columns nor is it required to. Adding to that: even if you find out which columns *currently* have unique values per record (see below) this does not guarantee that it is always the case. OP, the only real chance is to either investigate uniqueness constraints or unique indexes as Maxim has indicated or find out otherwise which columns are supposed to be unique (documentation, application code, ask someone). But frankly, if there are columns in the schema which are supposed to be unique but don't have appropriate constraints then I'd question the schema design. Note, if you want to find out the current state of affairs you can do something like select count(*) / count(distinct cola) as rows_per_val_cola , count(*) / count(distinct colb) as rows_per_val_colb .... Columns which turn up 1 are unique right now. But watch out for NULLS! And be prepared that this is likely slow if your tables are big. Kind regards robert -- remember.guy do |as, often| as.you_can - without end http://blog.rubybestpractices.com/
From: Lothar =?utf-8?Q?Armbr=C3=BCster?= on 28 Jul 2010 11:35
Mounilk <mounilkadakia(a)hotmail.com> writes: > Hi, > I am working on oracle database which has a few tables that do not > have any primary keys. Unfortunately, I cannot modify the table > designs. > > I need to find out the fields in the table that make the record > unique. > > I am not very familiar with Oracle and its system tables, but am > pretty sure I should be able to get this information from some system > table. > > Any help is greatly appreciated. > > Regards, > MounilK If you just need to identify a row to issue an update command, using the rowid pseudocolumn may help. This is kind of implicid primary key. But keep in mind that rowids should *never* be stored in the database since they are invalidated e.g. by export/import. Hope that helps, Lothar -- Lothar Armbrüster | lothar.armbruester(a)t-online.de Hauptstr. 26 | 65346 Eltville | |