I have to fetch all sequences with their table name along with the column name on which sequence is applied .Some how i managed to fetch table name corresponding to sequence because in my data base sequence is stored with first name as table name from data dictionary(all_sequences and all_tables) .
Please let me know how to fetch corresponding column name also if possible!!
In Oracle, a sequence is an independent object, it's not associated with a specific table or column. For example, you can run this query to get a list of the sequences:
SELECT * FROM all_sequences
And when you create a sequence, you'll notice that there's nothing in the CREATE SEQUENCE syntax to indicate that you want to associate it with a table or column.
A sequence is just a unique number generator, it doesn't care what you do with the number generated from it (i.e. whether you insert the sequence value into a table, etc.), it's just there to provide that unique number.
So it's impossible to tell for a given column what sequence was used (if any) to generate that column's value.
See dcp's answer.
However, a sequence will normally be used to generate a unique key for the table it corresponds to - try looking for primary keys and/or unique indexes on the matching table.
You can often 'guess' at a correlation by looking at the LAST_NUMBER in all_sequences and the following SQL (which looks at the highest number for numeric columns defined as part of a primary key).
select table_name, column_name, utl_raw.cast_to_number(high_value)
from dba_tab_columns
where owner = '...'
and data_type = 'NUMBER'
and (owner, table_name, column_name) in
(select cc.owner, cc.table_name, cc.column_name
from dba_cons_columns cc
join dba_constraints c
on cc.owner = c.owner and cc.constraint_name = c.constraint_name
where c.constraint_type = 'P')
order by 3;
But it is a good idea to adopt a naming standard that indicates the correlation (eg the same as the table_name with a _SEQ on the end).