sql - The maximum column value of all tables -

it's possible add below script max value of 1 specific column (each have column same name):

select    owner,    table_name,    round((num_rows*avg_row_len)/(1024*1024)) mb,   num_rows "rows",   last_analyzed --max(data_for_each_column) all_tables  owner = 'oap' order table_name asc 

if tables have 1 common column, can use hack xml dynamically create select max() each table:

select owner,         table_name,         round((num_rows*avg_row_len)/(1024*1024)) mb,        num_rows "rows",        last_analyzed,        dbms_xmlgen.getxmltype('select max(id) m '||owner||'.'||table_name).extract('//text()').getnumberval() max_id all_tables tbl owner = 'oap'   , exists (select 1                all_tab_columns ac               ac.owner = tbl.owner                 , ac.table_name = tbl.table_name                 , ac.column_name = 'id') order table_name asc; 

you need replace max(id) correct column name.

the query fail if there @ least 1 table not have column named id - not "skip" table. using and exists (..) condition use table have column named id.

---

dbms_xmlgen.getxmltype() run passed sql query , return xml representation of result set, like:

<rowset>  <row>   <m>42</m>  </row> </rowset> 

the extract('//text()') extracts (only) text value in result '42' , getnumberval() turn real number.