Well... the transposing you are doing...
Famy, April 04, 2002 - 4:44 pm UTC
in the second query will be more beneficial if you use the column_name, instead of constraint_name.. just think about it
in your case the same constraint name is printed multiple number of times. where as if we us the column_name , we can see the different column names in that specific foreign key...
what do you say..
my two cents
April 04, 2002 - 7:16 pm UTC
read it again - thats what I say.
The "second" query (assuming you mean the first query of the constraint question) shows the constraint name (important and needed) as well as the column name (equally important and relevant).
The "third" query shows the same data as the first, just in a different format. they both show the same AMOUNT and type of information.
Was working on something similar ;)
Yogeeraj, April 04, 2002 - 11:40 pm UTC
Hi,
oops! Should it not have been (column_name instead of constraint name):
===========================================================
select constraint_name, constraint_type,
rtrim(
max(decode(position,1,column_name,null)) || ','||
max(decode(position,2,column_name,null)) || ','||
max(decode(position,3,column_name,null)) || ','||
max(decode(position,4,column_name,null)) || ','||
max(decode(position,5,column_name,null)), ',' ) columns
===========================================================
I tried the same query as a user with dba roles. It just hangs!! why?
When i replace user_xxx with dba_xxx and add the OWNER clause, it works fine!!
===========================================================
swprod@CMTDB.CMT.MU> r
1 select constraint_name, constraint_type,
2 substr(rtrim(
3 max(decode(position,1,column_name,null)) || ','||
4 max(decode(position,2,column_name,null)) || ','||
5 max(decode(position,3,column_name,null)) || ','||
6 max(decode(position,4,column_name,null)) || ','||
7 max(decode(position,5,column_name,null)), ',' ),1,45) columns
8 from (
9 select a.constraint_name,
10 a.constraint_type,
11 b.column_name,
12 b.position
13 from dba_constraints a, dba_cons_columns b
14 where
15 (( a.constraint_type = 'P' and a.table_name = 'KNT100' )
16 or
17 ( a.constraint_type = 'R' and a.r_constraint_name =
18 ( select constraint_name
19 from dba_constraints
20 where constraint_type = 'P'
21 and table_name = 'KNT100'
22 and owner='SWPROD')
23 ) )
24 and a.constraint_name = b.constraint_name
25 and a.owner='SWPROD'
26 and b.owner='SWPROD'
27 )
28 group by constraint_name, constraint_type
29* order by 2, 1
CONSTRAINT_NAME C COLUMNS
______________________________ _ _____________________________________________
PK_KNT100 P ORDNUM,FABSEQ,KMCHCOD,SEQNUM
FK_KNT120_1 R ORDNUM,FABSEQ,KMCHCOD,SEQNUM
Elapsed: 00:00:00.48
swprod@CMTDB.CMT.MU>
===========================================================
Thanks for your time.
best regards
Yogeeraj
PS. Will your next book include something about BACKUP and Recovery? How to migrate to Oracle's Backup and Recovery Manager for Database backup - "Expert one-on-one Oracle" (Page 340)? (the gist of "Oracle 8i Operating System Backup and Recovery Guide ;) ) The way you present your material is simple and clear!! (30mb/30MB/30mB/30Mb or K,k - scientist look at it differently ;))
April 05, 2002 - 9:11 am UTC
It does not just hang -- its that without the "and owner = " on DBA_* views, the query is going to be somewhat inefficient.
The "primary key" of user_constraints is constraint_name
The "primary key" of dba_constraints is constraint_name, owner
When you changed views, in order to not only get the answer fast but in fact to get the RIGHT ANSWER you have to put OWNER on all references.
I'm not planning on a backup and recovery book -- looking at a performance related one :)
Much better.......
Yogeeraj, April 05, 2002 - 12:25 am UTC
hi again,
Much better now:
============================================================
swprod@CMTDB.CMT.MU> r
1 select substr(constraint_name,1,15) "Constrait Name", constraint_type,
2 substr(rtrim(
3 max(decode(position,1,column_name,null)) || ','||
4 max(decode(position,2,column_name,null)) || ','||
5 max(decode(position,3,column_name,null)) || ','||
6 max(decode(position,4,column_name,null)) || ','||
7 max(decode(position,5,column_name,null)), ',' ),1,45) columns, substr(r
_constraint_name,1,15) "References"
8 from (
9 select a.constraint_name,
10 a.constraint_type,
11 b.column_name,
12 b.position,
13 a.r_constraint_name
14 from dba_constraints a, dba_cons_columns b
15 where
16 (( a.constraint_type = 'P' and a.table_name = 'KNT100' )
17 or
18 ( a.constraint_type = 'R' and a.r_constraint_name =
19 ( select constraint_name
20 from dba_constraints
21 where constraint_type = 'P'
22 and table_name = 'KNT100'
23 and owner='SWPROD')
24 ) )
25 and a.constraint_name = b.constraint_name
26 and a.owner='SWPROD'
27 and b.owner='SWPROD'
28 )
29 group by constraint_name, constraint_type, r_constraint_name
30* order by 2, 1
Constrait Name C COLUMNS References
_______________ _ _____________________________________________ _______________
PK_KNT100 P ORDNUM,FABSEQ,KMCHCOD,SEQNUM
FK_KNT120_1 R ORDNUM,FABSEQ,KMCHCOD,SEQNUM PK_KNT100
Elapsed: 00:00:00.86
swprod@CMTDB.CMT.MU>
============================================================
Cheers
Yogeeraj
These queries are very useful
Kulguru, April 05, 2002 - 7:07 am UTC
Tom
If I want to put these scripts into a sql script, how should I add the set conditions along with the script, so that whenever I execute the script those settings take effect. Can you show an example with one of the above queries.
Further, the above (last two) queries are awesome, and will give quick details as far as constraints are concerned. Tom, is it possible to find out what is the kind of relationship between 2 given tables, i.e. 1 to 1 or 1 to many or many to one , or many to many.
Do the above scripts help in that aspect in any way, or do you have another sql to do this kind thing, or should it be done using pl/sql.
Can you kindly show us the way, in this regard.
Thank you
April 05, 2002 - 9:38 am UTC
Just change 'PAR' to '&1'
Then put the sql into a script and run it as:
@scriptname PAR
and that would do the same -- &1, &2, ... those are substition values passed to the script.
If the relationship is 1:1 -- there should be ONE table (IMHO). But anyway, you would have to see if the foreign key columns are also involved in a Unique or Primary key constraint on that table -- so yes ,you can find 1-1 if you write a tricky enough query. 1:M -- that should be assumed. M:M -- you'll NEVER see that as that takes three tables and two 1:M relationships -- there is no such thing as a M:M relationship really....
You can write the 1:1 vs 1:M in a sql query using subtle modifications of the above (a challenge for the reader). But as I said -- its probably just a waste of time as ALL of them should be 1:M.
..everything in a script
Yogeeraj, April 05, 2002 - 8:12 am UTC
hi
hope this helps..
Regards
Yogeeraj
============================================================
--check_pffkcons.sql
--YD-05/04/2002
SET VERIFY OFF;
SET DEFINE ON;
define MOWNER = '&MOWNER'
define MTABLE = '&MTABLE'
select substr(constraint_name,1,15) "Constrait Name", constraint_type,
substr(rtrim(
max(decode(position,1,column_name,null)) || ','||
max(decode(position,2,column_name,null)) || ','||
max(decode(position,3,column_name,null)) || ','||
max(decode(position,4,column_name,null)) || ','||
max(decode(position,5,column_name,null)), ',' ),1,45) columns, substr(r_constraint_name,1,10) "References", substr(status,1,3) Sts
from (
select a.constraint_name,
a.constraint_type,
b.column_name,
b.position,
a.r_constraint_name,
a.status
from dba_constraints a, dba_cons_columns b
where
(( a.constraint_type = 'P' and a.table_name = '&&MTABLE' )
or
( a.constraint_type = 'R' and a.r_constraint_name =
( select constraint_name
from dba_constraints
where constraint_type = 'P'
and table_name = '&&MTABLE'
and owner= '&&MOWNER')
) )
and a.constraint_name = b.constraint_name
and a.owner='&&MOWNER'
and b.owner='&&MOWNER'
)
group by constraint_name, constraint_type, r_constraint_name, status
order by 2, 1;
SET DEFINE OFF;
============================================================
Tom
Famy, April 05, 2002 - 9:59 am UTC
"
If the relationship is 1:1 -- there should be ONE table (IMHO). But anyway, you would have to see if the foreign key columns are also involved in a Unique or
Primary key constraint on that table -- so yes ,you can find 1-1 if you write a tricky enough query. 1:M -- that should be assumed."
Can you gift us a utility which does the above.
From the above what do you mean by '1:M -- that should be assumed.'
Thank you
April 05, 2002 - 11:42 am UTC
Ah, come on... Can't you take up the challenge?
...
You can write the 1:1 vs 1:M in a sql query using subtle modifications of the
above (a challenge for the reader). But as I said -- its probably just a waste of time as ALL of them should be 1:M.
.......
;)
When I said 1:M should be assumed is that
a) M:M -- doesn't exist, we can rule that out
b) 1:1 -- shouldn't exist IMO, I rule that out
c) 1:M -- thats all that is left over!
I know I can do it -- just wanna have others think about it (SQL queries like that can be fun sometimes!)
how should we determine
A reader, March 01, 2004 - 11:08 pm UTC
tom
how should we determine how many 9's to format with (999,999,999,999.99' ), and how we cover for negative numbers.
Sometimes my field can return just zero , how should I cover for that.
Thanks
March 02, 2004 - 7:30 am UTC
you have to answer that first question -- you know the data, no me :)
negative numbers will just take care of themselves. they will plop a "-" in there.
you have to decide what you want to do for zero as well.
the bottom line is -- pretty much anything you can think of, the formats can do. so, what do you want to have happen
A reader, March 03, 2004 - 11:18 am UTC
What does the following format decipher to .. what is the max number this format can support
-NNNGNNNGNN0
I want the negative numbers in brackets, how should I achieve that
Max length of Number Format Model
Michael, August 30, 2012 - 9:24 am UTC
Hi Tom,
why is a number format model limited to 63 chars? (ORA-01481)
("FM" does not count while "D"s and "G"s count.)
I didn't find anything in the docu about this limit...
E.g.
select to_char(-99999999999999999 + (1/3), 'FM999G999G999G999G999G990D0999999999999999999999999999999999999999')
from dual;
ER
Michael, September 11, 2012 - 4:41 am UTC
OK, just posted a comment on the documentation page.
I'll also open an Enhancement SR to get (finally) rid of this annoying limit...