Oracle Scratchpad

October 2, 2007

Sequences

Filed under: Infrastructure,Oracle,Troubleshooting — Jonathan Lewis @ 7:32 pm GMT Oct 2,2007

Just in from the Oracle Forum:

Can someone please tell me how to find which sequence is being used by which column in the whole database.

Oracle sequences are not used by Oracle tables, of course, they are used by application code. But if you don’t have the application documentation to tell you which sequences have been used to supply values for which tables, then here are a few clues that might help you work out if there are any relationships.

  • Naming: Some sites have naming conventions that might help; so you might find sequence names (in dba_sequences) that look similar to table names. For example, cust_seq and customer_seq look like promising sequences if you have to supply an ID for a customer table. Add probability points if the sequence and table are owned by the same schema.
  • Trawl the shared SQL area: if the sequences are being used efficiently, then there will be code in v$sql that does things like “insert into tableX …. sequence_name.nextval …”. Be cautious – it’s a little brutal to hammer your way through v$sql. If you’re using 10g the view v$sqlstats is less latch-intensive to use.
  • Try the same trick with dba_source. “select owner, name, type, line from dba_source where upper(text) like ‘%NEXTVAL%’;” you may have to trawl up and down the source for a few lines to find references to the table, of course.
  • Coincidences – keep an eye on the high values for ID columns in tables – they’re usually columns with an index that is susceptible to the “index (min/max) range scan” – and check dba_sequences.last_number for any sequence with a very similar value.

If you can think of any more “maybe match” ideas to add to the list, feel free to add them as comments.

Remember, though, the best way to know which sequences have been associated with which table is to make sure that the system was documented properly in the first place. So a time-machine is always helpful.

When it comes to documenting, though, don’t forget that you have the ‘comment’ command, so you could document the connection in the database when you create the table:

SQL> comment on column t1.n1 is 'Generated by sequence XXX';

Comment created.

SQL> select *
  2  from   user_col_comments
  3  where  comments is not null
  4  /

TABLE_NAME   COLUMN_NAM COMMENTS
------------ ---------- -----------------------------------
T1           N1         Generated by sequence XXX

1 row selected.

7 Comments »

  1. We have a “seqregistry” table which associates sequences with the tables which use them. It has “seq_owner”, “seq_name”, “table_owner” and “table_name” columns.

    This comes in handy with certain scripts which clone subsets of our production tables to test databases; typically, if we forget to “register” any new sequences, certain cloned triggers won’t work.

    Initially we relied on names like “_sequence”, but we found that this didn’t always work. Hence the registry table.

    Comment by Robert Shepard — October 2, 2007 @ 8:30 pm GMT Oct 2,2007 | Reply

  2. A quick trick to find all “insert into tableX …. sequence_name.nextval …” statements (surely a tad latch-intensive):

    create table t (x int);
    create sequence seq;
    insert into t(x) values (seq.nextval);
    
    SQL> select sql_text from v$sql where (hash_value, address) in (
      2  select from_hash, from_address from v$object_dependency where to_owner = 'DELLERA' and to_name = 'T'
      3  intersect
      4  select from_hash, from_address from v$object_dependency where (to_owner, to_name) in (
      5    select sequence_owner, sequence_name from dba_sequences
      6  )
      7  );
    
    SQL_TEXT
    -------------------------------------
    insert into t(x) values (seq.nextval)
    

    Comment by Alberto Dell'Era — October 2, 2007 @ 10:07 pm GMT Oct 2,2007 | Reply

  3. I’d suggest USER_DEPENDENCIES as a place to look (referenced_type=’SEQUENCE’). It may be faster that USER_SOURCE (or limit USER_SOURCE to ones with dependencies). You may miss out dynamic SQL though.

    Whatever version control mechanism you use may be able to extract a complete set of source code, and then grep is the simple answer. That’s especially appropriate if you’ve got wrapped packages in the db or shell scripts, SQL*Loader files, Java or anything else that doesn’t live in the database.

    Comment by Gary — October 2, 2007 @ 10:47 pm GMT Oct 2,2007 | Reply

  4. I am always using this script to get any object being used (including sequences)

    REM ### Get the name of an object in use
    
    SET PAGES 500
    SET VERIFY ON
    
    DEFINE user = 'D_SPS'
    
    SPOOL who_use.txt
    
    ACCEPT proc_name PROMPT 'Enter the name of the object you want to get: '
    
    COLUMN aa HEADING 'Nom' FORMAT A50
    COLUMN bb HEADING 'Type' FORMAT A10 
    COLUMN cc HEADING 'No|Ligne' FORMAT 9999
    --COLUMN dd HEADING 'Texte' FORMAT A60 TRUNC
    COLUMN dd HEADING 'Texte' FORMAT A80 
    
    SELECT A.name aa, 
     -- A.type bb, 
     -- A.line cc, 
        A.text dd
    FROM all_source A
    WHERE A.owner = UPPER('&user')
    AND upper(A.text) like upper('%&proc_name%');
    
    
    SPOOL OFF
    

    Comment by Houri Mohamed — October 3, 2007 @ 7:12 am GMT Oct 3,2007 | Reply

  5. Tom Kyte recently wrote an article in Oracle Magazine describing a new 11g feature which might be useful in this situation.

    “Now, Where Did I Leave My Keys?”

    http://www.oracle.com/technology/oramag/oracle/07-nov/o67asktom.html

    Comment by Scot — November 21, 2007 @ 3:43 pm GMT Nov 21,2007 | Reply

  6. Easier than documenting the system…create just one sequence and use it for everything!

    Comment by Rexton — June 11, 2008 @ 9:38 pm GMT Jun 11,2008 | Reply

  7. The article has now moved to:

    http://www.oracle.com/technetwork/issue-archive/2007/07-nov/o67asktom-101004.html

    P.s. Oracle website new design broke most of the links… grrr!
    And let’s not talk about Sun web links :(
    Why don’t they use HTTP 301?

    Comment by Kamal — March 25, 2011 @ 5:29 pm GMT Mar 25,2011 | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.