CODEWITHSUNDEEP
sqloracle-database
Shivam7898
Shivam7898

Reputation: 132

Combine results of multiple queries in Oracle Sql

Database - Oracle Database 10g Release 10.2.0.4.0 , working with Oracle SQL Developer

EDIT Sorry: Query-1 Should be :

SELECT TABLE_NAME FROM USER_TABLES;

earlier it was SELECT OWNER, TABLE_NAME FROM ALL_TABLES;

Output-1: ALL TABLES that I own

Query-2: SELECT COUNT(*) FROM MYTABLE_1;

Output-2: Total number of rows in specific table MYTABLE_1

Query-3: SELECT MAX(ORA_ROWSCN) FROM MYTABLE_1;

Output of Query-3 is a number (493672033308) which is further used in Query-4

Query-4: SELECT SCN_TO_TIMESTAMP(493672033308) FROM DUAL;

Output-4 is last updated time of specific table MYTABLE_1

How to combine all of this to get a list of all user tables with a total of 3 columns having column headers TABLE_NAME, TOTAL_ROWS, LAST_UPDATE_TIME

EDIT-2: Final Query:

SELECT t.TABLE_NAME , m.TIMESTAMP , t.NUM_ROWS , ((NVL(t.NUM_ROWS,0) + m.INSERTS) - m.DELETES) AS TOT_ROWS FROM USER_TABLES t LEFT OUTER JOIN USER_TAB_MODIFICATIONS m ON t.TABLE_NAME = m.TABLE_NAME ORDER BY t.TABLE_NAME;

Thanks APC, StevieG, bob dylan :)

Upvotes: 2

Views: 2809

Answers (3)

APC
APC

Reputation: 146349

You want to use the contents of the data dictionary to drive a query. This can only be done with dynamic SQL, in a procedure.

Some points to bear in mind:

  1. Oracle maintains the SCN Timestamp mapping to support Flashback Query. It just keeps the mapping for the supported UNDO_RETENTION period. So we can use SCN_TO_TIMESTAMP() only for tables which have recent activity. Staler tables will hurl ORA-08181.
  2. Tables with no rows won't have an associated SCN. SCN_TO_TIMESTAMP() hurls if we pass it null for the SCN.

So a robust solution is quite complex. This one uses DBMS_OUTPUT to display the results; other mechanisms are available:

declare
    n pls_integer;
    max_scn number;
    x_scn_too_old exception;
    pragma exception_init(x_scn_too_old ,-08181);
    txt varchar2(30);
begin
    for lrec in ( select table_name from user_tables )
    loop
        execute immediate 
            'select count(*), max(ora_rowscn) from '
                || lrec.table_name
                into n, max_scn;
        dbms_output.put(lrec.table_name
                                ||' count='||to_char(n));
        begin
            if n > 0 then
                select to_char(scn_to_timestamp(max_scn), 'yyyy-mm-dd hh24:mi:ss.ff3')
                into txt
                from dual;
            else
                txt := null;
            end if;
        exception
            when x_scn_too_old then
                txt := ('earlier');
        end;
        dbms_output.put_line(' ts='||txt );
    end loop;
end;
/

There is a pure SQL alternative, using NUM_ROWS from USER_TABLES and the USER_TAB_MODIFICATIONS view. This view is maintained by Oracle to monitor the staleness of statistics on tables. As you're on 10g this will be happening automatically (in 9i we had to switch on monitoring for specific tables).

USER_TAB_MODIFICATIONS gives us numbers for the DML activity on each table, which is neat because we can add those numbers to NUM_ROWS to get an accurate total, which is much more efficient than issuing a COUNT().

Again a couple of points.

  1. Any table which lacks statistics will have NUM_ROWS=0. For this reason I use NVL() in the arithmetic column
  2. USER_TAB_MODIFICATIONS only contains data for tables which have changed since the last time statistics were gathered on them. Once we gather statistics on a table it disappears from that view until more DML is issued. So, use an outer join.
  3. Note that we will only have a timestamp for tables with stale statistics. This is less predictable than the SCN_TO_TIMESTAMP used above, as it depends on your stats gathering strategy.

So here it is:

select t.table_name
        , m.timestamp
        , t.num_rows
        , ((nvl(t.num_rows,0) + m.inserts) - m.deletes) as tot_rows
from user_tables t
     left outer join USER_TAB_MODIFICATIONS m
         on t.table_name = m.table_name
order by t.table_name
/

Perhaps the best solution is a combination, using NUM_ROWS and USER_TAB_MODIFICATIONS to avoid the count, and only checking ORA_ROWSCN for tables with fresh statistics.

Note that this is only a concern because you don't have your own journalling or table audit. Many places add metadata columns on their tables to track change data (e.g. CREATED_ON, CREATED_BY, UPDATED_ON, UPDATED_BY).

Upvotes: 1

bob dylan
bob dylan

Reputation: 1498

I don't have enough rep to post a comment however the reason why StevieG's answer is still returning an error is because you don't have access to the dba_tab_modifications view, instead use user_ / all_ equivalents in-line with your permissions:

SELECT a.OWNER, a.TABLE_NAME, a.NUM_ROWS, b.TIMESTAMP
FROM ALL_TABLES a
INNER JOIN ALL_TAB_MODIFICATIONS b ON a.OWNER = b.TABLE_OWNER AND a.TABLE_NAME = b.TABLE_NAME

Upvotes: 1

StevieG
StevieG

Reputation: 8729

I'd do it like this:

SELECT a.OWNER, a.TABLE_NAME, a.NUM_ROWS, b.TIMESTAMP
FROM ALL_TABLES a
INNER JOIN DBA_TAB_MODIFICATIONS b ON a.OWNER = b.TABLE_OWNER AND a.TABLE_NAME = b.TABLE_NAME

edit - This is correct, except for NUM_ROWS might not be fully accurate: http://www.dba-oracle.com/t_count_rows_all_tables_in_schema.htm

Upvotes: 1

Related Questions