Get max length of data for a column

Toad for Oracle
21

Chaining is no longer the big issue was it was in the 90’s. For example
use CLOB or BLOB and chaining happens no matter what. Since there were so many
new cases that added to this chaining list, oracle got smarter at how it handles
chaining – and they no longer toot the chaining horn anywhere near as
loud. In fact chaining goes along with cache hit ratios - pretty much the old
way of thinking. As for CHAR storing a null when empty, that’s true for
any data type – so your example is not char specific. In fact one could
argue that char eliminates chaining when there are no null rows since it always
stores exactly N locations – whereas varchar when there are no empty rows
would almost always fit your cited example. So varchar is much more likely to
induce chaining.

But again, chaining is not the big issue it used to be. My advice is tha when
experts start claiming chaining you need to use the Peabody way back machine to
return to the 90’s when it meant a big deal J

22

[ Attachment(s) from Dennis Paulus included below]

Hi Chris,

I know I was late in the game to this one, but perhaps this might help. I
remembered doing something similar in a previous life, so I did some searching
and found a script. The attached script is pretty old, but it might get you most
of the way there and I’m sure it can be fine tuned quite a bit. J It was just a
quick and dirty script to get what I needed at the time.

Some key ingredients of the script:

  1. It uses EXECUTE IMMEDIATE, so it’s definitely not the fastest, but it gets
    the job done.

  2. It will skip over any data types that it can’t calculate, such as LONG data
    types. I had no need to do that, but it could be added pretty easily.

  3. It provides the ability to display for one table or all tables in a specified
    schema (see header notes for details on the calls).

  4. If you have a lot of tables, a lot of columns, large db etc., you may want to
    insert the results into a table (or some other method), instead of using the
    dbms_output statements, to ensure you get all the results.

  5. You’ll want to run as a user that has priv’s to dba_tables/dba_tab_cols.

Hope this helps.

Thanks,

Dennis