DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: Maximum filed length within schema

  1. #1
    Join Date
    Jul 2001
    Location
    Montreal
    Posts
    222

    Maximum filed length within schema

    Hi.. Could you please advise what dictionary tableview (if applicable) could I query that gives me the field with the maximum length for all tables within a schema? Thanks

  2. #2
    Join Date
    Mar 2002
    Posts
    534
    Oracle® Database Reference 10g Release 1 (10.1)
    http://download-west.oracle.com/docs...htm#sthref2876

    USER_TAB_COLUMNS.DATA_LENGTH

    HTH

  3. #3
    Join Date
    Jul 2001
    Location
    Montreal
    Posts
    222
    Thanks.

    I ran the following:
    select max(data_length), from USER_TAB_COLUMNS;
    MAX(DATA_LENGTH)
    ----------------
    4000

    How do I get the table_name included in the result?

    I tried running the following:

    select max(data_length),table_name from USER_TAB_COLUMNS;
    *
    ERROR at line 1:
    ORA-00937: not a single-group group function

    Please help and thanks.

  4. #4
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334
    what are you actually trying to find out?.

    The maximum length of each column in each table?

  5. #5
    Join Date
    Jul 2001
    Location
    Montreal
    Posts
    222
    The longest column with the table name and owner.

  6. #6
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Try this:

    Code:
    select *
    from (
          select table_name, max(data_length) from user_tab_columns
          where user = 'TAMIL'
          group by table_name
          order by 2 desc
    )
    where rownum <= 1
    /
    Tamil

  7. #7
    Join Date
    Jul 2001
    Location
    Montreal
    Posts
    222
    It worked. Thank you for your help.

  8. #8
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334
    im curious, why do you care who has the longest column.

    Sounds like a game public schoolboys might play :|

  9. #9
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Quote Originally Posted by davey23uk
    im curious, why do you care who has the longest column.

    Sounds like a game public schoolboys might play :|
    Looks like class assignment.

    Tamil

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width