Which Oracle Table Uses a Sequence

Which Oracle table uses a sequence?

In the database you can search all stored code in your schema like this:

select type, name, line, text
from all_source
where owner = 'MYSCHEMA'
and upper(text) like '%MYSEQ.NEXTVAL%';

In SQL Developer, there is a report to do this.

How can I get all sequences in an Oracle database?

select sequence_owner, sequence_name from dba_sequences;

DBA_SEQUENCES -- all sequences that exist 
ALL_SEQUENCES  -- all sequences that you have permission to see 
USER_SEQUENCES  -- all sequences that you own

Note that since you are, by definition, the owner of all the sequences returned from USER_SEQUENCES, there is no SEQUENCE_OWNER column in USER_SEQUENCES.

How to show sequence table in sequence folder

There are only simple explanations for the error ORA-02289: sequence does not exist when you can ad Hoc query it.

Let's list them

• the sequence exists, but in a different database than your process is connected to.

• the sequence exists, but in a different schema than your process is using to connect.

Solution of the former case is obvious.

In the latter case

• check the schema of the sequence

select SEQUENCE_OWNER from all_sequences where sequence_name = 'XXTG_SAMPLE_HEADER_S';

• check the user your process connects

• grant SELECT on the sequence to the connect user

connect with the user that created the sequence and

grant select on seq_user.XXTG_SAMPLE_HEADER_S to connect_user;

Sequence number in table

One way to try this, although unconventional, would be to run these queries to check if there are any sequence used in your functions , procedures, packages.

select * from user_source where 
         UPPER(TEXT) LIKE '%NEXTVAL%';   

select * from all_source where 
         UPPER(TEXT) LIKE '%NEXTVAL%';  

Then go to the specific procedure, function to check which column/table gets populated by a sequence.
Try this also with '%CURRVAL%'

This might not help if you are running inserts from JDBC or other external applications using a seqeunce.

Find if a column in Oracle has a sequence

You are correct; the sequence is separate from the table, and a single sequence can be used to populate any table, and the values in a column in some table may mostly come from a sequence (or set of sequences), except for the values manually generated.

In other words, there is no mandatory connection between a column and a sequence - and therefore no way to discover such a relationship from the schema.

Ultimately, the analysis will be of the source code of all applications that insert or update data in the table. Nothing else is guaranteed. You can reduce the scope of the search if there is a stored procedure that is the only way to make modifications to the table, or if there is a trigger that sets the value, or other such things. But the general solution is the 'non-solution' of 'analyze the source'.

How does an Oracle table know which sequence it is associated with?

As far as I know, sequences aren't systematically associated with a particular table in Oracle. According to the documentation:

Without sequences, sequential values can only be produced programmatically. A new primary key value can be obtained by selecting the most recently produced value and incrementing it. This method requires a lock during the transaction and causes multiple users to wait for the next value of the primary key; this waiting is known as serialization. If developers have such constructs in applications, then you should encourage the developers to replace them with access to sequences. Sequences eliminate serialization and improve the concurrency of an application.

So the developers of the application need to associate each sequence with the table(s) they are used with. Usually this is done by using a name that somehow indicates what it's used for. So a sequence for populating emp table's primary key might be called emp_sequence.

Oracle - does a column have a sequence attached to it?

there is no way to attach a sequence to a field in oracle, what you can do is to use the sequence in your application as you see fit.

General you'll need to look for triggers on the table, and for procedures that maybe used to insert data to this table, some people use those to regulate sequence use and to sort of attach it to a field but it's not a real attachment but they are just using the sequence and it could be used in many other ways.

---

Related Topics