How to get all columns which are nearly as long as the maximum allowed size

Question

Is it possible to get all columns (varchar2, char, nvarchar2), where the data is as long (or nearly as long - 2 characters less) than the allowed maximum size of the column in an Oracle Database?

With the following statement, I get the maximum allowed size for each column:

select table_name, column_name, data_type, data_length 
from user_tab_columns 
where data_type in ('VARCHAR2', 'CHAR', 'NVARCHAR2') 
order by data_type;

Now I want to find every column where max(length(column))+2 >= data_length

For example:

The output of my statement is following:

TableA  | ColumnA |  VARCHAR2 |  30
TableA  | ColumnB |  VARCHAR2 |  30
TableB  | ColumnA |  VARCHAR2 |  50
TableB  | ColumnB |  VARCHAR2 |  50

Now I have to run

SELECT MAX(LENGTH(ColumnA)) FROM TableA;
SELECT MAX(LENGTH(ColumnB)) FROM TableA;
SELECT MAX(LENGTH(ColumnA)) FROM TableB;
SELECT MAX(LENGTH(ColumnB)) FROM TableB;

with following results:

• 20 (is not important, because maximum allowed length is 30)
• 30 (is important, because maximum allowed length is 30)
• 30 (is not important, because maximum allowed length is 50)
• 50 (is important, because maximum allowed length is 50)

Is this possible to find all of them with a script or a statement?

Answer 1

Yes it's possible with PL/SQL:

1. make a FOR LOOP on your columns,

2. print output of the max data length; you can add your comparison in the prepared statement v_qry:

   declare
     v_qry varchar2(4000) := '';
     v_res number;
   begin
     for x in (
       select table_name, column_name, data_type, data_length 
         from user_tab_columns 
        where data_type in ('VARCHAR2', 'CHAR', 'NVARCHAR2') 
      order by data_type
     ) loop
        -- prepare your statement
        v_qry := 'select  MAX(LENGTH('||x.column_name||')) FROM '||x.table_name||' ';
        -- execute
        execute immediate v_qry into v_res;
        -- print result
       dbms_output.put_line('in:'||x.table_name||'.' ||x.column_name||':'||v_res||':compared to:'|| x.data_length);
     end loop;
   end;
   /
   

NB: it can take time depending on your tables sizes

With on of my tables (MY) it gives:

in:MY.C_UTI_MAJ:6:compared to:6
in:MY.C_UTI_CRE:6:compared to:6
in:MY.C_TYP_PARAM:20:compared to:20
in:MY.PARAM:16:compared to:20
in:MY.DESCRIPTION:245:compared to:255