Home » SQL & PL/SQL » SQL & PL/SQL » how to know sequence name on a table?
how to know sequence name on a table? [message #396096] Fri, 03 April 2009 18:40 Go to next message
nastyjillu
Messages: 211
Registered: February 2009
Senior Member
hi,

how to know SEQUENCE name on a table?

in all_sequences table i didnot find table name column.

so how to find out??


thanx
jillu

Report message to a moderator

Re: how to know sequence name on a table? [message #396097 is a reply to message #396096] Fri, 03 April 2009 19:02 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>how to know SEQUENCE name on a table?
You are wrong to assume that a sequence MUST be associated with 1 and only 1 table.

Of course if you had been willing or able to RTFM
http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_6015.htm#sthref7288

you would have read the following:

Sequence numbers are generated independently of tables, so the same sequence can be used for one or for multiple tables.

In the future please follow Posting Guidelines as found below
http://www.orafaq.com/forum/t/88153/0/

Report message to a moderator

Re: how to know sequence name on a table? [message #396099 is a reply to message #396096] Fri, 03 April 2009 20:19 Go to previous messageGo to next message
ktanya
Messages: 10
Registered: July 2007
Junior Member
The user_sequences vies store the information about the sequence name.
Have I answered your question?
Tanya

Report message to a moderator

Re: how to know sequence name on a table? [message #396417 is a reply to message #396099] Mon, 06 April 2009 09:16 Go to previous messageGo to next message
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
Senior Member
ktanya wrote on Fri, 03 April 2009 21:19
The user_sequences vies store the information about the sequence name.
Have I answered your question?
Tanya


Please do not follow this advice.

Report message to a moderator

Re: how to know sequence name on a table? [message #396638 is a reply to message #396096] Tue, 07 April 2009 07:06 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
There is a school of database design that advocates using a single sequence for all your tables, or at least for all of each group of tables that have PK-FK links to each other.
It makes it much easier to spot when you've joined colums incorrectly, as you suddenly stop getting any results - each FK id value matches with only on other id value in the whole db.

Report message to a moderator

Re: how to know sequence name on a table? [message #396668 is a reply to message #396638] Tue, 07 April 2009 09:25 Go to previous messageGo to next message
nastyjillu
Messages: 211
Registered: February 2009
Senior Member
yes i know that a SEQUENCE can be used by many tables.

but if i want to know the name of a SEQUENCE used by a table what i have to do?? i didnot find that information in table description (by pressing F4 in toad on table name).

Report message to a moderator

Re: how to know sequence name on a table? [message #396670 is a reply to message #396096] Tue, 07 April 2009 09:29 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>i want to know the name of a SEQUENCE used by a table
A sequence is NOT "used by a table".
A table is a passive receptacle.
A table can not take any action.
You could parse V$SOURCE, but this is easier said than done.

Report message to a moderator

Re: how to know sequence name on a table? [message #396688 is a reply to message #396668] Tue, 07 April 2009 12:55 Go to previous messageGo to next message
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
Senior Member
To put it another way, because I am not sure you understand:

In Oracle, there is no relation between a TABLE and a SEQUENCE.

Report message to a moderator

Re: how to know sequence name on a table? [message #396849 is a reply to message #396096] Wed, 08 April 2009 02:54 Go to previous messageGo to next message
Nithinks
Messages: 1
Registered: April 2009
Location: India
Junior Member
Hi,
It is time to go for a trial and error method to find the sequence used, if you don't find the code which inserts into the table. You may find the largest number in the table and try to match it with the highest sequence value generated.This may help you to figure out the sequence in some cases.
Nithin

Report message to a moderator

Re: how to know sequence name on a table? [message #396860 is a reply to message #396668] Wed, 08 April 2009 03:14 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
The most likely place to find a sequence being used to popuate a primary key column on a table is in a Before Inset trigger, that fires for every row inserted.

Look in USER_TRIGGERS.

It' is quite possible that the sequence is used in the initial INSERT statement, in which case you'll have to look through all the dependant objects manually.

Report message to a moderator

Re: how to know sequence name on a table? [message #397284 is a reply to message #396860] Thu, 09 April 2009 17:12 Go to previous message
nastyjillu
Messages: 211
Registered: February 2009
Senior Member
thanks JRowbottom.

you are right. i can find that way.

thanks
jillu

Report message to a moderator

Previous Topic: Using WHEN COUNT function in conjunction with Partition By
Next Topic: PL/SQL Variable Size>>>>
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Mon Aug 11 04:21:10 CDT 2025
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.