CODEWITHSUNDEEP
oracle-database
Manikandaraj Srinivasan
Manikandaraj Srinivasan

Reputation: 3647

Max Range and Highest value of Number Column in all tables in a Oracle Database

I'm using the below query to get the Max Range of all the Columns of all tables in a Oracle Database

    select OWNER,TABLE_NAME,COLUMN_NAME,DATA_PRECISION,(POWER(10,DATA_PRECISION) -1) 
from ALL_TAB_COLUMNS where OWNER = 'MASTER' and DATA_TYPE = 'NUMBER' and 
DATA_PRECISION is NOT NULL order by TABLE_NAME ASC;


OWNER    TABLE_NAME  COLUMN_NAME   DATA_PRECISION  (POWER(10,DATA_PRECISION) -1)
MASTER    TABLE_1      COL_1            7            9999999
MASTER    TABLE_1      COL_5            7            9999999
MASTER    TABLE_2      COL_3            10           9999999999

I am trying to get a 6th & 7th Column, which should be max of that column in that particular table and difference between Max Range( i.e (POWER(10,DATA_PRECISION) -1)) and actual max value in the table.

Ex:

OWNER    TABLE_NAME  COLUMN_NAME   DATA_PRECISION  (POWER(10,DATA_PRECISION) -1)   MAX_VALUE     DIFF
MASTER    TABLE_1      COL_1            7            9999999                         9994637     5362
MASTER    TABLE_1      COL_5            7            9999999                         9997637     2362
MASTER    TABLE_2      COL_3            10           9999999999                    8933999999    1066000000

How to achieve this ?

Would i be able to join tables, using TABLE_NAME ?

Bet, this would be helpful to a lot of people.

Upvotes: 3

Views: 969

Answers (2)

Jon Heller
Jon Heller

Reputation: 36902

Convert DBA_TAB_COLUMNS.HIGH_VALUE to the maximum value as described in this post by Jonathan Lewis.

The maximum and minimum values are recorded when table statistics are gathered. If the default estimate percent is used, and the statistics were gathered recently, the value will be accurate.

create or replace function raw_to_num(i_raw raw) 
return number 
as 
    m_n number; 
begin 
    dbms_stats.convert_raw_value(i_raw,m_n); 
    return m_n; 
end; 
/  

select owner,table_name,column_name,data_precision
    ,(power(10,data_precision) -1) max_range
    ,raw_to_num(high_value) max_value
    ,(power(10,data_precision) -1) - raw_to_num(high_value) diff
from dba_tab_columns
where data_type = 'NUMBER'
    and owner = user
    --Add conditions for specific users and tables.
order by owner, table_name, column_name;

Upvotes: 0

omaraguirre
omaraguirre

Reputation: 164

The easiest way to go about this is to write a small function that you can provide a table name and column like so:

  CREATE OR REPLACE FUNCTION get_max_value (p_table_name VARCHAR2, p_column VARCHAR2) 
    RETURN NUMBER IS
    v_query VARCHAR2(1000);
    v_max_value NUMBER;
BEGIN
    v_query := 'SELECT MAX (' || p_column ||') FROM '
        ||  p_table_name ;
    EXECUTE IMMEDIATE v_query
        INTO v_max_value;
    RETURN v_max_value;
END;

And then a slightly modified version of your query above:

 SELECT   owner,
         table_name,
         column_name,
         data_precision,
         (POWER(10,data_precision) -1) ,
         get_max_value (owner || '.' || table_name, column_name) max_val 
FROM    all_tab_columns   
WHERE   1 = 1 
AND     owner = 'MASTER' 
AND     data_type = 'NUMBER'   
AND     data_precision is NOT NULL 
AND     table_name NOT LIKE  '%$%'
ORDER BY c.table_name ASC

Just be careful, depending on the number of your tables and the number of rows in each, execution can take a while.

Upvotes: 2

Related Questions