Oracle SQL Find Highest ID Across All Tables

Every one 开发者_如何学Cof my tables has an "id" field. I need to be able to find the highest ID across all of them, given that the list of tables might change.

Is there any way to get the list of tables in an oracle database, aggregate their rows (only ids), and then get the max()?

P.S. This is for updating a sequence which has gone out of whack.

Here is some simple dynamic SQL driving off the data dictionary:

SQL> set serveroutput on
SQL> declare
  2      l_id pls_integer;
  3      max_id pls_integer;
  4      max_tab_name varchar2(30);
  5  begin
  6      max_id := 0;
  7      for r in ( select table_name
  8                 from user_tab_columns
  9                 where column_name = 'ID' )
 10      loop
 11          execute immediate 'select max(id) from '||r.table_name
 12              into l_id;
 13          if l_id > max_id
 14          then
 15              max_id := l_id;
 16              max_tab_name := r.table_name;
 17          end if;
 18      end loop;
 19      dbms_output.put_line('Highest score = '||max_id||' table='||max_tab_name);
 20  end;
 21  /
Highest score = 2010070705 table=SESSIONS

PL/SQL procedure successfully completed.

SQL>

If the sequence services tables across several schemas, you will need to drive off ALL_TAB_COLUMNS and include OWNER in the query.

How about this?

 SELECT MAX(ID) 
 FROM
 (
    SELECT MAX(ID) AS ID FROM CUSTOMER
    UNION ALL 
    SELECT MAX(ID) AS ID FROM EMPLOYEE
    UNION ALL 
    SELECT MAX(ID) AS ID FROM MANAGER
 ); 

Repeat the UNION ALL for all tables that you need to search from.

how about querying the sequence that drives the id's for CURRVAL...

if you need to find out also what table that id is in, then construct a new table to track the id centrally and add triggers to populate on insert.