Forum Stats

  • 3,751,247 Users
  • 2,250,337 Discussions
  • 7,867,360 Comments

Discussions

Script to ensure Sequences are not larger then the column sizes

user491696
user491696 Member Posts: 12
edited May 23, 2013 9:22PM in SQL & PL/SQL
Hi experts,

is there any way to create a check to ensure Sequences are not larger then the column sizes??? Any answer will be appreciated.

Thanks
Tagged:
«13

Answers

  • Tubby
    Tubby Member Posts: 6,987 Bronze Crown
    977594 wrote:
    Hi experts,

    is there any way to create a check to ensure Sequences are not larger then the column sizes??? Any answer will be appreciated.

    Thanks
    There's no way in Oracle to associate a sequence to a column so you would need to know that information in order to proceed here.

    Assuming you have that information, you should be able to query the data dictionary to figure this out.
    user_tab_cols
    
    user_sequences
    Replace user with dba if you have the proper permissions and you cannot log in as the object owner.

    Cheers,
  • Greg Spall
    Greg Spall Member Posts: 972
    977594 wrote:

    is there any way to create a check to ensure Sequences are not larger then the column sizes??? Any answer will be appreciated.
    Create the sequence properly in the first place??
    create table my_test
      (  id1   number(5),
         id2   number(10) )
    /
    
    create sequence seq_id1 maxvalue 99999;
    create sequence seq_id2 maxvalue 9999999999;
  • user491696
    user491696 Member Posts: 12
    So you are sayin that there is nothing that I can do which will check the column sizes won't get bigger than sequence?? If I am wrong then plzz breif me on this.
    Thanks
  • SomeoneElse
    SomeoneElse Member Posts: 14,866 Silver Crown
    If you create the column as type NUMBER you should be fine until the sun explodes.
  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 40,904 Red Diamond
    edited May 15, 2013 2:30PM
    Hi,
    977594 wrote:
    So you are sayin that there is nothing that I can do which will check the column sizes won't get bigger than sequence?? If I am wrong then plzz breif me on this.
    You can query the data dictionary (in particular the max_value column of all_sequences, or user_sequences, or, if you have the privileges, dba_sequences) to see how many digits a sequnce value might need.

    You can also query the data dictionary (in particular the data_precision and data_scale columns of all_tab_columns) to find out what numbers will fit into a given NUMBER column.

    However, there is nothing in the data dictionary that tells which columns are populated from which sequences. You have to keep track of that yourself. If you have a table that contains the table owners, table names, column names, sequence owners and sequence names, then you can join that table to all_sequences and all_tab_columns to get the information you want.

    Edited by: Frank Kulash on May 15, 2013 2:23 PM
  • Tubby
    Tubby Member Posts: 6,987 Bronze Crown
    977594 wrote:
    So you are sayin that there is nothing that I can do which will check the column sizes won't get bigger than sequence?? If I am wrong then plzz breif me on this.
    Thanks
    No, that's not what I said.

    What I said is that you would need to know which sequence maps to which column, and if you knew that you could write a query to figure this out (you'd have to look at the data dictionary views I sent and apply a little independent thought to the process).

    If you don't know that then yes... what you are asking is impossible short of inspecting all the code out there and finding out which sequence is used on which table.column.

    Cheers,
  • >
    is there any way to create a check to ensure Sequences are not larger then the column sizes???
    >
    Sure - just use this code on your table column that is defined as a NUMBER(p,s):
    alter table myTable modify (myNumberColumn number)
    That will redefine the table column to support the maximum numeric value allowed for a NUMBER datatype. In over 25 years I have never seen Oracle raise an exception because a sequence value could not be inserted into a column defined simply as NUMBER.

    Oracle ALWAYS stores data in columns that use NUMBER datatype using ONLY the space required to store the significant digits of the value.

    That is, '1000000' takes NO more space than '1' and takes LESS space than storing '123'.
    drop table test_number;
    create table test_number (myId number, myNumber number);
    insert into test_number values (1, 1);
    insert into test_number values (2,1000);
    insert into test_number values (3,1000000);
    insert into test_number values (4,123);
    insert into test_number values (5,123000);
    insert into test_number values (6,123000000);
    
    select myId, myNumber, dump(myNumber) from test_number order by myId;
    
    MYID	MYNUMBER	DUMP(MYNUMBER)
    1	1	Typ=2 Len=2: 193,2
    2	1000	Typ=2 Len=2: 194,11
    3	1000000	Typ=2 Len=2: 196,2
    4	123	Typ=2 Len=3: 194,2,24
    5	123000	Typ=2 Len=3: 195,13,31
    6	123000000	Typ=2 Len=3: 197,2,24
    The first three values all have 'Len=2' and the last three values only have "len=3' even though the actual decimal values are very different.

    Thus there is rarely ANY reason not to define columns simply as NUMBER when they are used to store sequence values.
  • user491696
    user491696 Member Posts: 12
    Thanks all of you for answers. In our new environment we are going to put sequence name like table name (e.g: seq_<table_name>) ....... Is it possible we can generate a pl/sql code which will count the column size and sequence ??? I am still learning pl/sql ...any help will be appreciated..... this time I know which sequence will be applying on which table.....Any PL/SQL code which will count first column of the table and highest value of the sequence (SEQ_<TABLE_NAME>)..... Thanks for reading my post
  • Unknown
    edited May 16, 2013 11:35AM
    >
    Is it possible we can generate a pl/sql code which will count the column size and sequence ???
    >
    Modify your columns to use NUMBER datatype spec so that you won't have a 'column size' problem. It's simple to do and is just a data dictionary change. There should be NO impact on any of your code or applications,.

    If you mean how do you find the largest valule used in the table
    SELECT MAX(mySequenceColumn) from myTab le;
    That will only be accurate if there is no DML on the table at the time that might insert a new value or change an existing one. That is typically used if you need to recreate/reset a sequence to start again from the next available value.
    >
    Any PL/SQL code which will count first column of the table and highest value of the sequence
    >
    You can find the 'current' last value
    select sequence_name, last_number 
    from all_sequences
    where sequence_name = 'EMPLOYEES_SEQ'
    
    SEQUENCE_NAME	LAST_NUMBER
    EMPLOYEES_SEQ	207
    That also will only be accurate if there is no DML on the table that might use a new value.
  • wtlshiers
    wtlshiers Member Posts: 83
    Consider creating a table... you need to map the sequence to the column that drives it. I've done something similar...
    CREATE TABLE sequence_alarm
        (owner                          VARCHAR2(30 BYTE) NOT NULL,
        table_name                     VARCHAR2(30 BYTE),
        column_name                    VARCHAR2(30 BYTE),
        sequence_name                  VARCHAR2(30 BYTE),
        last_seq_val                   NUMBER,
        percent_alarm                  NUMBER(2,2) NOT NULL,
        col_percent                    NUMBER(2,2),
        seq_percent                    NUMBER(2,2))
    The owner would be the schema owner (if you had multiple schemas you wanted to review). You would need the table that holds the column that drives the sequence.

    After that - it's simply a matter of using this table in conjunction with the DBA_SEQUENCES and DBA_TABLES to ensure you're not exceeding the amount. Probably wise to report on the sequences that may not have been mapped (so you don't miss mapping any of them) etc.

    The percent alarm would be the percentage you want to be warned at (i.e. .75 - at which time you'd indicate that the sequence is within 75% of the max usage of it's associated column).

    The col_percent would be updated with the amount the sequence is using of the column each time you run it as would the seq_percent (which would be the amount the sequence is using of it's total value). Remembering that you may create a sequence with a max value of 999 (for example) but - it's mapped to a column defined as NUMBER(15). In this case - the sequence will be a problem before the column it's mapped to will be.

    Let me know if that helps - if not - I'll try and lay out a 'step by step' - as to what I've done for the same.
This discussion has been closed.