>How can I get a record count of all the tables in the database ?
The spool file examples will do of course. Doing it the hard way, using
dynamic SQL:
Below are two scripts. One checks a column last_update_date, which is
normally present in Oracle Applications (like Oracle Financials) tables. The
other simply counts all tables.
Good luck,
Arjan.
- File : sql/check_last_update.sql
--
- Description : Creates and fills the table avbentem_last_updates
- Note the declaration of c_check_date, like:
--
- c_check_date varchar2(17) := '06-JUL-1998 13:42'
--
- As this script might encounter invalid views or other
- errors, and as the checks may take a very long time to
- finish, results are stored in a table. This way, the
- script can be restarted whenever needed. This table can
- also be used to check the progress of the script, as
all
- changes are committed directly.
--
- Syntax : check_last_update
--
whenever sqlerror continue
--
- This table might already exist due to restart of this script:
--
create table avbentem_last_updates
( check_date date not null
, table_name varchar2(132)
, column_name varchar2(132)
, check_result varchar2(50)
);
--
whenever sqlerror exit failure
--
- Delete uncompleted checks in case of restart of this script:
--
delete from avbentem_last_updates
where check_result = 'Checking';
commit;
--
declare
l_result integer;
c_check_date constant varchar2(17) := '06-JUL-1998 13:42';
--
cursor c_date_cols is
select usercol.table_name, usercol.column_name
from user_tab_columns usercol
where usercol.column_name = 'LAST_UPDATE_DATE'
or usercol.column_name = 'CREATION_DATE'
and not exists
( select null
from avbentem_last_updates checked
where checked.table_name = usercol.table_name
and checked.column_name = usercol.column_name
and checked.check_result != 'Checking'
)
and not exists
( select null
from user_views
where view_name = usercol.table_name
);
--
- Dynamic SQL function to execute any query which returns an integer value:
--
function exec_query( p_query in varchar2)
return integer
is
l_cursorid integer;
l_rowcount integer;
l_result integer;
begin
l_result := null;
--
l_cursorid := dbms_sql.open_cursor;
dbms_sql.parse( l_cursorid, p_query, dbms_sql.native);
dbms_sql.define_column( l_cursorid, 1, l_result);
l_rowcount := dbms_sql.execute_and_fetch( l_cursorid, true);
dbms_sql.column_value( l_cursorid, 1, l_result);
dbms_sql.close_cursor( l_cursorid);
--
if l_result is null then
dbms_output.put_line( 'No result for ' || p_query);
end if;
--
return l_result;
exception
when others then
if dbms_sql.is_open( l_cursorid) then
dbms_sql.close_cursor( l_cursorid);
end if;
raise;
end exec_query;
--
begin
for r_date_cols in c_date_cols loop
--
insert into avbentem_last_updates
( check_date, table_name, column_name, check_result)
values
( sysdate, r_date_cols.table_name, r_date_cols.column_name, 'Checking');
commit;
--
l_result := exec_query
( 'select count(*)'
|| ' from ' || r_date_cols.table_name
|| ' where ' || r_date_cols.column_name
|| ' > to_date(''' || c_check_date || ''', ''DD-MON-YYYY HH24:MI'')'
);
--
if l_result != 0 then
update avbentem_last_updates
set check_result = 'Newer: ' || to_char( l_result)
where table_name = r_date_cols.table_name
and column_name = r_date_cols.column_name;
commit;
else
update avbentem_last_updates
set check_result = 'All older'
where table_name = r_date_cols.table_name
and column_name = r_date_cols.column_name;
commit;
end if;
end loop;
end;
/
set pagesize 60
column check_result format a10 heading 'Result'
column table_name format a37 heading 'Table'
column column_name format a29 heading 'Column'
select table_name, column_name, check_result
from avbentem_last_updates
where check_result = 'All older';
select table_name, column_name, check_result
from avbentem_last_updates
where check_result != 'All older';
select check_result, count(*)
from avbentem_last_updates
group by check_result;
set serveroutput on size 10000
!echo Oracle-instance ORACLE_SID is $ORACLE_SID
show user
declare
l_cursor integer; -- cursor pointer
l_source varchar2(1000); -- SQL statement
l_rows integer;
l_count number(10);
l_total number(12) := 0;
--
cursor c_tabs is
select table_name
from user_tables
order by table_name;
begin
dbms_output.put_line( 'Count of all user tables');
for r_tab in c_tabs loop
l_cursor := dbms_sql.open_cursor;
l_source := 'SELECT COUNT(*) FROM ' || r_tab.table_name;
dbms_sql.parse( l_cursor, l_source, dbms_sql.NATIVE);
--
dbms_sql.define_column(l_cursor, 1, l_count);
l_rows := dbms_sql.execute_and_fetch(l_cursor, false);
dbms_sql.column_value(l_cursor, 1, l_count);
dbms_sql.close_cursor( l_cursor);
--
if l_count > 0 then
dbms_output.put_line( rpad( r_tab.table_name, 30, '.')
|| to_char( l_count));
l_total := l_total + l_count;
end if;
end loop;
dbms_output.put_line( rpad( 'Total: ', 30)||to_char( l_total));
end;
/
Received on Thu Sep 17 1998 - 11:30:15 CDT
